February 11, 2003 at 11:03 am
I almost sure the answer is "NO", but...
Can I GRANT a user EXECUTE on a Stored Proc but not GRANT the user SELECT on a table that SP access.
I want specific users to access ONLY through SPs and not run any Dynamic SQL they want to write badly.
I can't find a way to do this. Any help?
February 11, 2003 at 11:13 am
If the SP is not using Dynamic SQL then yes you can GRANT EXECUTE on SP to Person/Role. The only stipulation you may have issues with is that both the table and SP must have the same ownership chain, preferably dbo as the object owner.
Also, I suggest if this is multiple accounts to create a role instead of using the individual account since it is easier to add and remove accounts from the role than it is to remember who has and doesn't have execute rights on the SP. (You can handle thru the SP in EM but I prefer Roles).
February 11, 2003 at 11:56 am
Implementing data access control in this manner is considered a security best practice. Ownership chains were put into SQL Server for this purpose.
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
February 11, 2003 at 5:24 pm
Agree. Too bad they didnt add a db_procuser role similar to db_datawriter, would be a nice shortcut at times.
Andy
February 12, 2003 at 7:17 am
The problem was when I was testing this I had several EM and WinSQL windows open and created the tables and stored procs with different owners...dummy!
Thanks. This does exactly what I need. Are there any gotchas that I need to know about? For example: (provided you don't use EXEC in the SP) does anything cursors or temp tables (which I hate also) fail?
February 14, 2003 at 12:24 pm
There is one other gotcha, it didn't start until one of the Service Packs (for SQL 7 anyway). The SP will get you data only in the current database. If your SP reaches out to read data from another database, the user will have to have Select permissions in that table.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 14, 2003 at 1:43 pm
SP 3 for SQL 2K allows for cross-database ownership chaining which means the user doesn't have to have SELECT rights against the base tables. Of course, the security can of worms with using cross-database ownership chaining is now opened.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply