Query users and roles from all databases on a server instance

I was recently asked by a System Administrator to provide a query that would help her audit all of the users and their access levels on SQL Server. She was logging in to each database using SQL Server Management Studio and manually browsing through the users & permissions and noting them in Excel.

It’s probably the sort of task well suited to Powershell and if I were to extend this to work on all servers in an environment I would look at Powershell to do it. That situation would necessitate a single admin user across all environments – just to make admin easier – and reliable access to all the servers on a domain.

As it is, this query needs to be run per-database instance. Luckily there are only 15 of these that my Sysadmin colleague needs to connect to. If you had hundreds this would clearly be impractical and a multi-server Powershell script would be ideal.

 

The query is based on a query in the comments of this post, which I then modified to run on all DB’s in an instance based on this helpful post.

USE MASTER

DECLARE @TempDump Table

(

ServerName nvarchar(100),

DatabaseName nvarchar(100),

UserName nvarchar(100),

[public] nvarchar(100),

db_owner nvarchar(100),

db_accessadmin nvarchar(100),

db_securityadmin nvarchar(100),

db_ddladmin nvarchar(100),

db_datareader nvarchar(100),

db_datawriter nvarchar(100),

db_denydatareader nvarchar(100),

db_denydatawriter nvarchar(100)

)

 

INSERT INTO @TempDump

 

EXECUTE sys.sp_MSforeachdb

 

‘SELECT @@servername as ServerName,

”[?]” as DatabaseName,

UserName,

Max(CASE RoleName WHEN ”public” THEN ”Yes” ELSE ”” END) AS [public],

Max(CASE RoleName WHEN ”db_owner” THEN ”Yes” ELSE ”” END) AS db_owner,

Max(CASE RoleName WHEN ”db_accessadmin” THEN ”Yes” ELSE ”” END) AS db_accessadmin,

Max(CASE RoleName WHEN ”db_securityadmin” THEN ”Yes” ELSE ”” END) AS db_securityadmin,

Max(CASE RoleName WHEN ”db_ddladmin” THEN ”Yes” ELSE ”” END) AS db_ddladmin,

Max(CASE RoleName WHEN ”db_datareader” THEN ”Yes” ELSE ”” END) AS db_datareader ,

Max(CASE RoleName WHEN ”db_datawriter” THEN ”Yes” ELSE ”” END) AS db_datawriter ,

Max(CASE RoleName WHEN ”db_denydatareader” THEN ”Yes” ELSE ”” END) AS db_denydatareader,

Max(CASE RoleName WHEN ”db_denydatawriter” THEN ”Yes” ELSE ”” END) AS db_denydatawriter

 

from

(

select b.name as UserName,

c.name as RoleName

from [?].sys.sysmembers a

join [?].sys.sysusers b on a.memberuid = b.uid

join [?].sys.sysusers c on a.groupuid = c.uid

)s

Group by UserName

order by UserName’

 

 

SELECT * FROM @TempDump

Order BY UserName, DatabaseName

Leave a Reply

Your email address will not be published. Required fields are marked *