Why Do Stored Procedures I Copy Lose Their Permissions?
Sometimes I have to modify existing stored procedures (add fields, update calculations, etc.) To do this, I copy the existing stored procedure and rename it. For some reason, I have to run a separate script to Grant execute permission to the copied stored procedure. The original stored procedure has the permissions necessary to execute but my copied stored procedures do not. Any ideas why? This is happening on SQL Server 2008 R2.
Microsoft have a great permissions heirarchy diagram at [
https://msdn.microsoft.com/en-us/library/ms191465.aspx] I'll attempt to embed it here: ![alt text] The most granular level of security within SQL Server is the object or column level. Each stored procedure can have permissions explicitly to it individually and it can also inherit permissions from further up the heirarchy. You can view the mapping between a stored procedure and any explicitly assigned permissions using sys.database_permissions - [
https://msdn.microsoft.com/en-us/library/ms188367.aspx]: select object_name(major_id) as procedure_name, * from sys.database_permissions as dp where major_id = object_id('YOUR_PROCEDURE_NAME') That should return something like this: procedure_name class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name ------------------- ----- ------------------ ----------- ----------- -------------------- -------------------- ---- --------------- YOUR_PROCEDURE_NAME 1 OBJECT_OR_COLUMN 1069623 0 0 1 EX EXECUTE This lists your single procedure along with any explicit permissions that have been granted to it. The reason you have to grant permissions to the "copy" is that, when you create a "copy" of the stored procedure, you are actually creating an entirely new object that has no explicit permissions assigned. The new procedure is a blank slate, as it were. You can verify this by running the above query with the old procedure name and the new procedure name - they are identified by different object_id values and each have their own set of permissions. Referring back to the permissions heirarchy diagram, the next level up in granularity (less granular) is the schema. If you want all of the procedures you create to share a common set of permissions, you can place them into a common schema then grant the execute permission to that schema. With this approach, every time you place a new procedure into the schema, it will inherit the appropriate permissions without the need to run the additional script. :
One last thing I noticed and perhaps your above explanation addresses this - I have encountered another two stored procedures that do not have ANY permissions assigned to them; yet, the pages are able to execute/run them. I have copied them and, of course, I get the error that the EXECUTE permissions has been denied to the copies - because they do not have any permissions assigned to them...but the originals do not have any permissions assigned to them. If permissions were assigned to the entire schema and everything in the schema wouldn't I see the permissions in the originals?