Mapping of multiple server logins to one database user

Question

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology).

Is it possible to log multiple server logins (sql server authentication) to a single database user (which has permissions assigned by being member of a database role)?

I have dozens of sql logins (sql server authentication) which need to read one setting from a central database and I'd rather map all these logins to a single DB user in the target database than create an own DB user for each login.

If yes, what would be the correct T-SQL syntax?

Answer 1

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]