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.