Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted April 24, 2015

User Login Permission Detail

>>Script Language and Platform: SQL Server
Use this stored procedure to quickly capture all login information when needed, especially for security audit situations.

Managing login and DB user permissions is among the responsibilities that DBAs must take care of. To catch the permission of all Users in a database or all permissions of a certain login on all DBs, down to DB Object levels using SSMS is time consuming, tedious, even impossible, let alone instances with hundreds of databases. Especially, impersonated logins, which show no access on any DB, but they actually have access to hundreds DB objects like tables, view, Stored Procedures in several DBs in the instance. Use this stored procedure to quickly capture all login information when needed, especially for security audit situations.

What it does: This stored Procedure is to skim all login/DB Users in a SQL Server instance along with their DB permissions down to the DB object level. You can store the result set in a table and query the table to find out:

  1. All Database User accounts and all permission granted on a specific DB.
  2. Permissions of a specific DB User on a specific DB.
  3. All permissions that a certain login account possesses on all DBs in the instances.

Author: Stephanie Nguyen

CREATE proc [dbo].[DBASP_ALL_UserPermission]
DECLARE @strSQL nvarchar(2000),
@dbname nvarchar(256)
CREATE table #DBUsers 
DBname varchar (256),  
LoginName varchar(100),  
DBUserName varchar(100),           
[DBRole] varchar (100),     
PrincipalType  varchar(100), 
PermissionName  varchar(100) ,
ObjectType varchar(50),  
Objectname varchar(100), 
Columnname varchar(100)
DECLARE listdbs Cursor
SELECT name from master.dbo.sysdatabases
WHERE  name not in ('master', 'model', 'msdb', 'tempdb')
OPEN listdbs
FETCH next
     FROM  listdbs into @dbname    
     WHILE @@fetch_status = 0
     SELECT @strSQL =                      
     Use ['+ @dbname+'] ;
     , objectType = case perm.class
             WHEN 1 THEN obj.type_desc
                      ELSE perm.class_desc
     ,objectName = case perm.class
              when 1 then Object_name(perm.major_id)
                    when 3 then 
                             when 4 then
     sys.database_role_members drm
     RIGHT JOIN  sys.database_principals dp
     on dp.principal_id = drm.member_principal_id
     LEFT JOIN sys.database_principals dp2
     on dp2.principal_id = drm.role_principal_id
     FULL JOIN sys.server_principals sp 
     ON dp.[sid] = sp.[sid] 
     LEFT JOIN sys.database_permissions perm 
     ON perm.[grantee_principal_id] = dp.[principal_id]
     LEFT JOIN sys.columns col 
     ON col.[object_id] = perm.major_id 
     AND col.[column_id] = perm.[minor_id] 
     LEFT JOIN sys.objects obj 
     ON perm.[major_id] = obj.[object_id] 
     LEFT JOIN sys.schemas schem 
     ON schem.[schema_id] = perm.[major_id] 
     LEFT JOIN sys.database_principals imp 
     ON imp.[principal_id] = perm.[major_id] 
     WHERE not in (''sys'' , ''information_schema'' , ''guest'', ''public'')
     ORDER by
    INSERT into #DBUsers
    EXEC (@strSQL)
    FROM listdbs into @dbname
    CLOSE listdbs
    DEALLOCATE listdbs
    SELECT * from #DBUsers 


Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home

SQL Scripts & Samples Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM