June 22, 2004 at 4:54 am
If i allow a user to have db_owner rights then my SPROCs all work. If i remove the db_owner rights and assign exec permissions on the SPROCs individually then some of them stop working. The one's which won't work contain EXEC statements (which i'm using to build dynamic SELECT queries).
I don't want the user to have full rights to the database - they only need to be able to run the SPROCs. How do I go about this?
Thanks
June 22, 2004 at 7:31 am
When running dinamic queries inside a sp, SQL check that the user executing the stored procedure have enough rights to execute the dinamic query.
So if your sp builds a SELECT wich is dinamic depending of a variable, then the user executing the sp, must have right to execute the select on the specified table.
The way SQL handle this is correct, because a dinamic query can be replace by a drop table or something more armfull in a form, for example.
June 22, 2004 at 7:48 am
But that would mean giving the user the right to run any SELECT statement they wanted. I only want them to be able to run the sp. Is it possible to deny them SELECT permission but make an exception ONLY in the sp?
June 23, 2004 at 2:38 am
If the SP runs arbitrary dynamic SQL, then the user must have the ability to run arbitrary SQL. However, I suspect there is going to be some pattern to the possible dynamic SQL statements run by the SP.
I would advise taking a look at the dynamic SQL used in the SPs (and maybe sharing some examples with us). If, say, you are building simple dynamic SELECT queries, then one answer might be to create a view on the base table; if all possible queries the SP could run are answered by this view, then you can give permission to the view while keeping the rest of the table protected.
If you need more help, the table structure and example data and queries run by the SP would be useful.
June 23, 2004 at 5:04 am
But that would mean giving the user the right to run any SELECT statement they wanted. I only want them to be able to run the sp. Is it possible to deny them SELECT permission but make an exception ONLY in the sp?
That is one of the drawbacks with dynamic sql. Since the 'dynamic part' is actually not part of the query plan of the sp itself, the permissions of the current user apply, not that of the owner of the sp. However, you might be able to GRANT and DENY permissions inside your SP as you like, but IMHO that would be a kludge.
For the records:
Read this
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply