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 |
|
D.Carter
Starting Member
3 Posts |
 Posted - 2010-03-08 : 16:03:17
|
| I have been trying to figure this out for a while now and haven't had any luck so hopefully someone here can point me in the correct direction. I have 2 questions that are somewhat related.#1 I have 2 Databases A and B. In Database A I have a table, TableA, and in Database B I have TableB.I have a stored procedure in Database A that looks like this.Create Procedure ProcedureAasInsert into dbo.TableA (Column1, Column2) values (Value1, Value2)Insert into B.dbo.TableB (Column1, Column2) values (Value1, Value2)This is a web application so I give my Web Application's user Execute permission for this stored procedure.When I try to run the stored procedure I get the error message "The INSERT permission was denied on the object 'ProcedureA', database 'B', schema 'dbo'.I don't want to give the user permission to read/write in my databases on the ability to execute stored procedures. How can I make it so that all the user needs is permission to execute this stored procedure?#2 Ok lets say I get that working. Can I wrap a transaction around these 2 statements, or do I have to do something else since they are in different databases?Thanks in advance for any help I get. |
|
|
CSears
Starting Member
39 Posts |
Posted - 2010-03-08 : 16:30:28
|
| Try using:Insert into B..TableB (Column1, Column2) values (Value1, Value2)Leaving the schema blank means that it will use the schema that the users login has defaulted for Database B.If that doesn't solve the problem, the user may not be granted access to TableB on Database B, and this may be the underlying issue. |
 |
|
|
D.Carter
Starting Member
3 Posts |
Posted - 2010-03-08 : 16:37:43
|
quote:
Originally posted by CSears
Try using:Insert into B..TableB (Column1, Column2) values (Value1, Value2)Leaving the schema blank means that it will use the schema that the users login has defaulted for Database B.If that doesn't solve the problem, the user may not be granted access to TableB on Database B, and this may be the underlying issue.
They don't have access to TableB on Database B and I don't want to have to give them permission to. I just want to give them access to run the stored procedure on Database A. |
|
|
|
D.Carter
Starting Member
3 Posts |
Posted - 2010-03-09 : 12:32:10
|
| Finally got this to work using Signed Stored procedures. There is an in depth article about how to do this here ([url]http://www.sommarskog.se/grantperm.html[/url]).Hope this helps someone in the future. |
|
|
|
|
|
|
|
|
Run Stored Procedure Across Shared DB