Grant only sp access to table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Grant only sp access to table

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2007-08-31 : 09:40:49

I want to create a stored procedure that will insert
to a table. Then I want to grant execute on that procedure to
a user - While denying him the authority any access
to that table outside of the procedure.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-31 : 10:13:34

deny insert privileges on the table
grant execute privileges on the sproc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 15:51:05

don't give the user any other privilege aside from execute on the stored procedure

--------------------
keeping it simple...

dbthj
Posting Yak Master

143 Posts

Posted - 2007-09-06 : 17:19:27

Uh... You sure ?
As sa I created a login, made him a user of a database.
Then I created a procedure

create procedure hj_showauthors2 as
print 'I am executing this-here proc'
select * from tablename
go

and grant execute on procname to userguy

Then I logged on as userguy and fired up the SP.

The print command went swimmingly. The select netted me
"SELECT permission denied on object 'tablename', database 'dbname', owner 'tableowner' ."

Looks to me like I need an extra step or two to get this done.

JC7
Starting Member

3 Posts

Posted - 2007-09-06 : 18:05:58

I am not quite sure what you want to do. If all it needs to do for the sp is a select, then create a view instead of sp.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 18:16:16

dbthj,

When you got the error, were you running EXEC hj_showauthors2 or the SELECT statement?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-06 : 22:56:25

Do sp and table have same owner? If not, you'll get error since ownership chain is broken.

dbthj
Posting Yak Master

143 Posts

Posted - 2007-09-07 : 09:42:27

rmiao nailed it. "ownership chain" was the key. Thanks!

The table and the SP need to have the same owner. Just because sa
granted access to the SP doesn't mean the authority will be given to the table.

Now the select inside the SP succeeds for the userguy
But a select outside the SP fails.

If I wrote what I really need to do, the post would be too long and nobody would read it.
The problem was outlined in the original post: give access ONLY thru the SP.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 10:03:33

have you tried the Execute AS option?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dbthj
Posting Yak Master

143 Posts

Posted - 2007-09-11 : 10:12:12

Execute As works fine, but I'm pretty sure
that's a 2005 thing. I'm working with SQL 2000 here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources