If you have been a DBA for a while you have probably gone through at least one migration to a new version of SQL Server, or maybe even more than one migration. One of the pains you might have discovered is migrating all the logins and associated permissions from one instance of SQL Server to another.
Microsoft has made it fairly easy to script out the logins by using the sp_help_revlogin stored procedure. Only problem with this stored procedure is it doesn’t generate the permissions associated with each login. If you want to generate a script that creates the logins and all their permissions, you should consider using PowerShell to do this instead of using sp_help_revlogin stored procedure.
Chrissy LeMaire, an MVP, has provided a free PowerShell toolkit named “dbatools” which contains lots of different features. One of the features is a function named “Export-DbaLogin”. By using “Export-Dbalogin” function you can export all or a select set of logins and their permissions to a script file. To use this new export function all you need to do is just install the dbatools from the PowerShell gallery and then run the PowerShell command:
Export-Dbalogin -source <instance name> -FileName C:\temp\SQLlogins_and_permissions.sql
Where <instance name> is the name of the SQL Server instance in which you want to export the logins and permissions.
Next time you need to migrate logins and permissions consider using the Export-Dbalogin function to generate a script to move your logins and associated permissions to a new SQL Server instance.
» See All Articles by Gregory A. Larsen