May 31, 2019 at 6:54 am
Hi,
Just wondering if anyone can help me understand Schema permissions a bit better.
I am using Azure SQL database and there is a requirement to create a new service account user that will have permissions on only one schema and it shouldn't be able to access any other schema with in the database.
So far I have set it as an owner and granted it the following permissions
GRANT SELECT, INSERT, UPDATE, DELETE, EXEC, ON SCHEMA :: <schema> TO <user>
GRANT CREATE TABLE TO <user>
GRANT CREATE VIEW TO <user>
GRANT CREATE PROCEDURE TO <user>
GRANT CREATE FUNCTION TO <user>
So with these permissions will it only be able to create objects within the defined schema as long as the account is not the owner of any other schema?
Also if thats the case whats the best way to make sure the account is only the owner of one schema?
May 31, 2019 at 2:36 pm
What you have done sounds correct to me. But you don't have to be an owner of a schema to affect objects within it. you just need relevant permissions on underlying objects like you have done. If you remove all other access (other than public) to that user on the db you should be fine but test this. I think it will probably still be able to see objects (such as select * from sys.tables) but that's about it.
Adam Zacks-------------------------------------------Be Nice, Or Leave
May 31, 2019 at 2:47 pm
you can also use the following to test
EXECUTE AS USER = '<user>'
/* Do Something here */
REVERT -- this changes it back to you
June 5, 2019 at 10:38 am
Thank you for the responses.
So in the end I just made the user account an owner of the schema as it turns out they didn't need to create objects.
Also found out I didn't need this statement GRANT SELECT, INSERT, UPDATE, DELETE, EXEC, ON SCHEMA :: <schema> TO <user> as I made them an owner they get these permissions as standard.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply