Post reply

sp_help_revlogin not copying sysadmin login!!!!

  • February 12, 2015 at 11:15 am

    #297656

    I created a SQL authentication login 'Mylonig' on Server A (named instance) with 'sysadmin' privilege. I am trying to move logins from server A to Server B (named instance on same server i.e. laptop). I ran 'sp_hexadecimal' and 'sp_help_revlogin' on Server A and copy the out put and pasted on Server B and ran it which created 'Mylogin' on Server B.

    Now when I check properties on 'Mylogin' on Server B it is not 'sysadmin'!!!! but just a 'public'. Am I missing something here?

  • February 12, 2015 at 11:20 am

    #1777316

    that just copies logins featuring their hashed password, and not their associated roles.

    grab this version, which is enhanced to do exactly that:

    http://www.tinyint.com/index.php/2009/07/08/sp_help_revlogin-cleaned-up-and-revised-with-roles/

    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!

  • February 12, 2015 at 11:38 am

    #1777327

    Thanks for the reply Lowell. just have few questions:

    1. Is it OK just to copy login using sp_help_revlogin and give 'sysadmin' role later? will it work?

    2. what's the best solution to copy logins which there is Mirroring between server A and B?

    Thanks

  • February 12, 2015 at 11:49 am

    #1777331

    Tac11 (2/12/2015)

    Thanks for the reply Lowell. just have few questions:

    1. Is it OK just to copy login using sp_help_revlogin and give 'sysadmin' role later? will it work?

    yes that will work, just add your user back to the sysadmin role. that's what i used to do, before i grabbed that link i showed you, and added if not exists() logic to it.

    2. what's the best solution to copy logins which there is Mirroring between server A and B?

    Thanks

    what you've already done, is the best way, the enhanced version is a little better, but ti's definitely how i've always done it.

    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!

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

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