Finding All Access Groups For A Windows Login With sys.login_token
Posted by Josh | Posted in SQL Server, T-SQL Programming | Posted on 03-09-2012
Tags: Permissions, SQL Server
0
Recently I had a requirement to determine how a certain Windows user gained access to an instance of SQL. Let’s say, for example, you want to audit certain DDL code executions for some users, but not for others. We could do this at the individual login level, but that would be tedious to keep up. Instead, we want to use Windows domain groups to selectively enable the audit process. The trouble is, when a user is logged in all we see is their individual user name, not the name of the groups that, but virtue of them being a member, give them access to the server. Or do we?
I was reading a post by Erland Sommarskog on the subject of permission granting through stored procedures, and noticed his use of a system view called sys.user_tokens. This view (according to BOL) “Returns one row for every database principal that is part of the user token.” In essence, it shows one row per role or group that the user is part of. For example, if a user is a member of a Windows group that has access to the database, a row will be present for that.
This got me thinking, was there an equivalent for the server level? Sure enough, there is the sys.login_tokens view. This shows one row per server level authentication token. For some reason it seems to return duplicate rows at times, but when joined on the sys.server_principals table it does indeed seem to work as I hoped.
|
1 2 3 4 5 6 |
select slt.name, ssp.type_desc from sys.login_token slt JOIN sys.server_principals ssp ON slt.sid = ssp.sid where ssp.type_desc = 'WINDOWS_GROUP' |
This way I can tell what domain groups a user is a member of that gives them access to the server.
