Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Monday, July 7, 2014 8:17 AM
Points: 295, Visits: 402
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: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Monday, July 7, 2014 8:17 AM
Points: 295, Visits: 402
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: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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: Yesterday @ 10:35 AM
Points: 5,992, Visits: 12,940
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