Cloning SQL Permissions for users

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.

4 thoughts on “Cloning SQL Permissions for users”

Pingback: Tweets that mention Cloning SQL Permissions for users « TroubleshootingSQL -- Topsy.com

— File name : Cloning SQL Permissions for users.sql
— Author : Amit Banerjee
— Reference : https://troubleshootingsql.com/2010/06/26/cloning-sql-permissions-for-users/
— Modified by : Graham Okely B App Sc

set nocount on

— Declare and initialize local variables
Declare @SQL_String nvarchar(1024)
Declare @newuser nvarchar(255)
Declare @FromUser nvarchar(255)

— This is the user that you need to extract permissions for
Set @FromUser = ‘devuser’
Set @newuser = ‘IronMan’

— 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
Set @SQL_String = ‘insert into #tbl_permissions exec sp_helprotect null,’ + ”” + @FromUser + ””

exec (@SQL_String)

— 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
— Exclude diagram stored procedures as we are not copying over diagram permissions
where Object not like ‘%diagram%’

— Drop the temporary table
drop table #tbl_permissions

LikeLike