Azure SQL Server Database Permissions

Question

Good day,
I'm having trouble getting permissions correct for an external user.

I was asked to provide a user with permissions to select from dbo schema, create views, procedures and functions and main dbo and 4 other schemas, but not be able to insert, delete or update any of the data from the dbo schema.

I created the user alter and execute on all schema but deny update, insert and delete on dbo schema tables.

I have a hand full of views in the dbo schema that pulls data from the dbo schema.

The user tried to run this statement and got an error:

Alter schema test transfer dbo [TestTable]. -- this is a view

user received a permission error.

Please advise what permission I can add to this user to meet requirement?

Answer 1

Try to create a new role as shown below and assign the user to the role. Assign the user permissions to read data (GRANT SELECT) and execute programming objects.

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GO GRANT SELECT TO [db_executor]

GRANT ALTER ON SCHEMA::[dbo] TO [db_executor];

GRANT EXECUTE TO [db_executor]
GO

GRANT CREATE VIEW TO [db_executor];
GO

GRANT CREATE PROCEDURE TO [db_executor];
GO

GRANT CREATE FUNCTION TO [db_executor];
GO

ALTER ROLE db_executor ADD MEMBER MyUser;

Answer 2

Thanks for your response.

The users was trying to move a view from one schema to another but getting permission issues. I've already created the user and provided the create and alter permissions but still he has an error. I found a site that says to move from one schema to another you need to have control on the schema. I added control to the schema required (less the dbo) and it worked.

Thanks for your response.