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
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 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]