Understanding SQL Server Database Roles - Fixed & User Defined
#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
Watch video Understanding SQL Server Database Roles - Fixed & User Defined online without registration, duration hours minute second in high quality. This video was added by user Decode ITES 16 June 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,909 once and liked it 17 people.