0

I am new to database administration and work within a small team of data analysts trying to modernise. We have recently set up an Azure SQL DB server we are accessing using SQL Server Management Studio.

We have an admin account and I have created a user login for each of us for day-to-day work. I would like all user logins to have the ability to create, drop, select etc. freely for all objects where the schema is unspecified or specified as "dbo" but to only have read-only permissions where the schema is specified as "testschema". I have attempted this using the following code from the admin account:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'testschema')) 
BEGIN
    EXEC ('CREATE SCHEMA [testschema] AUTHORIZATION [dbo]')
END

---- Create permissions group:
EXEC sp_addrolemember 'team_member', 'Mel';

-- GRANT BLANKET PERMISSIONS
GRANT CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES, UPDATE ON SCHEMA::dbo TO team_member;

-- REVOKE PERMISSIONS TO SPECIFIED SCHEMAS
DENY CONTROL, ALTER, DELETE, EXECUTE, INSERT, REFERENCES,
          UPDATE ON SCHEMA::testschema TO team_member;

-- ALLOW TO SELECT AND VIEW ON THESE SCHEMAS
GRANT SELECT, VIEW DEFINITION ON SCHEMA::testschema TO team_member;

I have been playing around with granting, revoking and denying permissions to myself all morning but I can't seem to get the permissions settings correct so that my user account can create and drop tables with the "dbo" schema and see and select from tables with the "testschema" schema, but can't drop/alter/delete tables with the "testschema" schema.

Can anyone advise on the correct syntax for doing this?

EDIT: I have altered my original code slightly to DENY rather than REVOKE and instead of general permissions I have set dbo-specific schema permissions. The permissions are given as follows and appear to be correct, but team_member users still can not see or select from any testschema tables (there are no other currently-used schemas beyond "testschema" and "dbo").

permissions table view

6
  • DENY instead of REVOKE Apr 23, 2019 at 13:58
  • Thanks! I have tried DENY instead of REVOKE, but now user "Mel" can't even see tables using the "testschema" schema in the object explorer, and attempting a SELECT statement results in: "The SELECT permission was denied on the object 'keep_this', database 'DATABASE', schema 'testschema'." Is there any way I can give read-only access to this schema?
    – Mel
    Apr 23, 2019 at 14:03
  • Try your first grant ON SCHEMA::dbo and then the second one without any revoke/deny commands Apr 23, 2019 at 14:08
  • You mentioned you are new so this could be useful Introduction to SQL Server Security Apr 23, 2019 at 14:21
  • Thanks, I have tried this but the user is still able to drop "testschema" tables in that case.
    – Mel
    Apr 23, 2019 at 14:21

1 Answer 1

2

A combination of DENY and a GRANT works for me.

For example:

GRANT SELECT ON schema::[dbo] TO [user_name]
DENY SELECT ON schema::[other_schema] TO [user_name]
DENY SELECT ON schema::[schema_1] TO [user_name]
GRANT SELECT ON schema::[schema_Safe] TO [user_name]

If you need to prevent to drop the table by some user, try this:

DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user;
3
  • These worked, thanks! I think the problem was including "CONTROL" in any of the overall or schema-specific permissions. As soon as I took it out and denied or granted more specific permissions only they worked fine.
    – Mel
    Apr 30, 2019 at 12:52
  • I am glad to hear that this works for you... Give an upvote if this answer useful to you.
    – CR241
    Apr 30, 2019 at 23:08
  • I get the error Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself using GRANT SELECT ON schema::[other_schema] TO [user_name]
    – Kiquenet
    Sep 9, 2020 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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