SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Exec Grant on Login's SP: cant touch table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 01/29/2013 :  15:49:06  Show Profile  Reply with Quote
Hi,

--SQL Server 2008 R2--
I have created a Login in a DB, that login has db_datareader and db_datwriter and it has Execute on all the SP's in the DB.

When I execute that SP, I get a rights error on the table referenced by the SP. But when I add the table as a securable for that user with the appropriate Grants, then it works.

I was under the impression that is you set Exec rights on the SP's then you don't have to set any rights on the tables that SP touches, is this not correct? And therefore i will have to set grants for ALL the tables too?

Thanks!


--PhB

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 01/29/2013 :  15:55:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
Is it using dynamic SQL?

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

Subscribe to my blog
Go to Top of Page

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 01/29/2013 :  15:56:55  Show Profile  Reply with Quote
No it isn't.

Thanks for the reply.

--PhB
Go to Top of Page

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 01/29/2013 :  16:30:43  Show Profile  Reply with Quote
The SP performs a truncate on the table. I've read that you need to assign extra rights beyond exec on the SP is truncate is being called on a table.

--PhB
Go to Top of Page

phrankbooth
Posting Yak Master

USA
157 Posts

Posted - 01/29/2013 :  16:58:46  Show Profile  Reply with Quote
Yes, in fact you need a minimum of ALTER on the table being truncated otherwise you'll get an error. See Remarks here:
http://msdn.microsoft.com/en-us/library/ms177570.aspx

--PhB
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000