Security and permissions is a huge topic in SQL Server, but as a wee appetite wetter here are some queries I find endlessly useful:
All possible permissions and their scopes:
select * from fn_builtin_permissions(default)
My server permissions:
select * from fn_my_permissions(null,'SERVER')
My database permissions:
select * from fn_my_permissions(null,'DATABASE')
Of course other user’s permissions can be checked by impersonation:
execute as login = '[login]'; go select * from fn_my_permissions(null,'SERVER') revert;
There are multiple stored procedures to deal with roles, this one returns membership of a given server role:
EXEC sp_helpsrvrolemember 'sysadmin'
How does a given domain user access SQL Server?:
EXEC xp_logininfo '[domain\user]','ALL'
Here’s a couple that look at the OS\domain level:
EXEC master..xp_cmdshell 'whoami /priv' EXEC master..xp_cmdshell 'whoami /groups'
Get the current SQL context: