Search code examples
sqlsql-server-2008database-permissions

Creating database role in sql server that can be mapped to multiple logins

Scenario..

1 database role - 5 developers needs to be mapped with same access.

All 5 developers must login to sql server using there own username and password.

I want all these 5 developers must be mapped to 1 unique database role. So that if I changes permissions of this roles. All the developers are promoted to same permissions.

How to do this.

Currently when I creates a login using wizard It asks me for creating database user along with it. Which I don't want bcoz I want all logins to be mapped to single role.

Solution

  • Never create database logins for individual users.

    First step is to create an NT group:

    Net Localgroup MyDatabaseLoginGroup /Add 
Net LocalGroup MyDatabaseLoginGroup /Add UserName1

    Alternatively you could use a Domain login for this.

    Second step is to create a Server and Database login for the group DOMAIN\MyDatabaseLoginGroup. Call it MydatabaseLoginGroup.

    Third step is to create a database Role, DatabaseRoleName and make the group a member of it.

    You should manage all internal permissions via database roles. You can make the Database logins map to as many roles as you like. Ideally each role would encompass a reasonable set of required functionality which can be granted as a block.