Scipt User roles and permissions

Question

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...

Answer 1

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.

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