Recently while working with database security, I learned database roles as how the each role used. Apart from the server level roles if we need to require to assign access/rights to particular databases level then we need to go through database level roles.

Following are the fixed database level roles as per MSDN,
db_owner : Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin : Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin : Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator : Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin : Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter : Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter : Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
































You can see the image for all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with example.

Here i am creating required objects used for demo, So let's do that.

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Now here i want assigned to the access to user as following,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.

Workaround 1:
If we assigned fixed database roles to User like db_datareader and db_datawriter for particular database, then user can perform all DML operation on all tables, views for the database. Even if we assigned EXECUTE permission to user then user can execute all the stored procedures.

After all these permission we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.

Workaround 2:
Instead of doing above such stuffs, We will create a new custom database level roles and assigned requited access to user for the objects.

USE demo
GO

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO

CREATE ROLE [SupportRole] 
Go

GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO

EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO

Let's connect the SQL Server instance with this newly created user and see the access rights,

USE demo
GO

PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample1'
GO

PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample2'
GO

PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO

PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO


PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO

You can see the below image to see the access of running user,





























Main benefit of the custom database role is role can be assigned to multiple users. You can see below script where i have assigned same role to different user. So once role created it can  be assigned to multiple users.

USE demo
GO

CREATE LOGIN [DBAUser] 
WITH PASSWORD=N'DBAUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO

EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO

Hope you liked this post.