stored procedure and permissions

[zerkat]

We have a database with two roles that have execute permissions on our stored procedures. From what our DBA tells us this should be enough. The roles should not need select permissions on the table as long as they have execute on the stored procedures. But when we try to run any of these stored procedures through our .net web application we receive an error stating that we do not have select permission on the tables.

Can any one here please tell me why this is happening and what to do to correct it? Thanks.

 

[gmmastros]

From what our DBA tells us this should be enough.

Your DBA is mostly right. If you grant someone permission to execute a stored procedure, they should be able to run it. They do NOT need any permission to the tables that the stored procedure uses.

But...

If your query uses dynamic sql, you will need permissions to the underlying tables.

It's also possible that you are not connecting as the user you expect to be connecting as. Since you say, ".net web application", I would encourage you to double and triple check that you are connecting as the user you expect to be connecting with. I would suggest that you temporarily modify your app so that you run this query, and then see what output you get.

select suser_sname() As LoginUser



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Also, if your stored procedure references a table in a different database, you will have issues accessing the tables in the other database if the user does not have permissions to those tables.

 

[zerkat]

Thanks - did some more searching online. It is all the dynamic SQL we are using in our stored procedures. Almost all of our sp contain some dynamic SQL. I just read a good description of it.

I have included the link in case it helps someone else some day.

Thanks again.

 

[RiverGuy]

Almost all of our sp contain some dynamic SQL

That's unfortunate it. Not trying to criticize, but dynamic SQL is rarely actually needed, and generally not a good idea. In the past year, I think I've written 2 stored procedures which use dynamic SQL -- one of them changes the definition of a view, and one of them builds an MDX statement to a linked SSAS OLAP server. These are obviously out of scope for general data retrieval/insert/update, and in most cases, you can get by with regular T-SQL stored procedures for data retrieval/insert/update.

If you can post an example and a description of one of your stored procedures, we may be able to help you to code it without dynamic SQL.

 

[zerkat]

Hi RiverGuy,

Thanks for the offer. I spoke with our DBA and we are working through the issue. I originally thought that all of our stored procedures contained dynamic sql but only a few actually do. What I was thinking was dynamic SQL and what it really was are two different things. I am still getting my feet wet in SQL so am learning as I go.

Thanks again.