I have an application which has a bunch of specific permissions granted to some objects within the database. I encountered a problem while trying to migrate this application from one server to another because I needed to recreate these permissions which were added over the period of a year as and when new features were added to the application.

I ended up using sp_helprotect to list out the permissions for the objects that I was interested in. But I still needed to convert the permissions into the necessary GRANT/REVOKE statements. This can be tedious when you are trying to manipulate permissions across databases and multiple objects. What I ended up doing was writing a crude T-SQL script to help me create the necessary GRANT/REVOKE scripts based on the sp_helprotect output.

Note: sp_helprotect does not return information about securables that were introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.

So, if you have a securable that you are interested in which was introduced in SQL Server 2005, then this is not going to help you.

Note: This script has been tested only for the following objects:

1. Tables

2. Stored Procedures

3. Functions (UDFs/TVFs)

Modification: Aug 8, 2012: Thanks to Graham [blog] for suggesting the corrections.

Script:

set nocount on

-- Declare and initialize local variables
declare @newuser varchar(255)
set @newuser = 'newuser' -- This is the new user that you need to add permissions for

-- Create temp table to store sp_helprotect output

create table #tbl_permissions
(Owner varchar(50),
Object varchar(255),
Grantee varchar(255),
Grantor varchar(255),
ProtectType varchar(50),
Action varchar(50),
Col varchar(50)
)

-- Store sp_helprotect output in a temp table
insert into #tbl_permissions
exec sp_helprotect null,'olduser'

-- Get the necessary GRANT/REVOKE/DENY T-SQL commands
select RTRIM(LTRIM(ProtectType)) + ' ' +
CASE Action WHEN 'CONNECT' THEN Action + ' TO ['+@newuser+']'
ELSE Action + ' ON OBJECT::'+OWNER+'.'+OBJECT+' TO [' +@newuser+']'
END
from #tbl_permissions

-- Drop the temporary table
drop table #tbl_permissions

Now that a bit of my work was easier, I decided to put this script out on the web so that someone else can benefit from the same. I am sure that there are multiple T-SQL scripts which do the same but I found the one below very helpful. I shall try and make it more comprehensive by using sys.database_permissions in a future blog post.