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 |
|
kdfarber
Starting Member
24 Posts |
 Posted - 2003-05-06 : 12:26:12
|
| I have a situation where a stored procedure has been created and has been granted execute authority to a power role. Within that role are two NT groups. This procedure is doing an update to a table that is in a different database and give me a message stating that the they are lacking the permissions on the object (table). I have this same process within the database and it works fine without haveing to grant any specif update authority on the table itself. My question is do I have to grant specific authority on tables where the sp is outside of the database where the update is taking place? Also the security roles with NT groups are exactly the same in both databases. Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-06 : 12:42:03
|
| From SQL Server Books Online:"Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database. "The comment above doesn't mention object permissions, but I did my own test and could only run the stored procedure without errors when I granted the account permissions on the table in the other database.Tara |
 |
|
|
kdfarber
Starting Member
24 Posts |
Posted - 2003-05-06 : 12:53:19
|
| So you are saying that if your environment has a database that is for common tables and other databases for client information then the stored procedures should exist in the database base where the procedure is doing the updates, so you can grant the security to the procedure instead of the table ??Karen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-06 : 13:02:06
|
| I am not saying that at all. But that's one way of fixing it. Another way is to grant the permission on the table. And still another way (which is the one that I would recommend) is to have your stored procedure call another stored procedure that exists in the other database. This new stored procedure would do the updates. Then the user would need permissions on both stored procedures and not on any objects. This is what we have implemented here in a situation similar to yours.Example:This stored procedure would exist in DB1:CREATE PROC usp_DB1_ProcASEXEC DB2.dbo.usp_DB2_ProcRETURNThen DB2.dbo.usp_DB2_Proc would do the updates and would exist in DB2. Then the user or role would need permissions on both procedures.TaraEdited by - tduggan on 05/06/2003 13:03:48Edited by - tduggan on 05/06/2003 13:47:27 |
|
|
|
kdfarber
Starting Member
24 Posts |
Posted - 2003-05-06 : 13:07:05
|
| Thank you for the information. I really did not want to do grants on tables to specific groups. I will see if these solutions can fit. Thanks again ! |
|
|
|
|
|
|
Security Needed on DB object to run Stored Proc