I need to duplicate all Logins and their associated database-level permissions from one server to another. I am using SQL Server 2008 R2 for Server A and SQL Server 2012 for Server B I am able to find a few different scripts to transfer Logins from server A to server B. However, I nee to also transfer database-level permissions for each Login.

I've reviewed the following for transferring Logins:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror http://support.microsoft.com/kb/918992

The SQL Soldier solution seems to work fine.

The same databases that reside on server A also reside on server B. how can I automate/script the Login and permissions transfer from one server to another?

  • 1
    how can I automate/script the Login and permissions transfer from one server to another? You can use sqlcmd and the top portion (only step 1) of the script from here or using Powershell - script from here
    – Kin Shah
    Commented Sep 24, 2014 at 1:14
  • How are the databases on Server A and Server B kept in sync? If you're using mirroring or AAG's then the database level is already done
    – Greg
    Commented Mar 29, 2016 at 22:14
  • I am trying to use the approach in kb/918992 but I am receiving the error: The EXECUTE permission was denied on the object 'sp_help_revlogin', database 'master', schema 'dbo'. Commented Apr 12 at 17:38

2 Answers 2


I created a series of stored procedures that accomplished a similar goal (with some powershell for flavor) based on this article Claire-Hsu - SQL Logins

I don't have a blog of my own (I know, I know) so I can't share my precise solution with you. I essentially created three stored procedures. One for the server logins, one for database and another for database object permissions.

Ah, shared on my one drive. Link to files It's the stored procedures you need. I have them in my DBA database. I then use Powershell to get a list of logins, then query the stored procedure, save the script to a file and repeat. You could easily change it to run the script on a different server.


I've got a stored procedure you can use for the purpose.


When you run it it pulls all of the permissions for the database into three result sets. The first is a list of principals, the second is role membership and the last is direct permissions (GRANT EXEC ON SP for example). Each result set includes a script to remove said permission and another to create it. Best of all it was designed originally as a research tool so there are lots of ways to restrict the data. For example the parameter @LoginName will only return principals that match a given server principal name (as opposed to the database principal name) and even better for your purposes the All option for the @DBName parameter. It not only runs the results for all databases (including system ones) but includes a USE statement at the beginning of the drop & create statements.

That way if you need to generate all permissions for a given login you can run this:

EXEC sp_DBPermissions 'All',@LoginName = 'loginame'

Note: This does not include the CREATE LOGIN statement. To do that either use SQL Soldiers script or there is a sp_SRVPermissions as well.

  • Is there nothing from Microsoft to do this during a migration?
    – variable
    Commented Apr 7, 2022 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.