CODEWITHSUNDEEP
sql-serverstored-procedures
Jay
Jay

Reputation: 6027

stored procedure permissions and execute

I have a SQL user that I gave explicit Execute permission for a specific stored procedure. This stored procedure contains a truncate statement. The user is unable to execute that procedure and receives the error:

Cannot find the object TableName because it does not exist or you do not have permissions.

If I alter the stored procedure to use Delete instead of truncate the user can execute the procedure.

What do I need to do to allow the user to execute this stored procedure, without giving the user more access than necessary?

Upvotes: 2

Views: 18965

Answers (3)

Richard Ouimet
Richard Ouimet

Reputation: 476

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 AS capability 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 AS can 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 EXECUTE rights 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 CREATE TABLE. EXECUTE AS is 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.

With the EXECUTE AS clause the stored procedure is run under the context of the object owner. This code successfully creates dbo.MyTable and rows are inserted successfully. In this example, the user NoPrivUser has 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.

Upvotes: 0

S.K
S.K

Reputation: 165

You can try this:

create procedure SpName
with execute as owner
as
truncate table TableName
go

Then assign permission to user

grant execute on TruncTable to User

Upvotes: 2

dash
dash

Reputation: 91540

From MSDN:

http://msdn.microsoft.com/en-us/library/ms177570.aspx

"The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create Custom Permission Sets."

Upvotes: 2

Related Questions