Generate SQL-script that creates all logins with appropriate permissions

Question

Generate SQL-script that creates all logins with appropriate permissions

avpco

Old Hand

Points: 387
More actions
February 11, 2019 at 1:56 am

#381404

Hi, colleges.
How can I create a script that contains information for creation all SQL-logins including database users with all permissions?
It'll need after I'll move SQL server to another AD domain.
Previuous AD accounts will be destroyed.
Thanks in advance)

Viewing 6 posts - 1 through 5 (of 5 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

You can use sp_help_revlogin. Take a look at the following link for details:
https://support.microsoft.com/en-gb/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Thanks

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 2

I forgot to add that you can also right click on your database in SSMS --> Tasks --> Generate Scripts and script out all your logins.

Thanks

avpco Old Hand Points: 387 More actions · Answer 3

NorthernSoul - Monday, February 11, 2019 2:01 AM
You can use sp_help_revlogin. Take a look at the following link for details:
https://support.microsoft.com/en-gb/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
Thanks

this script generates logins only without database permissions

Feivel SSCertifiable Points: 7524 More actions · Answer 4

You can refer this post.

https://www.sqlservercentral.com/Forums/Topic1560182-1550-1.aspx

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 5

I personally like using the dbatools PowerShell module - https://dbatools.io/. There are several commands, like Export-DBALogin, that can assist with scripting out logins or users and their permissions.