11

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?

Improve this question
3
  • When you say "server logons" do you mean Windows accounts or SQL server accounts? – Phil Sumner Jan 28 '14 at 14:23
  • I am talking about SQL server accounts. Will edit the question to clarify – leepfrog Jan 28 '14 at 14:29
  • How about you create a ROLE and then add users to the role. This way you just grant permissions to the role. This will help you manage security in a better way. Also, what is the sql server version - 2008R2, 2012 ? – Kin Shah Jan 28 '14 at 14:39
20

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]
Improve this answer
1
  • Thanks for the detailed explanaition and for providing background information Kenneth, what I was unclear about was That's 1 Login to 1 User in a database. – leepfrog Jan 28 '14 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.