Determining the current security checks being made (SQL Server)

Answer 1

Not sure if this is what you mean by security context, but you can retrieve the user associated with your session like:

select SYSTEM_USER

This works for both a SQL Server login or a WIndows login. It even works inside stored procedures with execute as owner. For example,

create procedure dbo.Test
with execute as owner
as
select SYSTEM_USER
go
exec dbo.Test
select SYSTEM_USER

Prints:

sa
MyMachine\MyName

If you're looking for the Windows account that SQL Server is using to do things on your behalf, you could try to run whoami from the command like:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

EXEC master..xp_cmdshell 'whoami'

For me, that returns nt authority\network service.

Answer 2

I think you want to use CURRENT_USER to see the current security context. Here's an example:

SELECT CURRENT_USER AS 'Current User Name';
GO
EXECUTE AS LOGIN = 'junk'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
REVERT
SELECT CURRENT_USER AS 'Current User Name';
GO

with output (note: I'm admin on my SQL Server for this)

Current User Name
------------------
dbo

(1 row(s) affected)

Current User Name
------------------
Junk

(1 row(s) affected)

Current User Name
------------------
dbo

(1 row(s) affected)

Answer 3

Yes, there is such a pair of views that represents your current security context, considering all the details like EXECUTE AS or code signing:

• sys.login_token for the server wide context
• sys.user_token for the current database context

Every single access you get is ultimately derived from a row in the return of these results. Note that some access are implicit from hard coded role membership (like db_datareader database role or sysadmin server role).

Other that that:

• ownership chaining is not related to security context: you are not under the 'context' of the SP owner. Ownership chaining simply states that access checks are skipped for objects owned by the same owner as current object (SP, View).
• sp_executesql does not change the security context in any way