December 5, 2002 at 1:53 pm
Hello!
I'm running an EXEC command in one of my stored procedures. But I only want it to be able to run SELECT commands. Is this possible?
thAnks!
/Tomi
December 5, 2002 at 2:04 pm
Just remove it!
Seriously: By default SQL Server only checks permissions at the Stored proc level. If you can execute the proc, SQL Server do the higher overhead of checking each object and permissions to those objects as it executes. If you want, you can turn this on but you will suffer system-wide performance drains by doing so.
December 5, 2002 at 2:35 pm
A small clarification:
If the stored procedure references an object (table, view, user-defined function, another stored procedure) and both the calling stored procedure and the object have the same owner, permissions are only checked on the calling stored procedure. If the object has a different owner than the calling stored procedure, then permissions are checked on both objects.
For instance:
Case 1:
Owner1.MyStoredProcedure
Owner1.MyTable
Permissions are checked on Owner1.MyStoredProcedure only
Case 2:
Owner1.MyStoredProcedure
Owner2.MyTable
Since owners are different, permissions are checked on both the stored procedure and the table.
For more information, read up on ownership chains in Books Online. Once you've got that topic nailed down, it'll really help in your security implementations.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 5, 2002 at 2:43 pm
Thanks guys!
Ooh, it was that difficult. I'll just find another way then.
/Tomi
December 5, 2002 at 2:48 pm
Oh wait, you're running an EXEC command inside the stored procedure? If so, then the EXEC command actually executes the command in another batch... new batch, automatic permissions check.
If that's the case, ensure the user(s) only have SELECT rights against the table. Since the table permissions will be checked, if you've not granted INSERT, UPDATE, or DELETE, your users won't be able to perform those operations.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 6, 2002 at 10:26 am
Hello!
Ok, I'll try that...
Thanks!
/Tomi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply