#DecodeITeS

Database roles are security principals to control access of other database principals. Database roles are database scoped and provide database-level permissions to logins.
o Fixed / Built-in / Default Database Roles: Microsoft SQL Server comes up with 9 fixed or default Database roles.
• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawrite

db_owner
• db_owner as the name implies gives you permissions of the database owners.
• Users with db_owner can do anything inside the database.
• User with db_owner role detrudes the security checks and restrictions made at user levels.
• db_owner role and dbo user both give you DB owner level permissions but still, both are not the same.
• Logins with sysadmin server role automatically map to dbo user.
• Users maps to dbo will bypass all security checks whereas a user with db_owner role still needs to face security checks.
• DBAs and security admins should consider proper justifications before providing this role.

db_securityadmin
• db_securityadmin is similar to securityadmin fixed server role with a scope limited to database
• Users with db_securityadmin manage role membership, securables
• Very Rarely used ad DBAs usually manage security with sysadmin roles connect as dbo user in the database
• Can not alter db_owner role and user with db_owner role

db_accessadmin
• db_accessadmin also used to manage database security
• db_accessadmin role can’t permission to assign and revoke DB access
• db_accessadmin can also create new users or drop existing users
• User with db_accessadmin and db_securityadmin can manage complete database security
• Again very rarely user database role


db_backupoperator
• db_backupoperator role gives you permission to take database backups
• db_backupoperator give permissions of native SQL Server backup. In case you are using a 3rd party tool can't be managed using this role
• Again very rarely user database role

db_ddladmin

• db_ddladmin role allows you to create \ drop \ alert database objects regardless the object owner
• db_ddladmin role is usually given to database developer in a non-production environment
• db_ddladmin role can’t alter the object permissions
• A BIG NO for db_ddladmin on production systems


db_datareader
• db_datareader role provides permissions to execute SELECT statement against all tables & views
• Application reports may need this role
• Granting this role will give view permissions on your data. DBA should consider the data security perspective before assigning this role to anyone.

db_datawriter

• db_datawriter role gives you implicit access to INSERT, UPDATE, and DELETE against all tables and views in a database.
• db_datawriter role does not mean for developers
• db_datawriter role is assigned to application account to insert new data in tables

db_denydatareader
• db_denydatareader is opposite to db_datareader. This role denies SELECT access on all tables & views


db_denydatawrite

• db_denydatawrite is the opposite of db_datawrite. This role denies INSERT, UPDATE, or DELETE access on all tables & views

What you can grant to User-Defined Database Roles?

You can grant the only Database -level (DATABASE, SCHEMA) permissions to user-defined database roles. You can execute sys.fn_builtin_permission to list down all permissions. You can create a role with extra permissions over fixed role or can create a role with no fixed role permissions and only specific permissions



SELECT * FROM SYS.FN_BUILTIN_PERMISSIONS(DEFAULT)
WHERE CLASS_DESC IN ('DATABASE','SCHEMA')
ORDER BY CLASS_DESC, PERMISSION_NAME



How to create a Database Role using SQL Server Management Studio?

USE [DWQueue]
GO
CREATE ROLE [TestDBRole_TSQL]
GO
USE [DWQueue]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_accessadmin] TO [TestDBRole_TSQL]
GO
USE [DWQueue]
GO
ALTER ROLE [TestDBRole_TSQL] ADD MEMBER [SQLAuthUser]
GO
use [DWQueue]
GO
GRANT ALTER ON [dbo].[MessageQueueActivate] TO [TestDBRole_TSQL]
GO










a. Script to list permissions of customized roles

SELECT SYS.DATABASE_ROLE_MEMBERS.ROLE_PRINCIPAL_ID, ROLE.NAME AS ROLENAME,
SYS.DATABASE_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID, MEMBER.NAME AS MEMBERNAME,MEMBER.TYPE_DESC,PERMISSION_NAME,STATE_DESC,CLASS_DESC
FROM SYS.DATABASE_ROLE_MEMBERS
FULL JOIN SYS.DATABASE_PRINCIPALS AS ROLE
ON SYS.DATABASE_ROLE_MEMBERS.ROLE_PRINCIPAL_ID = ROLE.PRINCIPAL_ID
FULL JOIN SYS.DATABASE_PRINCIPALS AS MEMBER
ON SYS.DATABASE_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID
FULL JOIN SYS.DATABASE_PERMISSIONS PERMISSIONS
ON PERMISSIONS.GRANTEE_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID
WHERE MEMBER.NAME='TESTDBROLE' -- THIS WILL LIST ALL PERMISSIONS ASSIGNED TO TESTSERVERROLE USER DEFINED SERVER ROLE
OR ROLE.NAME ='TESTDBROLE' -- THIS WILL LIST ALL LOGINS ADDED UNDER TESTSERVERROLE USER DEFINED SERVER ROLE

свернуть