question

programmher avatar image
programmher asked

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.
stored-proceduresstored procedures
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
Microsoft have a great permissions heirarchy diagram at [ https://msdn.microsoft.com/en-us/library/ms191465.aspx][1] I'll attempt to embed it here: ![alt text][2] 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][3]: 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. [1]: https://msdn.microsoft.com/en-us/library/ms191465.aspx [2]: https://i-msdn.sec.s-msft.com/dynimg/IC353007.jpeg [3]: https://msdn.microsoft.com/en-us/library/ms188367.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Excellent answer!
0 Likes 0 ·
Thank you so much!!! Previously, procedures I copied inherited their permissions...it was likely how something was set up vs. the current environment I am now working in. Thanks again!
0 Likes 0 ·
programmher avatar image
programmher answered
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?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

if permissions are assigned at the schema-level, I do not believe that the objects within that schema would show the permissions as explicitly assigned. The permission would be implicit through the parent container/child object relationship. Not sure if that addresses the two procedures to which you refer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.