Security & permissions

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:

SELECT SYSTEM_USER
Advertisements
This entry was posted in Security, SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s