|
|
You're in the
sql server programming
group:SP and Triggers: object permissions
sql server programming:
to create a trigger (for update and insert) which selects some rows from a table in *another* database. The problem is that when a domain user in DB#1 tries updates a row in this table and the trigger fires, SQL Server 2000 raises an error because the domain user does not exsit over in DB#2. I am hoping to avoid recreating in DB#2 all of DB#1's domain users. Is there a way to create a trigger so that it executes with the permissions of the table owner rather than the permissions of the current user? Is there a way to accomplish this inter-database situation using a stored procedure? Is there a way to cause a SP to execute with a specific user's permissions, rather than those of the current user? Thanks
TR
Can you show us your trigger's code? UPDATE Databasename.dbo.Table SET col='something' WHERE blablabal Are you still getting the same error? [quoted text, click to view] "TR" <TR@org.org> wrote in message news:OXg%233tH%23FHA.572@TK2MSFTNGP15.phx.gbl... > This question involves two distinct databases in SQL Server 2000. I have > to create a trigger (for update and insert) which selects some rows from a > table in *another* database. > > The problem is that when a domain user in DB#1 tries updates a row in this > table and the trigger fires, SQL Server 2000 raises an error because the > domain user does not exsit over in DB#2. I am hoping to avoid recreating > in DB#2 all of DB#1's domain users. > > Is there a way to create a trigger so that it executes with the > permissions of the table owner rather than the permissions of the current > user? > > Is there a way to accomplish this inter-database situation using a stored > procedure? Is there a way to cause a SP to execute with a specific user's > permissions, rather than those of the current user? > > > Thanks > TR
Thanks for the reply. I'm at home at the moment, and cannot give you the
verbatim T-SQL or the verbatim error, but basically the trigger fails on the following attempt to access table TEST which happens to be in another database: .. .. select col1, col2 from THISDATABASE.DBO.CUSTOMERS T1 CROSS JOIN MYOTHERDATABASE.DBO.TEST T2 .. .. The error says, more or less, that {ourdomain/domainuser} is unrecognized in MYOTHERDATABASE. The trigger works fine when I happen to be the current user because I have domain administrator status. But the trigger fails when, for example, janedoe in accounting updates the table and thereby invokes the trigger. ourdomain/janedoe has *not* been added as a user in MYOTHERDATABASE but janedoe does exist as a user in THISDATABASE. I'd rather not have to recreate a whole lot of users in MYOTHERDATABASE, because this one trigger is the one and only occasion they'll ever need access any object in MYOTHERDATABASE. So my question is, is there a way to give a domain user select permission on a table if the domain user doesn't has not been granted access to the database where that table resides? I wanted to know if in SQL2000 it's possible to execute an update trigger with the permissions of the table's owner rather than the permissions of the current user. TR [quoted text, click to view] Uri Dimant wrote:
> TR > Can you show us your trigger's code? > > UPDATE Databasename.dbo.Table SET col='something' WHERE blablabal > > Are you still getting the same error? > > > > > "TR" <TR@org.org> wrote in message > news:OXg%233tH%23FHA.572@TK2MSFTNGP15.phx.gbl... > >>This question involves two distinct databases in SQL Server 2000. I have >>to create a trigger (for update and insert) which selects some rows from a >>table in *another* database. >> >>The problem is that when a domain user in DB#1 tries updates a row in this >>table and the trigger fires, SQL Server 2000 raises an error because the >>domain user does not exsit over in DB#2. I am hoping to avoid recreating >>in DB#2 all of DB#1's domain users. >> >>Is there a way to create a trigger so that it executes with the >>permissions of the table owner rather than the permissions of the current >>user? >> >>Is there a way to accomplish this inter-database situation using a stored >>procedure? Is there a way to cause a SP to execute with a specific user's >>permissions, rather than those of the current user? >> >> >>Thanks >>TR > >
[quoted text, click to view]
> So my question is, is there a way to give a domain user select permission > on a table if the domain user doesn't has not been granted access to the > database where that table resides? No. A user must have a database security context in order to access data in that database. This can be by individual account, group membership or the guest user. [quoted text, click to view] > I wanted to know if in SQL2000 it's possible to execute an update trigger > with the permissions of the table's owner rather than the permissions of > the current user. No, but you don't need to do that. As long as the ownership chain is unbroken (all objects involved have the same owner), permissions on indirectly referenced objects are not checked. This means that a user with UPDATE permissions on THISDATABASE.dbo.CUSTOMERS can execute the update trigger and access the MYOTHERDATABASE.dbo.TEST table even though no permissions on MYOTHERDATABASE.dbo.TEST have been granted to the invoking user. There are special considerations for cross-database ownership chains. First, you need to enable the 'db chaining' in the databases. This option was introduced in SQL 2000 SP3: EXEC sp_dboption 'THISDATABASE', 'db chaining', true EXEC sp_dboption 'MYOTHERDATABASE', 'db chaining', true Second, the databases need to have the same owner so that the 'dbo' user ownership chain is unbroken. You can use sp_changedbowner, if needed: EXEC THISDATABASE..sp_changedbowner 'CommonDbOwner' EXEC MYOTHERDATABASE..sp_changedbowner 'CommonDbOwner' Finally, all users with update permissions need to have a security context in MYOTHERDATABASE, although no object permissions need to be granted. If you don't want to add individual users, you can enable the 'guest' database user (EXEC sp_adduser 'guest') so that all SQL Server logins can access the database. Assuming you grant no permissions to guest, permissions will be limited to those of the public role. A security consideration with cross-database chaining is that you should enable the option only when you fully trust users that have permissions to create dbo-owned objects in those databases. This is especially true of sa-owned databases, where only sysadmin role members should have those permissions. See the Books Online for more information. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "TR" <TR@org.org> wrote in message
news:eMAo6DP%23FHA.600@tk2msftngp13.phx.gbl... > Thanks for the reply. I'm at home at the moment, and cannot give you the > verbatim T-SQL or the verbatim error, but basically the trigger fails on > the following attempt to access table TEST which happens to be in another > database: > . > . > select col1, col2 > from THISDATABASE.DBO.CUSTOMERS T1 > CROSS JOIN MYOTHERDATABASE.DBO.TEST T2 > . > . > The error says, more or less, that {ourdomain/domainuser} is unrecognized > in MYOTHERDATABASE. > > The trigger works fine when I happen to be the current user because I have > domain administrator status. But the trigger fails when, for example, > janedoe in accounting updates the table and thereby invokes the trigger. > ourdomain/janedoe has *not* been added as a user in MYOTHERDATABASE but > janedoe does exist as a user in THISDATABASE. > > I'd rather not have to recreate a whole lot of users in MYOTHERDATABASE, > because this one trigger is the one and only occasion they'll ever need > access any object in MYOTHERDATABASE. So my question is, is there a way > to give a domain user select permission on a table if the domain user > doesn't has not been granted access to the database where that table > resides? I wanted to know if in SQL2000 it's possible to execute an > update trigger with the permissions of the table's owner rather than the > permissions of the current user. > > TR > > > > Uri Dimant wrote: >> TR >> Can you show us your trigger's code? >> >> UPDATE Databasename.dbo.Table SET col='something' WHERE blablabal >> >> Are you still getting the same error? >> >> >> >> >> "TR" <TR@org.org> wrote in message >> news:OXg%233tH%23FHA.572@TK2MSFTNGP15.phx.gbl... >> >>>This question involves two distinct databases in SQL Server 2000. I have >>>to create a trigger (for update and insert) which selects some rows from >>>a table in *another* database. >>> >>>The problem is that when a domain user in DB#1 tries updates a row in >>>this table and the trigger fires, SQL Server 2000 raises an error because >>>the domain user does not exsit over in DB#2. I am hoping to avoid >>>recreating in DB#2 all of DB#1's domain users. >>> >>>Is there a way to create a trigger so that it executes with the >>>permissions of the table owner rather than the permissions of the current >>>user? >>> >>>Is there a way to accomplish this inter-database situation using a stored >>>procedure? Is there a way to cause a SP to execute with a specific user's >>>permissions, rather than those of the current user? >>> >>> >>>Thanks >>>TR >> >>
Dan,
This is very helpful. Thanks for the references to db chaining. TR [quoted text, click to view] Dan Guzman wrote:
>>So my question is, is there a way to give a domain user select permission >>on a table if the domain user doesn't has not been granted access to the >>database where that table resides? > > > No. A user must have a database security context in order to access data in > that database. This can be by individual account, group membership or the > guest user. > > >>I wanted to know if in SQL2000 it's possible to execute an update trigger >>with the permissions of the table's owner rather than the permissions of >>the current user. > > > No, but you don't need to do that. As long as the ownership chain is > unbroken (all objects involved have the same owner), permissions on > indirectly referenced objects are not checked. This means that a user with > UPDATE permissions on THISDATABASE.dbo.CUSTOMERS can execute the update > trigger and access the MYOTHERDATABASE.dbo.TEST table even though no > permissions on MYOTHERDATABASE.dbo.TEST have been granted to the invoking > user. > > There are special considerations for cross-database ownership chains. > First, you need to enable the 'db chaining' in the databases. This option > was introduced in SQL 2000 SP3: > > EXEC sp_dboption 'THISDATABASE', 'db chaining', true > EXEC sp_dboption 'MYOTHERDATABASE', 'db chaining', true > > Second, the databases need to have the same owner so that the 'dbo' user > ownership chain is unbroken. You can use sp_changedbowner, if needed: > > EXEC THISDATABASE..sp_changedbowner 'CommonDbOwner' > EXEC MYOTHERDATABASE..sp_changedbowner 'CommonDbOwner' > > Finally, all users with update permissions need to have a security context > in MYOTHERDATABASE, although no object permissions need to be granted. If > you don't want to add individual users, you can enable the 'guest' database > user (EXEC sp_adduser 'guest') so that all SQL Server logins can access the > database. Assuming you grant no permissions to guest, permissions will be > limited to those of the public role. > > A security consideration with cross-database chaining is that you should > enable the option only when you fully trust users that have permissions to > create dbo-owned objects in those databases. This is especially true of > sa-owned databases, where only sysadmin role members should have those > permissions. > > See the Books Online for more information.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |