Post reply

Loading Users into instance security

  • March 19, 2013 at 7:57 am

    #394418

    We are moving from a server with SQL server 2005 to one with SQL server 2008. Is there any way to script a bulk load of users into the instance security? I have mounted the databases, from backup, but need to get the users set-up in the instance. With over 1,000 users this would take a while going through the SSMS GUI interface.

  • March 19, 2013 at 9:41 am

    #1598508

    I am assuming you meant Logins and not Users. Users are at the database level and are stored within the databases themselves so should come over when you restore the backups to the new instance.

    To bring the Server Logins across you'll want to make sure you maintain the SIDs for the SQL Server-authentication-based Logins, else the Users in all your databases linked to those Logins on the new instance will be orphaned. You can do this by following this article:

    How to transfer logins and passwords between instances of SQL Server

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 28, 2013 at 9:29 am

    #1601693

    Yep that is it. Thanks.

  • March 28, 2013 at 11:14 am

    #1601761

    My pleasure. One item of note, the revlogin proc does not pickup Fixed Server Role membership. Here is a query you can adapt to pickup those memberships as well. I actually have it incorporated in a custom version of the revlogin proc I use. I would share the whole thing but it has some other items in it that are specific to my utiltiy database so it's not easily shareable.

    SELECT 'EXEC master.sys.sp_addsrvrolemember @loginame = ''' + SUSER_SNAME(r.sid) + ''', @rolename = ''' + p.name + ''';

    '

    FROM master.sys.server_principals r

    JOIN master.sys.server_role_members m ON r.principal_id = m.member_principal_id

    JOIN master.sys.server_principals p ON m.role_principal_id = p.principal_id;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 28, 2013 at 2:37 pm

    #1601867

    Thanks... our roles are simple, but that does save a bunch of key strokes.

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

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