|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Access to another database via a stored procedureI have a stored procedure on a database that selects information from
another database, and the users running the stored procedure are not users on the 2nd database. I don't want to have to enter all users individually into the 2nd database, I just want anyone who has execute rights to that stored procedure to be able to select information from the 2nd database. Do I have to somehow in the stored procedure log into the 2nd database as a public user? Right now I am getting errors that says the user is not a user of the 2nd database. The 2nd database is a public database, so I shouldn't need to be a user on it. Users need a security context in all databases accessed. If you don't want
to add users to the 2nd database, one method: 1) enable the 'guest' user in the second database (sp_adduser 'guest') 2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db chaining', true) If you objects are objects are owned by 'dbo', both databases need to have the same owner so that the dbo user ownership chain is unbroken. You can execute sp_changedbowner, if necessary. No permissions need be granted to guest. Guest permissions are limited to those granted to public. Note that you should fully trust those users that can create dbo-owned objects before you enable cross-database chaining ('db chaining'). If the databases are owned by 'sa', ensure only sysadmin role members should have permissions to create db-owned objects. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "et" <eagletender2***@yahoo.com> wrote in message news:eO3CPC1AGHA.4080@TK2MSFTNGP14.phx.gbl... >I have a stored procedure on a database that selects information from >another database, and the users running the stored procedure are not users >on the 2nd database. I don't want to have to enter all users individually >into the 2nd database, I just want anyone who has execute rights to that >stored procedure to be able to select information from the 2nd database. Do >I have to somehow in the stored procedure log into the 2nd database as a >public user? > > Right now I am getting errors that says the user is not a user of the 2nd > database. The 2nd database is a public database, so I shouldn't need to > be a user on it. > Thanks so much, this is very helpful information. I think the guest account
will work just fine, as I agree that I don't think I want to do the cross ownership until I see the need. Thanks. Thanks! Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:OsaivR1AGHA.2512@TK2MSFTNGP09.phx.gbl... > Users need a security context in all databases accessed. If you don't > want to add users to the 2nd database, one method: > > 1) enable the 'guest' user in the second database (sp_adduser 'guest') > > 2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db > chaining', true) > > If you objects are objects are owned by 'dbo', both databases need to have > the same owner so that the dbo user ownership chain is unbroken. You can > execute sp_changedbowner, if necessary. No permissions need be granted to > guest. Guest permissions are limited to those granted to public. > > Note that you should fully trust those users that can create dbo-owned > objects before you enable cross-database chaining ('db chaining'). If the > databases are owned by 'sa', ensure only sysadmin role members should have > permissions to create db-owned objects. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "et" <eagletender2***@yahoo.com> wrote in message > news:eO3CPC1AGHA.4080@TK2MSFTNGP14.phx.gbl... >>I have a stored procedure on a database that selects information from >>another database, and the users running the stored procedure are not users >>on the 2nd database. I don't want to have to enter all users individually >>into the 2nd database, I just want anyone who has execute rights to that >>stored procedure to be able to select information from the 2nd database. >>Do I have to somehow in the stored procedure log into the 2nd database as >>a public user? >> >> Right now I am getting errors that says the user is not a user of the 2nd >> database. The 2nd database is a public database, so I shouldn't need to >> be a user on it. >> > > I'm glad you found the information useful.
Just to be clear, if you don't enable cross database chaining, you will then need to grant SELECT permissions to guest (or public) in the second database because the ownership chain is broken. This will effectively allow all server users not already in the second database to select from the table directly. This might be ok in your situation but I want to make sure you are aware of the ramifications. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "et" <eagletender2***@yahoo.com> wrote in message news:uh%23icI2AGHA.2788@TK2MSFTNGP14.phx.gbl... > Thanks so much, this is very helpful information. I think the guest > account will work just fine, as I agree that I don't think I want to do > the cross ownership until I see the need. Thanks. > > Thanks! > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:OsaivR1AGHA.2512@TK2MSFTNGP09.phx.gbl... >> Users need a security context in all databases accessed. If you don't >> want to add users to the 2nd database, one method: >> >> 1) enable the 'guest' user in the second database (sp_adduser 'guest') >> >> 2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db >> chaining', true) >> >> If you objects are objects are owned by 'dbo', both databases need to >> have the same owner so that the dbo user ownership chain is unbroken. >> You can execute sp_changedbowner, if necessary. No permissions need be >> granted to guest. Guest permissions are limited to those granted to >> public. >> >> Note that you should fully trust those users that can create dbo-owned >> objects before you enable cross-database chaining ('db chaining'). If >> the databases are owned by 'sa', ensure only sysadmin role members should >> have permissions to create db-owned objects. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "et" <eagletender2***@yahoo.com> wrote in message >> news:eO3CPC1AGHA.4080@TK2MSFTNGP14.phx.gbl... >>>I have a stored procedure on a database that selects information from >>>another database, and the users running the stored procedure are not >>>users on the 2nd database. I don't want to have to enter all users >>>individually into the 2nd database, I just want anyone who has execute >>>rights to that stored procedure to be able to select information from the >>>2nd database. Do I have to somehow in the stored procedure log into the >>>2nd database as a public user? >>> >>> Right now I am getting errors that says the user is not a user of the >>> 2nd database. The 2nd database is a public database, so I shouldn't >>> need to be a user on it. >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics