User accounts

Question

Post reply

User accounts

Admingod

SSCertifiable

Points: 5997
More actions
September 24, 2019 at 5:01 pm

#3683000

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!

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

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

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 1

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: 5997 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

Sue_H SSC Guru Points: 90860 More actions · Answer 4

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: 1003857 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

Sue_H SSC Guru Points: 90860 More actions · Answer 7

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: 1003857 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

Admingod SSCertifiable Points: 5997 More actions · Answer 9

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: 734434 More actions · Answer 10

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

Admingod SSCertifiable Points: 5997 More actions · Answer 11

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: 734434 More actions · Answer 12

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: 5997 More actions · Answer 13

Admingod

SSCertifiable

Points: 5997

December 3, 2019 at 5:35 pm

#3703911

Would that transfer the passwords for sql account?

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 14

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).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs