SQL Server 2005 Object Level Permissions

I had been working a few weeks back for one of my friends to extract the object level permissions provided for a bunch of developers. The dba team wanted to have an initial list and then go for a restructuring of the existing security. I wrote a script to help me have this; please find it belwow. Its a much raw one, anyone can suggest modifications or can do the modifications by themselves and reuse it.


/*
SCRIPT TO FIND THE OBJECT LEVEL PERMISSIONS PROVIDED TO SPECIFIC LOGINS.
** THIS DOES NOT INCLUDE THE "ROLE BASED" PERMISSIONS SUCH AS PROVIDING "db_datareader" ROLE FOR A SOPECIFIC LOGIN
** THE GRANTEDBY VALUE WILL ALWAYS RETAIN "dbo" IF THE PERMISSIONS WERE GRANTED BY SOMEONE WHO HAS A "sysadmin" ROLE
** TO BE USED WITH SQL SERVER 2005
Wriitten By: RAJAN JOHN
*/
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
SELECT dpm.class_desc as [AccessLevel],
ISNULL(OBJECT_NAME(dpm.major_id), 'N/A')
as [ObjectName], dpr1.name AS [GrantedTo], dpr2.name AS [GrantedBy],
CASE dpm.type
WHEN 'AL' THEN 'ALTER'
WHEN 'ALAK' THEN 'ALTER ANY ASYMMETRIC KEY'
WHEN 'ALAR' THEN 'ALTER ANY APPLICATION ROLE'
WHEN 'ALAS' THEN 'ALTER ANY ASSEMBLY'
WHEN 'ALCF' THEN 'ALTER ANY CERTIFICATE'
WHEN 'ALDS' THEN 'ALTER ANY DATASPACE'
WHEN 'ALED' THEN 'ALTER ANY DATABASE EVENT NOTIFICATION'
WHEN 'ALFT' THEN 'ALTER ANY FULLTEXT CATALOG'
WHEN 'ALMT' THEN 'ALTER ANY MESSAGE TYPE'
WHEN 'ALRL' THEN 'ALTER ANY ROLE'
WHEN 'ALRT' THEN 'ALTER ANY ROUTE'
WHEN 'ALSB' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY REMOTE SERVICE BINDING'
WHEN 'ALSC' THEN 'ALTER ANY CONTRACT'
WHEN 'ALSK' THEN 'ALTER ANY SYMMETRIC KEY'
WHEN 'ALSM' THEN 'ALTER ANY SCHEMA'
WHEN 'ALSV' THEN 'ALTER ANY SERVICE'
WHEN 'ALTG' THEN 'ALTER ANY DATABASE DDL TRIGGER'
WHEN 'ALUS' THEN 'ALTER ANY USER'
WHEN 'AUTH' THEN 'AUTHENTICATE'
WHEN 'BADB' THEN 'BACKUP DATABASE'
WHEN 'BALO' THEN 'BACKUP LOG'
WHEN 'CL' THEN 'CONTROL'
WHEN 'CO' THEN 'CONNECT'
WHEN 'CORP' THEN 'CONNECT REPLICATION'
WHEN 'CP' THEN 'CHECKPOINT'
WHEN 'CRAG' THEN 'CREATE AGGREGATE'
WHEN 'CRAK' THEN 'CREATE ASYMMETRIC KEY'
WHEN 'CRAS' THEN 'CREATE ASSEMBLY'
WHEN 'CRCF' THEN 'CREATE CERTIFICATE'
WHEN 'CRDB' THEN 'CREATE DATABASE'
WHEN 'CRDF' THEN 'CREATE DEFAULT'
WHEN 'CRED' THEN 'CREATE DATABASE DDL EVENT NOTIFICATION'
WHEN 'CRFN' THEN 'CREATE FUNCTION'
WHEN 'CRFT' THEN 'CREATE FULLTEXT CATALOG'
WHEN 'CRMT' THEN 'CREATE MESSAGE TYPE'
WHEN 'CRPR' THEN 'CREATE PROCEDURE'
WHEN 'CRQU' THEN 'CREATE QUEUE'
WHEN 'CRRL' THEN 'CREATE ROLE'
WHEN 'CRRT' THEN 'CREATE ROUTE'
WHEN 'CRRU' THEN 'CREATE RULE'
WHEN 'CRSB' THEN 'CREATE REMOTE SERVICE BINDING'
WHEN 'CRSC' THEN 'CREATE CONTRACT'
WHEN 'CRSK' THEN 'CREATE SYMMETRIC KEY'
WHEN 'CRSM' THEN 'CREATE SCHEMA'
WHEN 'CRSN' THEN 'CREATE SYNONYM'
WHEN 'CRSV' THEN 'CREATE SERVICE'
WHEN 'CRTB' THEN 'CREATE TABLE'
WHEN 'CRTY' THEN 'CREATE TYPE'
WHEN 'CRVW' THEN 'CREATE VIEW'
WHEN 'CRXS' THEN 'CREATE XML SCHEMA COLLECTION'
WHEN 'DL' THEN 'DELETE'
WHEN 'EX' THEN 'EXECUTE'
WHEN 'IM' THEN 'IMPERSONATE'
WHEN 'IN' THEN 'INSERT'
WHEN 'RC' THEN 'RECEIVE'
WHEN 'RF' THEN 'REFERENCES'
WHEN 'SL' THEN 'SELECT'
WHEN 'SN' THEN 'SEND'
WHEN 'SPLN' THEN 'SHOWPLAN'
WHEN 'SUQN' THEN 'SUBSCRIBE QUERY NOTIFICATIONS'
WHEN 'TO' THEN 'TAKE OWNERSHIP'
WHEN 'UP' THEN 'UPDATE'
WHEN 'VW' THEN 'VIEW DEFINITION'
WHEN 'VWDS' THEN 'VIEW DATABASE STATE'
END
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dpr1
ON dpm.grantee_principal_id = dpr1.principal_id INNER JOIN sys.database_principals dpr2
ON dpm.grantor_principal_id = dpr2.principal_id
WHERE dpr1.principal_id NOT IN (0,1,2,3,4,16384,16385,16386,16387,16388,16389,16390,16391,16392,16393)