We are currently moving to SQL Server and are setting up initial permissions for our environment. Does anyone have any best practices to set up:

We plan to give an application its own user for security purposes and then allow that user to have access to what is needed.

How do we set it up so that developers can develop their stored procedures and get permission to what they need without getting blocked by permissions issues.

Is there a way to allow any stored procedures to have execute permissions if it belongs to a specific schema?

Essentially we have 4 Databases with many tables with many applications that access the various tables. We want to make it easy to manage but also easy to know what each application has permissions to do. For example we only want ReadOnly access for those tables that the application only needs readOnly to and we we want update/delete/insert to those that require it..

Any documentation on best practices out there? We have a lot of stored procedures that access tables from all the different database as well.

What security should an application user have so that when a new stored procedure is added they don't need to be granted specific privileges for the new stored procedure ? is that even possible? What if I create a new schema and add all the stored procedures for this application inside of it. Can I grant execute to the schema level ? thoughts?

Thanks in advance.

CodePudding user response：

Before I say anything, I'd recommend creating a database role for your application user, placing the application user into it, and then granting permissions to the role (and not the user directly). Why? Some day the credentials for the app user will need to be rotated (e.g. password got leaked into a log, disgruntled ex-employee, etc). If you grant to the role, the procedure for doing this without application downtime is:

1. Create a new application user
2. Put the new user into the database role
3. Update your application to use the new user
4. Confirm that the old user is no longer used
5. Drop the old user

With that out of the way:

Is there a way to allow any stored procedures to have execute permissions if it belongs to a specific schema.

Yes. Say your schema is named app. You can do

grant execute on schema::[app] to yourAppRole;

How do we set it up so that developers can develop their stored procedures and get permission to what they need without getting blocked by permissions issues.

Looking at the documentation for ALTER PROCEDURE, it says:

Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.

That said, the ALTER permission on the procedure could be implicit. That is, if you do something like:

grant alter on schema::[app] to yourDevelopers;

That should allow them ALTER permissions on any stored procedure in the app schema. But that could have unintended consequences insofar as it would also grant them the ability to alter any other schema-owned objects (e.g. tables, views, etc). You'll have to make the call as to whether that's appropriate for your environment or not. You could get around that by having the procs and tables in separate schemas at which point that ALTER permission on the proc schema should have a fairly limited blast radius.