Comments (33)

  1. This is like bacon wrapped bacon! You even handled explicit permissions and I did not even think to do that. How you handled the SID and Password is pure genious! I used the sp_hexadecimal to convert them in my version. I’ve written a post about this but won’t be publishing it for some time, but I will certainly be linking back to this.

  2. Thanks Ryan!! I don’t think I can get a better compliment than “This is like bacon wrapped bacon!”. 🙂

  3. Great code!

    May I also suggest adding code to address port number instead of instance name:

    If CharIndex(‘,’, @PartnerServer) > 0
    Begin
    Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);
    End
    Else
    Begin
    Set @Machine = @PartnerServer;
    End

  4. Thanks for the suggestion Christian!! I’ll take that into consideration. Though now that I think of it, I’m wondering if I should just remove that block. My intent is that you pass in the name of the linked server it should us and backslash and comma are both acceptable characters in the name of a linked server.

  5. I thopugh the intent of that block was to exclude the Local window logins from the partner server when querying PartnerServer.master.sys.server_principals. These logins wouldn’t exist on the principal server. That’s why mine was exiting with errors, and why I put in the code to accept the comma.

  6. You’re right!! I was looking only at the If Else block and not following it down to the dynamic SQL. Thanks again!! I’ll make that update.

  7. […] out that the publisher has the old code for the procedure so he blogged and provided the new one HERE.  It does not handle changing the passwords for accounts that already exist, but it would be very […]

  8. Hello ! Your book about SQL 2008 Dataabse Mirroring is very interesting. I have a production environment with SQL 2008 SP1 and a database mirroring solution. When I want to test your “dba_copylogins.sql”, it doesn’t run:
    exec dbo.dba_copylogins @partnetserver = ‘wh1234’

    Msg 7202, Level 11, State 2, Line 1
    Could not find server ‘wh0603v’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

    The nameserver is good. Why this message about “sp_addlinkedserver” ?
    Thanks for your response

  9. Thanks Yano!!

    Did you also create the stored procedure dba_ManageLinkedServer (also in the book)? If the linked server doesn’t exist, the procedure uses this procedure to create the linked server.

    You can also download the SQL script to create it here: http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_ManageLinkedServer.sql

  10. Bob,

    This is a very useful script. Thanks!

  11. When I try and run it in a sql 2008 management studio it gives me the following error on the Cast(” As XML).

    Cannot call methods on int.

    Am i doing something wrong.

    Thanks

  12. Chris, I can’t reproduce the error. Can you make sure that the code has the right single quotes in place (” instead of ”).

  13. […] Speaking of logins, I have already covered transferring logins in a different blog post: Transferring Logins to a Database Mirror. […]

  14. […] to make your recovery process either: automate login creation.  Schedule a job to run weekly with Robert Davis’s login copy script and send the results to yourself via email.  That way, at the very least, you’ll have the […]

  15. Great script, just got a few errors

    Incorrect syntax near the keyword ‘ANY’.
    Msg 50000, Level 1, State 1
    Incorrect syntax near the keyword ‘ANY’.
    Msg 50000, Level 1, State 1
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    so ran it with debug = 1, and got

    GRANT_WITH_GRANT_OPTION ALTER ANY DATABASE To [DOMAINsomeserver$];
    GRANT_WITH_GRANT_OPTION CREATE ANY DATABASE To [DOMAINsomeserver$];
    GRANT CONNECT SQL To [DOMAINMy.Username];
    GRANT CONNECT On ENDPOINT::[Mirroring] To [DOMAINthisserver$];

    where domain and usernames have been obfuscated.

    1. Thanks for the feedback Kev! That should be a simple fix.

      1. The fix I did to get around the grant_with_grant_option was this alteration of the dynamic sql:
        Select @PermState = PermState,
        @PermName = PermName,
        @Class = Class,
        @LoginName = LoginName,
        @MajorID = MajorID,
        @SQL = CASE WHEN PermState = ‘GRANT_WITH_GRANT_OPTION’ THEN ‘GRANT’ ELSE PermState END + space(1) + PermName + SPACE(1) +
        Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)
        When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)
        Else ” End
        + space(1) + ‘To’ + space(1) + QUOTENAME(LoginName)
        + CASE WHEN PermState = ‘GRANT_WITH_GRANT_OPTION’ THEN ‘ WITH GRANT OPTION’ ELSE ” END + ‘;’
        From @Perms
        Where PermID = @CurrID;

        Thanks for the book and script Robert!
        -Chuck Lathrope

        1. Thanks Chuck! I really need to write a new version of this.

  16. […] Note that this could also be a symptom of an orphaned login. After establishing mirroring, Availability Groups, log shipping, etc. you may have created a new login or associated a user with a login on the primary database. The database-level user information gets replayed on the secondary servers, but the login information does not. Everything will work fine – until you have a failover. In this situation, you will need to synchronize the login and user information (for one example, see this script from Robert Davis). […]

  17. Great Article

    i launch the script like this:
    exec dbo.dba_copylogins @PartnerServer = ‘vmwp13b-db’

    where the vmwp13b-db is the name of the target server where the logins should be copied.

    I see “Command(s) completed successfully.” but actually no logins are copied to the destination… Why? Am I missing something?

    The idea is to copy logins between alwayson members in a SQL2012 Availability group.

    Thank you in advance.
    Alessandro

    1. Hi Alessandro. The process was set up to be run on the mirror/replicas/secondaries and @PartnerServer is the server from which it will copy logins. Does that make sense?

      1. Thank you for the reply,

        However I tried to run the script from the target server where the logins should be copied like this
        exec dbo.dba_copylogins @PartnerServer = ‘vgwp13a-db’
        (vgwp13a-db master server)

        “Command(s) completed successfully.”

        The servers have each other configured as linked servers. Is there any other requirement?

        … but actually the logins have not been copied…

        Thank you

        1. What do you get if you run the following:

          exec dbo.dba_copylogins @PartnerServer = ‘vgwp13a-db’, @Debug = 1;

          And do you have permissions to see the logins on the other server?

  18. […] AlwaysOn doesn’t replicate any logins. I use a sp called dbs_CopyLogins, it can dowloaded here: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror, is very useful since it copies the password […]

  19. Thank you for the script it works great for the initial loading of logins. One issue I am wondering how you handled was password changes. I would like to automate the copying of logins from production. This script will pick up any logins added from the last run, which is nice. But if the password changes for an existing login this would not come over to the replicate. If you have handled this another way I would interested in hearing how you did it.

    Thanks again!

    1. Thanks Andy. I made the decision not to handle “changes” and only process things that don’t exist. So many things can change that it would make the script very difficult.

      1. Thanks for the reply. I think I am going to come up with something based off the updatedate column in syslogins. Its not the most elegant solution but seems to be fairly foolproof just for making sure I always have the latest password.

        1. I know that Jonathan Kehayias was at one time trying to come up with a dynamic solution that would use extended events to transfer changes in real time, but there were issues with trying to get the required info. I don’t recall what the issues were.

  20. […] This procedure by SQLSoldier does what sp_help_revlogin does, with a couple of very important additions. It also copies roles and permissions. Also, it has a more elegant solution for handling the hash strings using XML. The script can be found here. Also check out this blog post. […]

  21. […] have reused a solution from a login copy script written by Robert L. Davis (T | B): based on this article, I rewrote the script as […]

  22. […] still works great. Check it out here in his blog post. Even if you don’t use his script to do this, it still will teach out a bit about some of the […]

Leave a Reply to Copy logins between SQL Server instances | Tomas LindTomas LindCancel reply