getting "select permission denied" error when using sp_executesql in proc (Sql Server 2008)

Question

I'm using Sql Server 2008 and have a proc that uses sp_executesql inside. I keep getting the following error when executing the proc through ASP.NET:

The SELECT permission was denied on the object 'MyTable', database 'MyDatabase', schema 'dbo'.

I've done lots of research on this and most people point to the fact that I need to grant select permission on the underlying table that my dynamic query reads from. Well, I've done this and it still doesn't seem to work:

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions
where major_id = object_id('User') and 
class = 1

Results:
MyTable public  dbo SELECT  GRANT
MyTable guest   dbo SELECT  GRANT
MyTable myuser  dbo SELECT  GRANT
MyTable NT AUTHORITY\NETWORK SERVICE    dbo SELECT  GRANT

As you can see, I've gone overkill on trying to grant select on the underlying table. I've even re-written my proc down so that it only references MyTable in the dynamic sql. I've even granted all of the above users "execute" permission on the proc... No luck.

The "myuser" above is the username in my connectionstring in the web.config.

Am I missing something here?

Thanks! Dave

Forthwith

Does the stored procedure have a WITH EXECUTE AS defined? – Crossjack

Yes... tried it with "MyUser", caller, self, owner. Are there any others I'm missing? Funny thing is that when I use MyUser, then it throws the same error in management studio query window (whereas without this it doesn't) – Forthwith

Answer 1

I figured it out. Posting this for anyone else who runs into this.

1) First, I noticed my dbo user didn't have a login name associated with it. You can see this by right clicking on the dbo user in the Security/Users folder on the dbo user and clicking Properties. In the greyed out area you should a User name (dbo) and Login name (sa). I think this typically happens when you restore your database or something (not sure). Anyway, if you don't see login name, just run the script in the database with the issue:

sp_changedbowner 'sa'

This just makes dbo the owner of the database (or something like that). The reason I found this out was because I tried saving some of the permissions such as "db_datareader", "db_datawriter" through this gui interface but threw an error saying login name is required. I think dbo should have db_owner checked (mine does) and I think this trumps all the others anyway.

2) In order to get my proc to work, I had to then add "with execute as owner" at the end of the proc. I tried this before, but think it didn't work because of my first issue #1. Procs typically run under the owner of the proc, but not dynamic sql inside procs. These run under the context of the user executing the proc. To me this is wierd because I would think I would need to add this in the dynamic sql (which I tried), but it needs to be in the proc itself (go figure?)

3) Probably wouldn't hurt to run this script also:

grant select on 'MyTable' to 'MyUser'

for every table used in the dynamic sql.

Hope this helps...

here's some references to this same issue:

http://www.jimmcleod.net/blog/index.php/2007/05/15/sp_executesql-breaks-chain-of-ownership-in-sql-server-2000/

http://kbalertz.com/301299/Security-Context-Dynamic-Statements-Inside-Stored-Procedure.aspx

http://www.mssqltips.com/tipprint.asp?tip=1822

Forthwith

Answer 2

What has helped me was:

In SQL Management Studio, right-click the stored procedure and choose Properties, and on the Permissions page grant permisssion to the relevant user. The relevant user depends on your SQL connection string: if you are using Windows authentication then it is the Windows user (which may be ASPNET if so configured); otherwise it is the SQL user you have specified in your connection string. The permission you need to grant is Execute permission.

Sentinel