Determining the current security checks being made (SQL Server)

Question

One thing that I've always hated more than just about anything in MS SQL Server is the way that security works. The security context constantly switches if you look at the server funny and it's often very hard (for me anyway) to predict or debug.

In dealing with an issue today, I though, "I wish I could just add a line to my code that would display the security context that SQL Server is using when this code runs." Does such a command exist? For example, SELECT security_context()

To be a little clearer... if I'm in a stored procedure and am therefor subject to the security context of the owner of the SP then I'd like to see that. If I'm in code that was called by sp_executesql and it's causing the security to be under the context of the SQL Server service account, then I would want to see that.

At least then I might be able to figure out why SQL Server thinks that I shouldn't have access to something.

Thanks!

---

EXAMPLE

-- Set up
CREATE USER Test_User WITHOUT LOGIN
CREATE TABLE Test_Security_Context (my_id INT)
INSERT INTO Test_Security_Context VALUES (1)
DENY SELECT ON Test_Security_Context TO Test_User
GO
CREATE PROCEDURE Test_Security_Context_SP
AS
  SELECT SUSER_SNAME()
  SELECT * FROM Test_Security_Context  -- This will return ok
  EXEC('SELECT SUSER_SNAME(); SELECT * FROM Test_Security_Context')  -- SUSER_SNAME() will match above but select fails
GO
GRANT EXECUTE ON Test_Security_Context_SP TO Test_User
GO

-- Switch to the new user
SETUSER 'Test_User'
GO

-- Do the test
EXEC Test_Security_Context_SP
GO

-- Clean up
SETUSER
DROP PROCEDURE Test_Security_Context_SP
DROP TABLE Test_Security_Context
DROP USER Test_User
GO

Amby

Answer 1

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

Thordis

+1 Wow. One question: why does the login_token for builtin\administrators say deny only ? I had expected that was the reason for most of my grants! – Nidify

@Andomar: I think is Vista LUA, it modifies the Administrators token to be deny only. I'm not sure though. – Thordis

Maybe poor terminology on my part. What I'm looking for is, "Here's why you don't have permissions" and "Here's why you do have permissions". I'm probably asking too much, but I know that I'm not alone in my hatred of trying to unravel these kinds of security bugs, especially when it comes to cross-database access, dynamic SQL, etc. – Amby

@Tom: Your example is all about ownership chaining. See technet.microsoft.com/en-us/library/ms188676.aspx. The code in Test_Security_Context_SP has access to Test_Security_Context because they're both owned by the same user. The code invoked by EXEC is no longer inside the SP, is invoked by the SP and thus looses the benefit of ownership chaining. – Thordis

It would be nice to know that the code in the SP is running with the privileges of user X and that the dynamic code, even though it's in the SP is under user Y. It doesn't look like SQL Server makes that information available though. Thanks for your help. – Amby

Running under an SP and pass privileges to dynamic code can be achieved with EXECUTE AS. That truly is an 'execution context' and gets passed to called procedures, including sp_executesql. – Thordis

Answer 2

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.

Nidify

Although SUSER_SNAME() is the user associated with the session, that won't still be valid when you're in a stored procedure (which uses the security context of the owner of the SP) or when you use dynamic SQL like EXEC(@cmd). Not to mention the whole mess of ownership chaining. :( – Amby

@Tom H: Both suser_sname() and SYSTEM_USER seems to work well for stored procedures (example added to answer.) Perhaps you could post an example where they fail? – Nidify

I've added an example that uses dynamic SQL. In both places the same SUSER_SNAME() is returned, but in one the select fails while it succeeds in the other. – Amby

Answer 3

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)

Morphogenesis

This has the same problem as Andomar's solution. Please see the example that I've added above. – Amby