Rights to execute stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Rights to execute stored procedure

Rights to execute stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Wednesday, March 24, 2010 7:54 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi,

I have this stored procedure:

create procedure UpdateAut (@TIPO as varchar(10),@OBS as varchar(20))
as
begin

declare @erro as integer
begin transaction

update consulta_contribuintes set tipo =@tipo,obs=@obs,dtaupd=getdate()
set @erro =@@error

if @erro =0
begin
commit transaction
insert into procedimentos_executados values (21)

end
else
rollback transaction

end

I want that a user (SQL User) can execute the stored procedure, so i will give him the right to execute the procedure.

Inside the procedure i insert and update values in two tables:

consulta_contribuintes
procedimentos_executados

This users needs rights to in this two tables? or only needs the right to execute the procedure?

Thank you

Post #888975

Lynn Pettis

Posted Wednesday, March 24, 2010 9:23 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

Should only need execute rights on the stored procedure. There may be exceptions to that, but usuallly only if the tables in question on in another database.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #889048

river1

Posted Wednesday, March 24, 2010 9:26 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

hum???

How is that?

If a user as rights to execute the stored procedure but do not have permissions on the tables inside the stored procedure how can the procedure run if it runs in the context of the user?

Post #889050

Lynn Pettis

Posted Wednesday, March 24, 2010 9:32 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

It is one aspect of security. If you only grant users access to the views and stored procedures, they don't require access to the underlying tables. It is a means of layering security.

Also, by using views and stored procedures to provide access to the underlying tables, you hide the actual schema of your database. If future changes are required to add functionality, as long as the output from the views and stored procedures remains the same, the users never have to know that the schema changed.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #889058

river1

Posted Wednesday, March 24, 2010 9:35 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

ok, thank you very much.

So what is the "EXECUTE AS" for?

Post #889060

Perry Whittle

Posted Wednesday, March 24, 2010 12:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:47 AM
Points: 5,201, Visits: 11,151

river1 (3/24/2010)

ok, thank you very much.

So what is the "EXECUTE AS" for?

to execute as another database user that is not linked to a server level login

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #889231

« Prev Topic | Next Topic »

Permissions