SQL Script to generate script for existing database permissions

Leave a comment (0) Go to comments

Today, One of my entry level DBA asked me, "How Can I generate SQL Script for existing database permissions" prior to answer his question, I asked him, "Why you wanted to do that"

He clarifies that he has done a data export for some table (Partial data / tables) to a new server, new database,using SSIS and now he want to sync the permissions.

This is valid reason, where a DBA need to copy the permissions from one server to another Server. So we should have a script which should generate script for existing database permissions.

SQL Script to generate script for existing database permissions

-- ***********************************************************************************/
-- Description  :    SQL Script to generate script for existing database permissions
--                    This Script will generate a Script, which can used to copy the permissions from one server to another Server
-- Compatibility  :     7.0+
-- This Script should be executed in specific database or master
-- ***********************************************************************************/
set nocount on
go
use Northwind
go
create table perms
(
    perms varchar (100)
)
declare @name varchar(50)
declare @object_id int
declare @action tinyint
declare @protect_type tinyint

declare perms cursor for
select su.name, sp.id, sp.action, sp.protecttype
from sysusers su
inner join sysprotects sp on (su.uid = sp.uid)
order by su.name
open perms
fetch next from perms into @name, @object_id, @action, @protect_type
while (@@fetch_status = 0)
begin
    if @action = 26
    insert into perms
    select 'GRANT REFERENCES' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 178
    insert into perms
    select 'GRANT CREATE FUNCTION TO ' + @name
    if @action = 193
    insert into perms
    select 'GRANT SELECT' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 195
    insert into perms
    select 'GRANT INSERT' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 196
    insert into perms
    select 'GRANT DELETE' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 197
    insert into perms
    select 'GRANT UPDATE' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 198
    insert into perms
    select 'GRANT CREATE TABLE TO ' + @name
    if @action = 203
    insert into perms
    select 'GRANT CREATE DATABASE TO ' + @name
    if @action = 207
    insert into perms
    select 'GRANT CREATE VIEW TO ' + @name
    if @action = 222
    insert into perms
    select 'GRANT CREATE PROCEDURE TO ' + @name
    if @action = 224
    insert into perms
    select 'GRANT EXECUTE' + ' ON [' + object_name(@object_id) + '] TO ' + @name
    if @action = 228
    insert into perms
    select 'GRANT BACKUP DATABASE TO ' + @name
    if @action = 233
    insert into perms
    select 'GRANT CREATE DEFAULT TO ' + @name
    if @action = 235
    insert into perms
    select 'GRANT BACKUP LOG TO ' + @name
    if @action = 236
    insert into perms
    select 'GRANT CREATE RULE TO ' + @name
    insert into perms values ('GO')
    fetch next from perms into @name, @object_id, @action, @protect_type
end
close perms
deallocate perms
select * from perms
drop table perms
set nocount off

OUTPUT


SQL Script to generate script for existing database permissions how to Generate script for existing sql database permissions Generating scripts for existing permissions generate SQL Script for existing database permissions

Tags : Generating scripts for existing permissions, how to Generate script for existing sql database permissions,generate SQL Script for existing database permissions

EOF - SQL Script to generate script for existing database permissions, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.