Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Mar 6, 2006

Copy GRANT/REVOKE User/Group Permissions

By DatabaseJournal.com Staff



>>Script Language and Platform: SQL Server 2000

This script will Copy GRANT/REVOKE User/Group Permissions.

Simply run the script with proper substitution on :
source server + source database + source user
Copy the results and paste it into Query Analyzer of:
target server + target database
RUN (:

Author: Ajith Dharwar


-------Copy GRANT/REVOKE User/Group Permissions--------------------------
--------------04/01/2005-----------------
-------------AJITH DHARWAR-----------
------------akdharwar@yahoo.com------
------------Systems Architect--------

--REPLACE <Your Role/User on Source Server> with say DBAUSER_OLD

--REPLACE <Your Role/User on Target Server> with say DBAUSER_NEW


SELECT
CASE protecttype WHEN  204 THEN 'GRANT_W_GRANT '
     WHEN 205 THEN 'GRANT '
     WHEN 206 THEN 'REVOKE '
ELSE 'DUMMY GRANT' END
+ CASE action WHEN 26 THEN 'REFERENCES'
	WHEN 178 THEN 'CREATE FUNCTION'
	WHEN 193 THEN 'SELECT'
	WHEN 195 THEN 'INSERT'
	WHEN 196 THEN 'DELETE'
	WHEN 197 THEN 'UPDATE'
	WHEN 198 THEN 'CREATE TABLE'
	WHEN 203 THEN 'CREATE DATABASE'
	WHEN 207 THEN 'CREATE VIEW'
	WHEN 222 THEN 'CREATE PROCEDURE'
	WHEN 224 THEN 'EXECUTE'
	WHEN 228 THEN 'BACKUP DATABASE'
	WHEN 233 THEN 'CREATE DEFAULT'
	WHEN 235 THEN 'BACKUP LOG'
	WHEN 236 THEN 'CREATE RULE'
 ELSE ' DUMMY ' END + ' ON ' 
+ so.name + ' TO <Your Role/User on Target Server>'
from sysprotects sp
inner join sysobjects so on (so.id=sp.id)
inner join sysusers s on (sp.uid=s.uid)
where s.name= '<Your Role/User on Source Server>'

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued


Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date