Copy SQL Logins and Permissions From One Server to Another

Question

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?

Answer 1

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.

Answer 2

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

sp_dbpermissions

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.