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