2

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`
  • You can also try something like DBHistory to know what changed, when and by whom. – Remus Rusanu Apr 24 '17 at 15:47
9

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;
  • I went with your second bullet and that worked for my requirements. However, I failed to state in the questions they need to be able to create tables/views/functions etc in the custom schema. How can I accomplish this? – Jeff Apr 21 '17 at 15:49
  • You'll want to include the CREATE\ALTER\DROP permissions in your GRANT (I've updated my answer to include them) – AMtwo Apr 21 '17 at 15:52
  • I ran this statement GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,CREATE,ALTER,DROP ON SCHEMA::custom TO dev and received this error Incorrect syntax near 'CREATE' – Jeff Apr 21 '17 at 15:55
  • Sorry--that's what I get for trying to respond while I was on call. They should have permission to create tables in the custom schema with the existing permissions. – AMtwo Apr 21 '17 at 16:02
  • I realized I never ran the grant control server command. Verified this was able to do what I want. – Jeff Apr 24 '17 at 15:30
0

I'm going to answer this in two parts.

What you should do

Honestly I don't think anyone but a DBA in charge of the instance should have sysadmin or even control server. I've only very rarely seen situations where you need to go past the following:

  • Read
  • Write
  • Execute
  • ddl_Admin
  • view definition

In this case in order to grant the permissions you want you can do the following:

EXEC sp_addrolemember 'db_datareader','dev'
EXEC sp_addrolemember 'db_datawriter','dev'
EXEC sp_addrolemember 'db_ddladmin','dev'
GRANT EXECUTE, VIEW DEFINITION TO dev
DENY ALTER ON SCHEMA::dbo TO dev

What you may have to do

I understand that sometimes you don't have an option. Politics, whatever, require sysadmin for everyone. Although I'll say again this is a horrible idea. That said, you can use a DDL trigger (or policy based management) to prevent changes to dbo. It will basically bypass security.

I have an example of a DDL trigger doing something similar here and a policy here.

In both cases they were demo code where I prevented the use of nolock. That said you can still probably use them as a template. You'll need to be careful to include in your code the ability to allow your vendor permission to make the changes. Here is an example (untested) of how it might look:

CREATE TRIGGER No_DBO_Changes
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE,
    CREATE_FUNCTION, ALTER_FUNCTION,
    CREATE_VIEW, ALTER_VIEW,
    CREATE_TRIGGER, ALTER_TRIGGER
AS
    IF ORIGINAL_LOGIN() != 'vendoruser'
    BEGIN
        IF EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') = 'dbo'
        BEGIN
            ROLLBACK
        END
    END
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.