0

I am having issues with the user accounts in SQL Server v 11.0.6020. I need to run a script that will allow for the copy of the principal user account: 'TrainID1' and all of the user rights and permissions so that when I get the other users logged in, they can access the DB's and run the applications that connect to the SQL DB's. So I have the user: 'TrainID1', and that user connects to the SQL DB: 'TrainID_01' DB, so I need to have TrainID2 to 42 user accounts allowed in SQL Security for all of the other Data Bases like: TrainID_02, TrainID_03 and so on...

2 Answers 2

3

I recommend you have a look at the procedure sp_dbpermissions by Kenneth Fisher.

I have been using it and it allows you to list all the permissions of a particular user in a database, and gives you all the scripts to copy all those permissions and apply them on a different database.

In case you want to apply them on a different server as well, that is fine too, but don't forget to create the server login first.

2

You can accomplish this via the dbatools powershell module.

There is an Export-SqlLogin cmdlet that will do nearly exactly what you describe. From the documentation, this cmdlet will:

Exports Windows and SQL Logins to a T-SQL file...with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc).

If you need to replicate the exported login N times, you could modify the exported T-SQL script. However, you likely need to consider that all N can't be created with the same SID, so you may wish to simply remove the SID when you rename/clone the login.

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.