List all permissions for a user in all or selective databases

by Dev on

Today I am gonna talk about the requirement of finding out all permissions for a user in all or selective databases.

The query here will help you in following situations:

Customer has provided you with a user id and couple of database names and they wanted to find out all the permissions the user has on those databases.
You have a task to provide access for a user id to some of the databases. You have provided the access and now want to pull the report to send to customer.
You have been given a user id and asked to find out all permissions in all user databases.

This may not be exactly what you are looking for but certainly will give you some idea to make it your way.

Retrieve all permissions for a user in selective databases:

Link to Download the code:

Query for Selective Database(s) permissions

In this example I have listed the permissions for “Test_User” on databases “database1” and “database2″as shown the below screen shot:

Retrieve all permissions for a user in all user databases:

Download the code:

Query to copy the code

Here is an example output:

Please feel free to let me know if there is any better way you can think of to retrieve the same information.

Check out the below scripts as well:
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name prior to Failover
Script to List All Users with db_owner role in SQL 2000

Please spread the word:

https://sqlgeekspro.com/list-all-permissions-for-a-user-in-all-or-selective-databases/

Tagged as: T-SQL Script

Comments:

Moggawawa

Hi,

I am looking for a script to list all users of all databases of a given SQL server but including those not declared at the database level (such as DBA/sysadmin) but they can access all databases. Do you have such a thing?

Thank you

Moggawawa

Reply
- Dev
  
  Hi Moggawawa, you just need to remove the where clause from the second query and run the server level permission query along with that.
  Try this. Hope that is what you were looking for:
  
  DECLARE @dbname VARCHAR(50)
  DECLARE @statement NVARCHAR(MAX)
  
  DECLARE db_cursor CURSOR
  LOCAL FAST_FORWARD
  FOR
  SELECT name
  FROM master.sys.databases
  WHERE name NOT IN (‘master’,’msdb’,’model’,’tempdb’)
  AND state_desc=’online’
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @dbname
  WHILE @@FETCH_STATUS = 0
  BEGIN
  
  SELECT @statement = ‘use ‘+@dbname +’;’+ ‘SELECT
  
  ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole
  
  FROM sys.database_role_members roles
  
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id’ — Change the user name
  
  EXEC sp_executesql @statement
  
  FETCH NEXT FROM db_cursor INTO @dbname
  END
  CLOSE db_cursor
  DEALLOCATE db_cursor
  
  SELECT @@SERVERNAME as SQLInstance, R.name AS server_role
  , P.name AS role_member
  FROM sys.server_role_members RM
  INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
  INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = ‘SERVER_ROLE’) R
  ON RM.role_principal_id = R.principal_id
  
  Reply