I have a vendor solution that uses a sql server (2008r2) database. We are allowed to do anything we want in our own schema, but cannot modify dbo objects without vendor permission. Any customization we make is in our own schema (cust). We have full control of this server and have always allowed sysadmin rights to any developer. Lately, I've noticed these rules have not been followed, so I want to see if I can set up permissions to stop this in the first place.
Here is what I would like to accomplish:
Tables: read/write to all, alter cust schema only, view design
Views/Sprocs/Functions: view any definition, create or alter cust schema only
I created a new user (dev) and applied the following:
deny alter on schema::dbo to dev grant alter on schema::custom to dev grant view definition to dev
Will this accomplish what I want, or am I missing something else?
Solution (thanks to AMtwo)
use master go create login dev with password = 'test', check_expiration = off, check_policy = off grant control server to dev use CustomerDB go if not exists(select * from sys.database_principals where name = 'dev') begin create user dev for login dev grant select, insert, update, delete, execute, alter on schema::custom to dev grant view definition to dev deny alter on schema::dbo to dev -- deny other schemas here end`