Post reply

Copying users, roles and permission from one db to another

  • December 14, 2011 at 7:27 am

    #249776

    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

  • December 14, 2011 at 7:35 am

    #1421294

    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!

  • December 14, 2011 at 7:43 am

    #1421307

    In fact, I only need to script out the users, database roles and the mapping between users and logins.

  • December 16, 2011 at 3:09 pm

    #1422747

    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

  • December 16, 2011 at 4:16 pm

    #1422762

    You can take the bits that you need out of here

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

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply