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 , , , , , , , , , , | 6 Comments

6
Leave a Reply

avatar
3 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
uday arumilliNarasimhaJoannaManu Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Manu
Guest
Manu

Thanks for sharing the useful scripts

Joanna
Guest
Joanna

this is just what i need !! Thank you!

Narasimha
Guest
Narasimha

Is there anyway to run the script over all database in single click