SQL Server permissions discovery

I had to migrate a product to a new SQL Server recently, and there are some places for configuration that I overlooked.  For example, I missed how one login had the bulkadmin role at the server level.   And I missed how some users had SQL Server Agent permissions in MSDB. This led to some problems on the first day, which were quickly resolved. So I was too focused on user databases, and not on the overall picture.

Here is a script to discover many of the rights in your system databases.


--Server level permissions
SELECT
 [srvprin].[name] [server_principal],
 [srvprin].[type_desc] [principal_type],
 [srvperm].[permission_name],
 [srvperm].[state_desc]
FROM [master].[sys].[server_permissions] srvperm
 INNER JOIN [master].[sys].[server_principals] srvprin
 ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
and permission_name <> 'CONNECT SQL'
and [srvprin].[name] not in('##MS_PolicyTsqlExecutionLogin##')
ORDER BY [server_principal], [permission_name];

--MSDB permissions
SELECT P.name, O.name,DP.*
FROM msdb.sys.database_permissions DP
inner join msdb.sys.database_principals P
on DP.grantee_principal_id = P.principal_id
and DP.grantee_principal_id <> 0
left join msdb.sys.objects O
on DP.major_id = O.[object_id]
WHERE P.type_desc <> 'DATABASE_ROLE'
and DP.type <> 'CO'
and P.name <> '##MS_PolicyEventProcessingLogin##'
order by DP.class,P.name, O.name

--MSDB Role Assignments
SELECT p.NAME as principal_name
,m.NAME as role_member_name
FROM msdb.sys.database_role_members rm
JOIN msdb.sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN msdb.sys.database_principals m
ON rm.member_principal_id = m.principal_id
WHERE m.type_desc <> 'DATABASE_ROLE'
and m.NAME not in('dbo',
'MS_DataCollectorInternalUser',
'MS_DataCollectorInternalUser',
'MS_DataCollectorInternalUser',
'##MS_PolicyEventProcessingLogin##',
'##MS_PolicyTsqlExecutionLogin##'
)
order by m.NAME

--master permissions (Execute)
SELECT P.name, O.name,DP.*, OBJECT_NAME(major_id) as cmdName
FROM [master].sys.database_permissions DP
inner join [master].sys.database_principals P
on DP.grantee_principal_id = P.principal_id
and DP.grantee_principal_id &lt;&gt; 0
left join [master].sys.objects O
on DP.major_id = O.[object_id]
WHERE P.type_desc <> 'DATABASE_ROLE'
and DP.type <> 'CO'
and P.name not in('##MS_AgentSigningCertificate##','##MS_PolicyEventProcessingLogin##')
order by DP.class,P.name, O.name

--Master role assignments
SELECT p.NAME as principal_name
,m.NAME as role_member_name
FROM [master].sys.database_role_members rm
JOIN [master].sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN [master].sys.database_principals m
ON rm.member_principal_id = m.principal_id
WHERE m.type_desc <> 'DATABASE_ROLE'
and m.name <> 'dbo'
order by m.NAME

--Server level roles
select [name], 'sysadmin' from syslogins
where sysadmin = 1
UNION ALL
select [name], 'setupadmin' from syslogins
where setupadmin = 1
UNION ALL
select [name], 'securityadmin' from syslogins
where securityadmin = 1
UNION ALL
select [name], 'serveradmin' from syslogins
where serveradmin = 1
UNION ALL
select [name], 'processadmin' from syslogins
where processadmin = 1
UNION ALL
select [name], 'diskadmin' from syslogins
where diskadmin = 1
UNION ALL
select [name], 'dbcreator' from syslogins
where dbcreator = 1
UNION ALL
select [name], 'bulkadmin' from syslogins
where bulkadmin = 1
ORDER BY 1
Advertisements
This entry was posted in Database Administration and tagged . 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