SQL – List Server and DB Permsission

Database Level Permission

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE
(
DBName VARCHAR(200),
UserName VARCHAR(250),
LoginType VARCHAR(500),
AssociatedDatabaseRole VARCHAR(200)
)
SET @DBuser_sql='
SELECT "[?]" AS DBName,a.name AS Name,
a.type_desc AS LoginType,
USER_NAME(b.role_principal_id) AS AssociatedDatabaseRole
FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members b ON a.principal_id=b.member_principal_id
LEFT OUTER JOIN [?].sys.server_role_members c ON a.principal_id=c.member_principal_id and a.principal_id=c.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN ("C")
AND a.is_fixed_role <> 1 AND a.name NOT LIKE "##%" AND "?" NOT IN ("master","msdb","model","tempdb")
ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT DBName,UserName,LoginType,
max(case when AssociatedDatabaseRole ='db_owner' then '1' else '0' end )'db_owner',
max(case when AssociatedDatabaseRole ='db_securityadmin' then '1' else '0' end )'db_securityadmin',
max(case when AssociatedDatabaseRole ='db_accessadmin' then '1' else '0' end )'db_accessadmin',
max(case when AssociatedDatabaseRole ='db_backupoperator' then '1' else '0' end )'db_backupoperator',
max(case when AssociatedDatabaseRole ='db_ddladmin' then '1' else '0' end )'db_ddladmin',
max(case when AssociatedDatabaseRole ='db_datareader' then '1' else '0' end)'db_datareader',
max(case when AssociatedDatabaseRole ='db_datawriter' then '1' else '0' end) 'db_datawriter',
max(case when AssociatedDatabaseRole ='db_denydatawriter' then '1' else '0' end )'db_denydatawriter',
max(case when AssociatedDatabaseRole ='db_denydatareader' then '1' else '0' end )'db_denydatareader',
max(case when AssociatedDatabaseRole is NULL then '1' else '0' end )'No Roles'
FROM @DBuser_table
group by DBName,UserName,LoginType

Server Level Permission

SELECT sp.name AS LoginName,sp.type_desc AS LoginType, sp.default_database_name AS DefaultDBName,slog.sysadmin AS SysAdmin,slog.securityadmin AS SecurityAdmin,slog.serveradmin AS ServerAdmin, slog.setupadmin AS SetupAdmin, slog.processadmin AS ProcessAdmin, slog.diskadmin AS DiskAdmin, slog.dbcreator AS DBCreator,slog.bulkadmin AS BulkAdmin
FROM sys.server_principals sp JOIN master..syslogins slog
ON sp.sid=slog.sid
WHERE sp.type <> 'R' AND sp.name NOT LIKE '##%'


Find Logins With VIEW SERVER STATE Permissions

SELECT @@SERVERNAME,SPRIN.[name],SPER.[permission_name] FROM sys.[server_permissions] SPER 
 INNER JOIN sys.[server_principals] SPRIN
 ON SPER.[grantee_principal_id] = SPRIN.[principal_id] WHERE SPER.[permission_name] = 'VIEW SERVER STATE'
 AND (SPER.[state] = 'G' OR SPER.[state] = 'W')AND SPRIN.[name] NOT LIKE '##%'
ORDER BY SPRIN.[name]


Permission
Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
Aside | This entry was posted in Security, SQL and tagged , , , . Bookmark the permalink.

One Response to SQL – List Server and DB Permsission

  1. Pingback: SQL – List Server and DB Permsission | SutoCom Solutions

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