Prevent Modification of dbo objects while allowing other schemas to be modified

Question

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`

asked 2017-04-20 by Jeff

---

Answer

We have full control of this server and have always allowed sysadmin
rights to any developer.

The sysadmin privilege effectively short-circuits any other permissions. If a user is a member of the sysadmin fixed server role, then any DENY permissions will be ignored.

You can work-around this by revoking sysadmin and instead using the CONTROL SERVER permission. CONTROL SERVER is similar to the sysadmin fixed server role, except that CONTROL SERVER will obey DENY permissions.

GRANT CONTROL SERVER TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
DENY ALTER ON SCHEMA::dbo TO dev

If you want to completely undo the developers’ need to have high-level access on the server (which is a pretty good idea), then you could take two approaches:

• Grant wide access to the database, then explicitly deny permissions on dbo.

  USE MyDB
  GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON DATABASE::MyDB TO dev
  GRANT VIEW DEFINITION TO dev
  DENY ALTER ON SCHEMA::dbo TO dev
  EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
  

• Explicitly grant just the permissions the users require; do not grant ALTER permission in dbo.

  USE MyDB
  GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA::custom TO dev
  GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON SCHEMA::dbo TO dev
  GRANT VIEW DEFINITION TO dev
  EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
  

Ultimately, you want to follow (or be close to) the principal of least privilege. But you also have to balance complexity and manageability.

I’d suggest you create a few test scenarios to test your permissions when running as dev, so that you can make sure the user has the right permissions. You can do this using EXECUTE AS LOGIN:

EXECUTE AS LOGIN='SomeDeveloper';
CREATE TABLE custom.AM2 (foo BIT);
GO
CREATE PROCEDURE custom.GetAM2 AS SELECT * FROM custom.AM2;
GO
DROP TABLE custom.AM2;
DROP PROCEDURE custom.GetAM2;
GO
REVERT;

answered 2017-04-20 by Andy Mallon