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.
One thought on “Scripting Object Level Permissions”
Comments are closed.