locked
Script to get all user permissions of all users in a database in SQL Server 2005 RRS feed

  • Question

  • Can someone help me in writing a Script to get all user permissions of all users in a database in SQL Server 2005.
    Tuesday, April 14, 2009 6:36 AM

Answers

  • Hello,

    Go through this link.

    http://blogs.conchango.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

    Also the code below.
    SET NOCOUNT ON
    DECLARE	@OldUser sysname, @NewUser sysname
    SET	@OldUser = 'HRUser'
    SET	@NewUser = 'PersonnelAdmin'
    
    SELECT	'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
    SELECT	'--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'
    
    SELECT	'EXEC sp_addrolemember @rolename =' 
    	+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
    FROM	sys.database_role_members AS rm
    WHERE	USER_NAME(rm.member_principal_id) = @OldUser
    ORDER BY rm.role_principal_id ASC
    
    SELECT	CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    	+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) 
    	+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    	+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
    FROM	sys.database_permissions AS perm
    	INNER JOIN
    	sys.objects AS obj
    	ON perm.major_id = obj.[object_id]
    	INNER JOIN
    	sys.database_principals AS usr
    	ON perm.grantee_principal_id = usr.principal_id
    	LEFT JOIN
    	sys.columns AS cl
    	ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    WHERE	usr.name = @OldUser
    ORDER BY perm.permission_name ASC, perm.state_desc ASC
    
    SELECT	CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    	+ SPACE(1) + perm.permission_name + SPACE(1)
    	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    	+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
    FROM	sys.database_permissions AS perm
    	INNER JOIN
    	sys.database_principals AS usr
    	ON perm.grantee_principal_id = usr.principal_id
    WHERE	usr.name = @OldUser
    AND	perm.major_id = 0
    ORDER BY perm.permission_name ASC, perm.state_desc ASC
    Thanks,
    Shanthi
    • Marked as answer by Tintu Jose Tuesday, April 14, 2009 8:53 AM
    Tuesday, April 14, 2009 6:45 AM