2

In SSMS, since I can't assign multiple login objects to a single user object, what is a best practice to achieve an equivalent security model?

For example, I have 20 logins and want to give them all the same permissions on a single database:

  • Do I have to create 20 user objects in that database?
  • Do I have to touch all 20 Login objects?
Improve this question
2
  • You can use a Windows group for the login, assign one user to the group, and then, outside SQL Server, assign Windows users to the group. Alternately, you can use role-based permissions and assign the users to the role and assign the role the permissions it needs. There's a huge amount of depth to the SQL Server security system. – Bacon Bits Nov 28 '15 at 14:20
  • @BaconBits I am not using Windows authentication. You may not have read the question: I have one User and 20 Login objects. Assigning permissions to the User object is not the issue. The issue is finding a Best Practice to assign multiple Login objects to a single User object or an equivalent strategy. – Delmer Nicholson Nov 28 '15 at 15:32
4

Since you are using SQL Server local logins, they cannot be grouped. Each login is independent and each login needs a user in the database.

Where you can get some saving in specifying rights is to create roles, to which you grant, revoke, or deny rights. (Note that deny rights override grant rights.) Then use these roles to give rights to the users.

This way a role contains the specifications of permissions, rather than applying those to every user individually.

After this you can add the users to the roles that user needs. For example:

  • Allen - ReadOnly role
  • Sally - ReadOnly role, MaintainAccounts role
  • Zenubia - UpdateNonSecureData Role
  • etc.

This preserves your security settings at the role level, so you should not need to repeat those grants. Just add or remove a user from a role, when needed.

Improve this answer
3

Create a database role and add those users to the role (assuming as per the previous comment that you can't create an AD group). Grant permissions on the appropriate objects to the role.

create role MyRole

grant EXECUTE to MyRole    
-- or whatever permissions you want to give

EXEC sp_addrolemember 'MyRole', 'thisUser'    
-- add the relevant user name "thisUser" to the role "MyRole"

NB: you cannot map more than one "login" (server level) to a database "user" (db level) because each database user is created with a mapping to a specific server login. The model is fundamentally that a login in a database corresponds to 1 (or 0 in the case of contained databases...) server logins.

Edited to add: To make it more explicit - you have to create a database user for each of those logins (assuming you can't use windows authentication). But then you should give permissions via roles rather than individually if those 20 users are 'equivalent' with their security.

Improve this answer