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.

Advertisements

4 thoughts on “Cloning SQL Permissions for users

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

  2. — 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

    Like

  3. Pingback: Something for the Weekend - SQL Server Links 10/08/12

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s