Scripting Object Level Permissions

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.

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 [sys].[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.