CODEWITHSUNDEEP
sql-serverstored-procedures
DeepakTheGeek
DeepakTheGeek

Reputation: 161

How to make a stored procedure accessible by a specific user

I am using SSMS 10.50 to access SQL Server 2008 R2 and I am very new to SQL coding. My login id is SA. I created a stored procedure and I want to make it executable by specific users. But I am failing to do so.

When I write these line,

create proc GetCustomerDetailsCompanyWise
    (@comp varchar(40))
as
begin
    grant execute on GetCustomerDetailsCompanyWise to [sgp\deepak.b]
    execute AS USER='sgp\deepak.b'; 

    select * 
    from DD_Customer 
    where Company = @comp;
end 

exec GetCustomerDetailsCompanyWise 'Google'

I get this error

Msg 916, Level 14, State 1, Procedure 
GetCustomerDetailsCompanyWise, Line 6
The server principal "sgp\deepak.b" is not able to access the 
database "TEMP" under the current security context.

Could you please explain what I may have done wrong and how can I fix it ?

Upvotes: 2

Views: 494

Answers (2)

gofr1
gofr1

Reputation: 15977

Add REVERT; before the end to get back to previous user state. Then remove grant statement from SP. Then create SP. Then run this:

grant connect on TEMP to [sgp\deepak.b];
grant select on TEMP to [sgp\deepak.b];
grant execute on GetCustomerDetailsCompanyWise to [sgp\deepak.b];

Upvotes: 0

shadow
shadow

Reputation: 1903

First create the procedure

create proc GetCustomerDetailsCompanyWise
    (@comp varchar(40))
as
begin
    select * 
    from DD_Customer 
    where Company = @comp;
end
go

Then grant permissions

grant execute on GetCustomerDetailsCompanyWise to [sgp\deepak.b];
go

Then try to run it.

Upvotes: 2

Related Questions