Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cottage125
Starting Member
32 Posts |
 Posted - 2011-02-22 : 14:29:19
|
| I have 2 databases.Database A and Database B.In Database A, I have testuser with read,write,ddladmin and db_exec(custom role to execute SPs) rolesIn Database B, I have testuser with read,write,ddladmin and db_exec(custom role to execute SPs) roles.I have one SP in Database A which uses dynamic sql to select data from DB A and DB B. (uses inner join).With SA account there is no issue. but when testuser wants to alter this SP in Database A, it gives anerror message saying,Msg 15151, Level 16, State 1, Line 1Cannot find the user 'dbo', because it does not exist or you do not have permission.---------------------------------------------------------------------------------------------------As I said above, testuser has read permissions in DB B.When I execyte the plain select statement, there is no issue but when I do the same thing from SP and usingdynamic sql then this error message comes.I tried to give db_owner role to testuser in DB A and then there was no issue at all. But I dont want to givedb_owner. Is there any other way I can do this??? Let me know. Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-22 : 14:41:43
|
| You can grant permissions to individual tables for that user to do what's needed (insert, select, etc.), but the better approach is to rewrite it so it does not use dynamic SQL. If you're trying to enforce proper security then dynamic SQL will always defeat that goal. |
 |
|
|
cottage125
Starting Member
32 Posts |
Posted - 2011-02-22 : 14:58:09
|
| Thanks for the reply but I dont need to grant the permissions on individual tables as I have granted read permission already and its working fine without dynamic sql.(in my case i only need to select the data) but the same code when goes inside dynamic sql then prob comes. But still i have tried explicitly to grant select on database B. table XYZ and still not helpful. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
synamic sql permissions