Scripting Object Level Permissions

Posted by & filed under , , , , .

A while ago while making changes to a client database, I needed to determine all of the object level permissions that were in the database I was working on.  The system stored procedure sp_helprotect turned out to be pretty useful in obtaining this information. 

The procedure accepts four parameters:


sp_helprotect [ [ @name = ] 'object_statement' ]
[ , [ @username = ] 'security_account' ]
[ , [ @grantorname = ] 'grantor' ]
[ , [ @permissionarea = ] 'type' ]

They do pretty much what would be expected.  Provide @name of an object and the results are limited to those for that object only.

Of course, I wasn’t a huge fan of the output from the procedure since I couldn’t use it directly to rebuild all of the permissions.  Below is the T-SQL statement that I put together to provide what I needed.


SET NOCOUNT ON
GO
 
DECLARE @Permissions TABLE
(
Owner nvarchar(128)
,Object nvarchar(128)
,Grantee nvarchar(128)
,Grantor nvarchar(128)
,ProtectType nvarchar(10)
,[Action] nvarchar(10)
,[Column] nvarchar(128)
)
 
INSERT INTO @Permissions
EXEC sp_helprotect
 
SELECT RTRIM(UPPER(ProtectType)) + Space(1)
   + RTRIM(UPPER([Action])) + Space(1)
   + CASE WHEN Object <> '.' THEN 'ON ' + QUOTENAME(Owner) + '.' + QUOTENAME(Object)
   + Space(1) ELSE Space(0)
   END
   + CASE WHEN (PATINDEX('%All%', [Column]) = 0) and ([Column] <> '.') THEN ' ('
+ [Column] + ')' ELSE Space(0) END
   + 'TO ' + QUOTENAME(Grantee)
   + CHAR(10) + 'GO' + CHAR(10)
FROM @Permissions
ORDER BY CASE WHEN [Action] = 'CONNECT' THEN 0 ELSE 1 END, Owner, Object, Grantee

Hope this helps anyone with similar needs.