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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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.



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