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?
Best Answer
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.