How to Script Login and User Permissions in SQL Server

How to Script Login and User Permissions in SQL Server

How to Script Login and User Permissions in SQL Server

Migrating login and user permissions to a new instance is one of the most common tasks for a SQL DBA. This post “How to Script Login and User Permissions in SQL Server“ can help you in scripting the login and the user permissions for a given database. Most of us knew that there is a script “sp_help_revlogin” available for migrating SQL Server logins which can also handle the passwords for SQL Server logins. Just to summarize the topic we are also giving the MS suggested script.

Server / Instance Level

  • Script Logins with Passwords
  • Script Login Server Roles
  • Script the Server Level Permissions

Database / Object Level

  • Script User Creation
  • Script User Database Roles
  • Script the Database Level Permissions
  • Script Object Level Permission

 

Here is the script for generating Login creation script for the given SQL Server instance. Here is the reference from the MS site. https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

There is another beautiful script provided by JP Chen and it also working for login migration.

Server / Instance Level:

 

Database / Object Level:

 

Summary:

The post “How to Script Login and User Permissions in SQL Server” can help you in transferring logins and users between SQL Server instances. Please do comment on the below comment box if you find any issues in code.

Download Scripts:

How to Script Login and User Permissions in SQL Server_Logins

How to Script Login and User Permissions in SQL Server_Users

Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , | 4 Comments

Leave a Reply

4 Comments on "How to Script Login and User Permissions in SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
Manu
Guest

Thanks for sharing the useful scripts

Joanna
Guest

this is just what i need !! Thank you!

wpDiscuz