How can I manage stored procedure rights of the role in SQL Server

Question

I want to create three SQL Server database roles.

1. That can CREATE, ALTER and EXECUTE all stored procedures in database
2. That can only EXECUTE all stored procedures in database
3. That have no access to any stored procedures in database

I have created the roles, but I'm facing issues while REVOKE their permissions.

I have executed

REVOKE CREATE PROCEDURE TO [ROLE NAME]

to revoke the permissions to create the procedure and it executed successfully.

But I got error while executing this statement:

Error: Incorrect syntax near 'ALTER'.

I am very new to SQL server role rights so I might be completely wrong with my approach.

Please guide me to achieve my goal in correct way.

Thanks

Answer 1

From the documentation Create a Stored Procedure:

Permissions

Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Therefore just giving CREATE PROCEDURE on it's own won't allow you to create a procedure. In fact, giving a ROLE the CREATE PROCEDURE permission, and not ALTER on the schema will result in the below error:

The specified schema name "dbo" either does not exist or you do not have permission to use it.

There is no ALTER PROCEDURE permissions, therefore, for a member of a ROLE to be able to both CREATE and ALTER a PROCEDURE you would need to do:

GRANT CREATE PROCEDURE TO YourRole;
GRANT ALTER ON SCHEMA::dbo TO YourRole; --Replace with appropriate schema name

This, however, will also enable to user to ALTER anyprocedures on said schema. Ut also enable those in the role to ALTER other objects on the schema as well (such as tables) though.

If your ROLE has permissions to ALTER the procedures and you want to remove that, you would need to run the below:

REVOKE ALTER ON SCHEMA::dbo TO YourRole;

This will, as mentioned, also revoke their ability to ALTER any other objects on said schema.

Remember, REVOKE doesn't DENY, it simply means that the USER won't inherited that permission from that ROLE any more. If the USER has the permission from a different ROLE, or they have the permission themselves, they will be able to continue to use the permission. If you must stop a USER from performing an action, regardless of any other permissions, they must have the DENY permission.

Answer 2

1) That can CREATE, ALTER and EXECUTE all stored procedures in database

That's the db_owner role, or the CONTROL permission on the database. Anyone with all those permissions can escalate their own privileges to a database-level admin. So don't try.

2) That can only EXECUTE all stored procedures in database

GRANT EXECUTE TO [SomeRole]

3) That have no access to any stored procedures in database

A user has no access to any stored procedure unless you grant permissisions or add them to a role that has permissions.