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").