I have had some questions recently about how to grant developers certain permissions without giving them sysadmin rights. Hopefully this summary will help you determine how to grant the least possible privileges. The summary is based on SQL Server 2005 but will also apply to SQL Server 2008.
· I would hesitate to grant any more permissions in development than they get in production. This means avoid not only sysadmin but also db_owner where possible.
o This avoids problems where they spend a long time developing something only to find out at the last minute that it won’t work with production permissions.
o If there is any production data on the development system it may be more vulnerable to attack when more people have elevated permissions.
o As an alternative you may want to create an application that lets them submit requests to do things that require elevated permissions. It can log on to SQL Server with the appropriate permissions and perform whatever action they need. It can optionally log this activity, create a change request ticket, email the DBAs, or whatever you like. This should reduce the chance that some elevated permission need makes it into the application because it is much more obvious when they are performing an activity that they or the application will not be able to do in production.
· Generally you will not want to grant CREATE DATABASE permissions to non-DBAs. Creating databases involves OS level permissions and space management, performance considerations, best practice implementations, backups, maintenance, etc. Also, the creator of a database can make themselves a db_owner which is usually more than a developer needs. If you do decide to grant permissions to create databases, the permission is GRANT CREATE DATABASE TO … and/or GRANT ALTER ANY DATABASE TO ….
· The KILL command to kill an existing SPID requires either PROCESSADMIN or SYSADMIN role membership. The PROCESSADMIN role includes both ALTER ANY CONNECTION and ALTER SERVER STATE and the combination of the two are required to use the KILL command.
· To run SHOWPLAN or use the GUI actual/estimated execution plans to see execution plans, you can GRANT SHOWPLAN TO… in the database(s) that contain the objects referenced in the queries. They also need permission to execute the query itself. There is no need to grant anything more than the ability to execute the query if you just want to SET STATISTICS TIME or SET STATISTICS IO. The danger in granting this permission is that the plan could theoretically contain information about data or the schema that would help a hacker.
· To run SQL Profiler you can GRANT ALTER TRACE TO…. The danger is that the user can see information about the schema and sometimes the data that could be used to hack into the system.
· To use Job Activity Monitor, first add the login or group as a user in the MSDB database. Then add them to the operator role:
sp_addrolemember ‘SQLAgentReaderRole’, ‘test1’
· Using the Activity Monitor requires VIEW SERVER STATE and SELECT on sysprocesses and syslocks. The SELECT on sysprocesses and syslocks is granted by default to PUBLIC and therefore everyone, but VIEW SERVER STATE has to be explicitly granted.
· To run the Database Tuning Advisor (DTA) you need SHOWPLAN permissions and the ability to execute the queries in all the databases in the workload. However, if the trace file used as input includes the LoginName data column DTA will try to impersonate the users and therefore permission needs to be granted to each user OR you can avoid collecting the LoginName data column. Right after a new instance of SQL Server is installed, a sysadmin must run DTA once before anyone else can use it initialize some settings.
· To create objects, you have a couple of choices. You can GRANT CREATE TABLE, GRANT CREATE PROCEDURE, etc. in each database. Alternatively you can add them to the db_ddladmin role in the appropriate databases. This will grant them VIEW ANY DATABASE and the database level permissions 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
This query will show you the list of available privileges:
select * from sys.fn_builtin_permissions (DEFAULT)