SQL Server - Permisssions on Stored Procedure

Asked By Naresh Kumar on 17-Jan-12 04:41 AM
Hi All

I have created one Stored Procedure and need to give Execute Permissions for only one user.

How can I do this.. If any one knows please help me..

Riley K replied to Naresh Kumar on 17-Jan-12 04:45 AM

To grant permissions on a stored procedure

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the database in which the stored procedure belongs, and then expandProgrammability.

  3. Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.

  4. From Stored Procedure Properties, select the Permissions page.

  5. To grant permissions to a user, database role, or application role, click Add.

  6. In Select Users or Roles, click Object Types to add or clear the users and roles you want.

  7. In the Explicit Permissions grid, select the permissions to grant to the specified user or role. 

Regards
smr replied to Naresh Kumar on 17-Jan-12 04:45 AM
hi

Execute permissions on the stored procedure is sufficient.
  
CREATE TABLE dbo.Temp(n int)
  
GO
DENY INSERT ON dbo.Temp TO <your role>
GO
CREATE PROCEDURE dbo.SPTemp(@Int int)
AS
  
INSERT dbo.Temp
SELECT  @Int 
  
GO
  
GRANT EXEC ON dbo.SPTemp TO <your role>
  
GO
Then the (non-db_owner) user will have the following rights:
  
EXEC dbo.SPTemp 10
GO
  
INSERT dbo.Temp --INSERT permission was denied on the object 'Temp'
SELECT  10


follow
http://www.vikramlakhotia.com/Granting_Execute_permission_on_All_Stored_Procedure_for_a_new_SQL_Server_User.aspx
http://www.sqldbatips.com/showarticle.asp?ID=8
D Company replied to Naresh Kumar on 17-Jan-12 04:46 AM
Hello Naresh,


try this

Stored Procedure Execute Permissions

For example:

Create proc sp_Sample
as
select * from users
where name= 'abc'
go

grant execute on sp_Sampleto [my_user]
go


try this and let us know if it is not working

Regards
D
Suchit shah replied to Naresh Kumar on 17-Jan-12 04:52 AM
To grant permissions on a procedure in Query Editor

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs.

  3. On the File menu, click New Query.

  4. Copy and paste the following example to grant EXECUTE permission on stored procedure HumanResources.uspUpdateEmployeeHireInfo to an application role named Recruiting11.

USE AdventureWorks2008R2;
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
    TO Recruiting11;
GO