Assigning Permissions to Individual Users from Group Logins

Let’s say we have Developer’s Group and one developer in that group needs higher permissions than others on one database.
In that case instead of creating dedicated login for the user, we can extract the login from the group and assign the required permissions on the database.

For example: assume company ABC has developer group and all developers have same permissions except that one senior developer needs higher privileges on one database.

Dev. Group Login: ABC\Developers and senior Developer: ABC\Rose

ABC\Rose authenticates herself as the part of the Dev. group on Server level and on the database level, you can create a database user and associate it with the login ABC\Rose.

The login ABC\Rose does not explicitly exist on the server but as a part of the DEV Group.

The way this works is that SQL Server knows that connection came from ABC\Rose and ABC\Rose is part of Developer group. So, ABC\Rose is authenticated on the server level and granted access. On the database level, we have to create a user for login ABC\Rose and this lets ABC\Rose to have access to the database with more permissions granted for the user.

Use [Master]
GO
Create Login [ABC\Developers] from windows WITH DEFAULT_DATABASE=[master]
GO
USE [DatabaseName]
GO
CREATE USER [ABC\Rose] FOR LOGIN [ABC\Rose]
GO
EXEC sp_addrolemember N'db_dataWriter',N'ABC\Rose'