Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copying users, roles and permission from one db to another Expand / Collapse
Author
Message
Posted Wednesday, December 14, 2011 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 26, 2015 3:21 AM
Points: 295, Visits: 414
Hi,

I need a script that copy users, mapping between users and logins, user permissions from one db to another. The source database will be on the same server as target database.
The script will be run on many servers. Each server can have differrent loggins and source database can have different users, user permissions etc.

Thanks
Post #1221670
Posted Wednesday, December 14, 2011 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 14,323, Visits: 37,408
well, searching the scripts and articles section here on SSC will get you lots and lots of examples for scripting out users and roles, and object permissions too;

permissions on the objects is certainly possible, so you plan on deploying the script agaisnt a standardized database schema, so all the expected objects exist?

what would happen if you generate a script for, say SELECT permissions on a specific view, but the view doesn't exist int he database?


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1221680
Posted Wednesday, December 14, 2011 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 26, 2015 3:21 AM
Points: 295, Visits: 414
In fact, I only need to script out the users, database roles and the mapping between users and logins.
Post #1221695
Posted Friday, December 16, 2011 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, May 22, 2016 7:33 PM
Points: 7,916, Visits: 14,251
In that case sys.database_principals and sys.database_role_members has all the metadata you need to build the commands that can add the Database Roles and Database Users to another database.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1223372
Posted Friday, December 16, 2011 4:16 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 6,113, Visits: 13,562
You can take the bits that you need out of here

http://www.sqlservercentral.com/scripts/Permissions/76450/


---------------------------------------------------------------------

Post #1223389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse