Need advice: user-defined database / server role?...

[alexjones]

I am a database designer/developer (and not a DBA) working for an extremely security-conscious company. OLTP Production is still on SQL Server 2000. We have SQL Server 2005 set up in a development environment. I am tasked with developing SSIS ETL packages for an SS2K5 data mart that takes its data from SS2K on another server. I am also Content Manager for SSRS2K5 on the same development box.

It's apparently against policy to assign server roles to a developer - even in the development environment.

I need read/write access to tables, need to be able to create and manage stored procedures and user-defined functions, need to be able to create and test SSIS packages. It would be great to have bulk-insert permissions and the ability to see SQL Agent jobs.

I'm looking for role definitions that will let me do my job and still satisfy the company's security policies. Any suggestions would be appreciated.

 

[mkal]

This is from Books Online SQL 2005

Fixed Database Role Database-level Permission Server-level Permission
db_accessadmin
Granted: ALTER ANY USER, CREATE SCHEMA
Granted: VIEW ANY DATABASE

db_accessadmin
Granted with GRANT option: CONNECT


db_backupoperator
Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
Granted: VIEW ANY DATABASE

db_datareader
Granted: SELECT
Granted: VIEW ANY DATABASE

db_datawriter
Granted: DELETE, INSERT, UPDATE
Granted: VIEW ANY DATABASE

db_ddladmin
Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
Granted: VIEW ANY DATABASE

db_denydatareader
Denied: SELECT
Granted: VIEW ANY DATABASE

db_denydatawriter
Denied: DELETE, INSERT, UPDATE


db_owner
Granted with GRANT option: CONTROL
Granted: VIEW ANY DATABASE

db_securityadmin
Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION

Being a developer myself I would recommend assigning yourself to the dbo role. There are some gotcha's that can arise from limiting a developer to a ddl role for creating objects.

There are Server Roles as well.
Fixed Server Role Server-level Permission
bulkadmin
Granted: ADMINISTER BULK OPERATIONS

dbcreator
Granted: CREATE DATABASE

diskadmin
Granted: ALTER RESOURCES

processadmin
Granted: ALTER ANY CONNECTION, ALTER SERVER STATE

securityadmin
Granted: ALTER ANY LOGIN

serveradmin
Granted: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE

setupadmin
Granted: ALTER ANY LINKED SERVER

sysadmin
Granted with GRANT option: CONTROL SERVER

Hope this helps.