Azure SQL Server Database Permissions

Cherise Woods 26 Reputation points
2022-04-19T16:01:12.21+00:00

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?

Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 33,331 Reputation points MVP
    2022-04-19T17:01:49.147+00:00

    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;
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Cherise Woods 26 Reputation points
    2022-04-19T17:47:44.333+00:00

    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.