Sql-server – SQL Server setting up logins for backup site

First to make sure of terminology. A Login is an instance level security principal (sys.server_principals) and a User is a database level security principal (sys.database_principals). They are joined together by an SID (security identifier). If you look in the system views above you can see how they are joined together in a 1:1 format by SID. That's 1 Login to 1 User in a database. A Login can have multiple Users but they must be in different databases.

So you will need to create a User in your database for each Login that you want to have access to it. It's a simple enough command.

CREATE USER [UserName] FROM LOGIN [LoginName]

From there you can put all of your users into a single (or multiple) Role. A role is a container that has permissions and shares those permissions with each User (for database roles) or Login (for instance roles). There is a built in database role called db_datareader that gives read access to every table and view in the database. You could add all of your users to that role. However, a better idea would be creating a new role and adding it to the db_datareader role. Then add all of your users to the new role. The benefit here is that if you want to add additional permissions to the group you can by simply changing the permissions on the role.

Create the role by:

CREATE ROLE RoleName

Add a user to a role (or one role to another)

EXEC sp_addrolemember 'RoleName','UserName'

or if you are in 2012 or higher

ALTER ROLE [RoleName] ADD MEMBER [UserName]

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.