February 22, 2011 at 12:28 pm
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) roles
In 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 an
error message saying,
Msg 15151, Level 16, State 1, Line 1
Cannot 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 using
dynamic 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 give
db_owner. Is there any other way I can do this??? Let me know. Thanks
February 22, 2011 at 1:11 pm
try this
grant alter on yourproc to youruser
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 22, 2011 at 1:39 pm
I did that but no luck.
February 22, 2011 at 1:52 pm
Are these databases on the same server or are they being joined via linked server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 22, 2011 at 1:55 pm
they are on same server.
February 22, 2011 at 2:08 pm
and the user is just trying to alter the proc from within a connection to the database?
At what point are you getting an error? Will it allow you to view the proc definition? Or does it error only when trying to commit the changes?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 22, 2011 at 2:12 pm
yes the user tries to alter from database A from ssms. since it has read,write and ddladmin rights, once he alters something and hits the execute button, he gets this error message. He can view the defination. so it errors when he tries to commit the changes.
I strongly feel this is because of dynamic sql but i want to know is there any other way to achieve this if i dont want to grant db_owner???
and the user is just trying to alter the proc from within a connection to the database?
At what point are you getting an error? Will it allow you to view the proc definition? Or does it error only when trying to commit the changes?
February 22, 2011 at 2:34 pm
Have you explicitly denied the user deny access to the proc? DDL_admin should give you access to alter the proc.
Could you post the proc or something like it to verify that the issue can be recreated from somewhere else?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 22, 2011 at 2:46 pm
nothing has been denied on that user. I will generate a test code for you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply