Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted September 9, 2019

Using PowerShell to Generate Logins, Server and Database Permissions

By Greg Larsen

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM