TSQL: showing database level and server level permissions
Posted by jpluimers on 2014/06/12
A few short scripts checking out the cause for TFS Integration Tools refuses to start despite an admin being able to create a TEST database.
Get a list of names having sysadmin access to the server from Bru Medishetty:
SELECT NAME, isntname FROM SYSLOGINS WHERE sysadmin = 1 and hasaccess = 1
Check the permissions for a specific database (thanks Arnie Rowland):
Note that if a user has a default database, but no permission for that database, you can get a hard to find login error as List All Permissions a User Has in SQL Server Database and Error 4064 explains.
SELECT * FROM fn_my_permissions(NULL, 'master')
CONTROL SERVER is a special SERVER wide permission.
Two notes why you should be careful with CONTROL SERVER:
- it implies any permission as this table shows (thanks User Aaron Alton)
- Potential Security Exploit Using CONTROL SERVER Permissions in SQL Server (thanks K. Brian Kelley who also wrote this first script)
SELECT login.name, perm.permission_name, perm.state_desc FROM sys.server_permissions perm JOIN sys.server_principals login ON perm.grantee_principal_id = login.principal_id WHERE permission_name = 'CONTROL SERVER';
Listing all server wide permissions (also by K. Brian Kelley):
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];
Finally a long one: SQL Server 2005: View all permissions (2) – Explode the roles by Jamie Thomson (jamiet) on Twitter while he was working at EMC (his new SSIS Junkie blog is at SQLblog.com – The SQL Server blog spot on the web):
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM perms_cte p
WHERE principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment