Defining Current Security Checks (SQL Server)

Question

One thing that I have always hated more than anything in MS SQL Server is the way to ensure security. The security context constantly switches if you look at the server funny, and it is often very difficult (for me anyway) to predict or debug.

In connection with the problem today, however, I want: "I would like to add a line to my code that displays the security context that SQL Server uses when running this code." Is there such a team? For example, SELECT security_context()

To be a little clearer ... if I am in a stored procedure, and therefore I must obey the security context of the SP owner, I would like to see this. If I am in the code called by sp_executesql, and this will cause the security to be in the context of the SQL Server service account, I would like to see this.

At least I could understand why SQL Server believes that I should not have access to anything.

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 

Answer 1

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

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

Each access you get is ultimately taken out of line as a result of returning these results. Note that some access is implied from hard-coded memberships (for example, the db_datareader database role or the sysadmin server role).

Other that:

• the ownership chain is not related to the security context: you are not under the "context" of the SP owner. The ownership chain simply states that access checks are skipped for objects belonging to the same owner as the current object (SP, View).
• sp_executesql does not in any way alter the security context.

Answer 2

Not sure if this means a security context, but you can get the user associated with your session, for example:

 select SYSTEM_USER 

This works for both logging into SQL Server and logging into WIndows. It even works inside stored procedures using execute as owner . For instance,

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

Print

 sa MyMachine\MyName 

If you are looking for the Windows account that SQL Server uses to do something on your behalf, you can try running whoami from a command, for example:

 EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC master..xp_cmdshell 'whoami' 

For me, this returns nt authority\network service .

Answer 3

I think you want to use CURRENT_USER to see the current security context. Here is 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 exit (note: for this I am the administrator of my SQL Server)

 Current User Name ------------------ dbo (1 row(s) affected) Current User Name ------------------ Junk (1 row(s) affected) Current User Name ------------------ dbo (1 row(s) affected)