13
Sep

Reasons not to grant db_owner
DBO can do some nasty stuff:-

  •  CHANGE RECOVERY MODEL
  •  BACKUP DATABASE
  •  ALTER ANY SYMMETRIC KEY
  • TURN ON/OFF AUTOSHRINK
  •  ADD FILE
  • AUTO_UPDATE_STATISTICS
  • SET RECURSIVE_TRIGGERS
  • DROP DATABASE
  • SET OFFLINE

 

If you must create database objects, view showplans/dynamic tables or more but do not want to give away ‘the keys to the city’, consider creating a custom role.


— Avoid using the db_owner role by creating a new role with enhanced permissions

use somedatabase
go

CREATE ROLE [enhanced_role]  AUTHORIZATION [dbo]
GO

— creates
GRANT CREATE PROCEDURE TO [enhanced_role]
GRANT CREATE TABLE TO [enhanced_role]
GRANT CREATE VIEW TO [enhanced_role]
GRANT CREATE RULE TO [enhanced_role]
GRANT CREATE SCHEMA TO [enhanced_role]
GRANT CREATE TYPE TO [enhanced_role]
GRANT ALTER ANY SCHEMA TO [enhanced_role]
–DML
GRANT UPDATE TO [enhanced_role]
GRANT DELETE TO [enhanced_role]
GRANT INSERT TO [enhanced_role]
GRANT EXECUTE TO [enhanced_role]
GRANT SELECT TO [enhanced_role]
GRANT REFERENCES TO [enhanced_role]
–extras
GRANT SHOWPLAN TO [enhanced_role]
GRANT VIEW DATABASE STATE TO [enhanced_role]
go

Some Notes:

AUTHORIZATION [dbo] means the role is owned by dbo.   If no user is specified, the role will be owned by the user that executes CREATE ROLE.
There are a lot more possibilities for beefing up this user.

GRANT ALTER ANY APPLICATION ROLE TO [enhanced_role]
GRANT ALTER ANY ASSEMBLY TO [enhanced_role]
GRANT ALTER ANY DATABASE DDL TRIGGER TO [enhanced_role]
GRANT ALTER ANY DATASPACE TO [enhanced_role]
GRANT ALTER ANY FULLTEXT CATALOG TO [enhanced_role]
GRANT ALTER ANY MESSAGE TYPE TO [enhanced_role]
GRANT CREATE AGGREGATE TO [enhanced_role]
GRANT CREATE ASSEMBLY TO [enhanced_role]
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [enhanced_role]
GRANT CREATE DEFAULT TO [enhanced_role]
GRANT CREATE FULLTEXT CATALOG TO [enhanced_role]
GRANT CREATE FUNCTION TO [enhanced_role]
GRANT CREATE ROLE TO [enhanced_role]
GRANT CREATE SERVICE TO [enhanced_role]
GRANT CREATE SYNONYM TO [enhanced_role]
GRANT CREATE XML SCHEMA COLLECTION TO [enhanced_role]
GRANT VIEW DEFINITION TO [enhanced_role]

0 No comments

Leave a Reply

Your email address will not be published. Required fields are marked *