SQL Server Training | Tutorials | How to's

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

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