User accounts

  • Admingod

    SSCertifiable

    Points: 5664

    I am planing to upgrade to SQL Server 2016 and I’d like to see any suggestions on user account. We store credentials securely in Account security solutions(third party security solution). We have SQL server 2012 user accounts with password stored in Account security solution. Is there an option to copy user accounts from SQL Server 2012 to 2016 version?I am thinking to use Generate and Publish scripts options for users to be created and select logins and right click and generate script and run them on new SQL 2016 server. Right now planing to move only user accounts not the database. Would that be good option? Would you recommend using the same copied user accounts or having new ones created? I don't see any reason for the new one's.Please advise?

    Thanks in Advance!

  • Sreekanth B

    SSCertifiable

    Points: 6144

    Are you aware of sp_help_revlogin?? If not, check this

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

  • Admingod

    SSCertifiable

    Points: 5664

    Yes. I know. Thanks. Can anybody comment on other items as well?

  • Jeff Moden

    SSC Guru

    Points: 995161

    sp_Help_RevLogin does not migrate server level privs.  You'll need to do a search for a proc the will generate a script for those privs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sue_H

    SSC Guru

    Points: 90287

    The Generate Scripts is at the database level so that can also cause some problems - the logins need to be there as that tool generates statements like CREATE USER UserName FOR LOGIN LoginName. Seems you would want to figure out what you are going to do with logins first.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 995161

    Sue_H wrote:

    The Generate Scripts is at the database level so that can also cause some problems - the logins need to be there as that tool generates statements like CREATE USER UserName FOR LOGIN LoginName. Seems you would want to figure out what you are going to do with logins first.

    Sue

    Just to be sure, I'm not talking about the built in Generate Scripts.  I'm talking about a separate script to execute that will create the server level privs for logins.  Restores will take care of the database level privs if the script from sp_help_revlogin has been executed first.  Neither will take care of server level privs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995161

    Perhaps the following will help...

    https://www.databasejournal.com/features/mssql/using-powershell-to-generate-logins-server-and-database-permissions.html

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sue_H

    SSC Guru

    Points: 90287

    Jeff Moden wrote:

    Sue_H wrote:

    The Generate Scripts is at the database level so that can also cause some problems - the logins need to be there as that tool generates statements like CREATE USER UserName FOR LOGIN LoginName. Seems you would want to figure out what you are going to do with logins first.

    Sue

    Just to be sure, I'm not talking about the built in Generate Scripts.  I'm talking about a separate script to execute that will create the server level privs for logins.  Restores will take care of the database level privs if the script from sp_help_revlogin has been executed first.  Neither will take care of server level privs.

    I was directing that to Admin since the question was about using the Generate scripts thing to generate the scripts and it seemed they wanted more info about that with the follow up post and wanting comments about it. My point was to that it references the login. And not knowing what type of accounts they are, could be an issue with orphans as well depending on how things are done. Just seemed appropriate to worry about the logins first as I said. And logins would be the ones with server roles, server level permissions.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 995161

    Ah... got it, Sue.  It looked like you were referencing my reply.  Sorry.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Admingod

    SSCertifiable

    Points: 5664

    As a part of upgrade, I have restored the databases to different server but forgot to create the Logins. So would that cause orphans on the new server? Since the users would come with database and Logins won't exist. Would Just creating the logins would be suffice?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716659

    If you create the logins with the same parameters, yes, this will help. sp_helprevlogin does this.

  • Admingod

    SSCertifiable

    Points: 5664

    So you saying there won't be any Orphans? How about the passwords for SQL Account? Would that be moved with SP_helprevlogin?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716659

    If you restore a database, you restore users. If the logins that link to these by SID do not exist, you have orphans.

    Using sp_helprevlogin will give you a script to move logins, and remove the orphans. You would still need to link them with sp_change_users_login.

  • Admingod

    SSCertifiable

    Points: 5664

    Would that transfer the passwords for sql account?

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88174

    Admingod wrote:

    Would that transfer the passwords for sql account?

    Yes - it creates the new login with the same password and the same SID.  Because the SID is the same - it ties to the user in the database without having to execute sp_change_users_login (no orphans).

    But - this does not do anything for server level permissions, which you will either have to manually recreate or find a script that identifies those for you.  You could also build your own script using the tables sys.server_role_members and sys.server_principals.

    Or - you can look at dbatools and use their powershell functions (https://docs.dbatools.io/#Copy-DbaLogin).

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 15 posts - 1 through 15 (of 16 total)

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