Post reply

Use sp_addlogin to syncronize users

  • July 12, 2005 at 2:09 pm

    #165381

    When setting up log shipping, you quite often have database level users out of sync with server level logins ( generally because the sql server logins are missing on the standby server).   I've searched many articles on this and nearly all offer some convoluted approach to copying logins from the primary log shipping server.

    If you can get the SID from sysusers in the restored database on the standby server, what is the downside from creating the login as below, where you specify the desired sid to match sysusers.  Seems so simple there must be a hitch.  I'm only talking about situations with very few sql server logins.

    EXEC sp_addlogin 'Michael', 'chocolate', 'pubs', 'us_english', 0x0123456789ABCDEF0123456789ABCDEF

    Randy

  • July 15, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • July 15, 2005 at 10:30 am

    #574951

    Hello,

    I too use this sytax with source server SIDs to create a list of logins in the destination server like on a test server.

    I usually do it with a script for a big list of logins, I even did it when migrating from SQL Server 6.5 to SQL Server 2000 where I had to convert SID data type in the intermediate table.

    My only concern is if there are already logins on the destination server that were created for some other reason with Enterprise Manager before I run my script. The best example is my own local standard login that I may create right after SQL Server install. This login may already have certain permissions in different databases on the destination server and when I run my script this login will not be created the second time, the error message will be posted.

    So I output and keep a list of created logins logins that could not be created.

    Yelena 

    Regards,Yelena Varsha

Viewing 3 posts - 1 through 2 (of 2 total)

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