Control database developer permissions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Control database developer permissions?

Author

Topic

pjstreiff
Starting Member

12 Posts

Posted - 2008-09-07 : 10:05:12

I need to be able to restrict db developers privilege in a database by granting CREATE, ALTER, DROP permission on 'programming' objects like sp's, functions, triggers, but deny CREATE, ALTER, DROP permission on tables and views.

Therefore, db_ddladmin fixed database role doesn't quite meet the requirement.

All I could think of is creating some kind of 'custom' database role like db_developer and assigning privilege, but it seems to cumbersome and can't be scripted.

Can anyone give me advise or suggestions on how to accomplish this?

Thanks,
Phil

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-07 : 21:08:06

>> can't be scripted.

any action on a SQL Server database can be scripted afaik.

pjstreiff
Starting Member

12 Posts

Posted - 2008-09-10 : 09:22:22

Since I haven't gotten any suggestions on how to accomplish this yet, here is my attempt I came up with:

USE [USER_DATABASE];
GO

CREATE ROLE [db_developer] AUTHORIZATION [dbo]
GO

EXEC sp_addrolemember 'db_developer', 'AD\DB_Developers'
GO

GRANT CREATE PROCEDURE TO [db_developer] -- ALTER & DROP permission implied
GO
GRANT CREATE FUNCTION TO [db_developer] -- ALTER & DROP permission implied
GO
DENY CREATE TABLE TO [db_developer]
GO
DENY CREATE VIEW TO [db_developer]
GO

Let me know if anyone knows a better way, or if you see a problem with this approach.

Thanks,
-pjstreiff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources