How do you grant execute permission for a single stored procedure?
Normally when I create a stored procedure I use the following as a template of sort
Create procedure <procedurename> <@param1 <type>, @param2 <type>, etc..> as begin <procedure> end
Is there a way to include granting execute permission on only that stored procedure while I'm at it?
For instance like ...
Grant execute [User_Execute]
... but only for this stored procedure?
I've seen some other similar questions but they seem to all refer to ALL of the stored procedures and not just one, nor have I seen one where you can specify permissions inside of the
create procedurescript. Even answers about how I can set permissions without the GUI for specific stored procedures would be welcome.
Edit The top answer certainly pointed me in the right direction, this is is essentially what i was looking for, I didn't think about batching the commands which is what I ended up doing, batching the command along with my stored procedure. Anyway, I think it's pretty slick.
Create procedure <procedurename> <@param1 <type>, @param2 <type>, etc..> as begin <procedure> end GO GRANT EXECUTE ON <procedurename> to <username> GO
truncate table Setting permission on objects like stored procedures can be accomplished with:
GRANT EXECUTE ON <schema>.<object> to <user>;
However, you may also want to grant security rights at both the login and user level. You will want to determine and grant ONLY the necessary rights for the objects that require access (such as execution). Consider use of the
EXECUTE AScapability which enables impersonation of another user to validate permissions that are required to execute the code WITHOUT having to grant all of the necessary rights to all of the underlying objects (e.g. tables).
EXECUTE AScan be added to stored procedures, functions, triggers, etc.
Add to the code as follows right within the Stored Procedure:
CREATE PROCEDURE dbo.MyProcedure WITH EXECUTE AS OWNER
In this case you are impersonating the owner of the module being called. You can also impersonate SELF, OR the user creating or altering the module OR... imperonate CALLER , which will enable to module to take on the permissionsof the current user, OR... impersonate OWNER, which will take on the permission of the owner of the procedure being called OR... impersonate 'user_name', which will impersonate a specific user OR... impersonate 'login_name' with will impersonate a specific login.
MOST of the time, you will only need to grant
EXECUTErights to stored procs and then rights are granted to all objects referenced within the stored proc.
In this way, you DO NO need to give implicit rights (example: to update data or call additional procs). Ownership chaining handles this for you. This is especially helpful for dynamic sql or if you need to create elevated security tasks such as
EXECUTE ASis a handy tool to consider for these.
This example may help clarify all of this:
Create a user called NoPrivUser with public access to a database (e.g. dbadb):
USE [master]; GO CREATE LOGIN [NoPrivUser] WITH PASSWORD=N'ABC5%', DEFAULT_DATABASE=[dbadb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON; GO USE [DBAdb]; GO CREATE USER [NoPrivUser] FOR LOGIN [NoPrivUser]; GO
NOTE: CREATOR OR OWNER OF THIS PROCEDURE WILL REQUIRE CREATE TABLE RIGHTS within the target database.
use DBAdb go CREATE PROCEDURE dbo.MyProcedure WITH EXECUTE AS OWNER truncate table MyTable GO GRANT EXEC ON dbo.MyProcedure TO NoPrivUser; GO -- Now log into your database server as NoPrivUser and run the following.
EXECUTE ASclause the stored procedure is run under the context of the object owner. This code successfully creates
dbo.MyTableand rows are inserted successfully. In this example, the user
NoPrivUserhas absolutey no granted rights to modify the table, or read or modify any of the data in this table.
It only takes on the rights needed to complete this specific task coded WITHIN the context of this procedure.
This method of creating stored procedures that can perform tasks that require elevated security rights without permanently assigning those rights come be very useful.
select database login-->Go to Securable and click on Search button as in the preceding image. On clicking the Search button, you’ll find he following window to add the type of object.
Click on Object Types button and you’ll get “Select Object Types” window with various objects. Now if you see, stored procedure is listed in the object types area. Now we’ll select our specific stored procedure on which we want to provide permission.