Re: Is there a way to Use an Application Role with more than one database?

So you are still getting the 'is not a valid user in database' error? Did you enable the guest user? i.e.:

USE ProductionDB
EXEC sp_adduser 'guest'

I've double checked everything. (Presumable I shouldn't remove guest permissions to systems objects in the ProductionDB?)

You shouldn't need to grant or revoke guest permissions. The only purpose of guest in this case is to provide a default security context. System object permissions will be inherited from the public role.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Clive" <clive.elsworth@xxxxxxxxxxxxxxxxx> wrote in message news:eTcBhiIGHHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for that Dan, however:

I'm still getting the same error message when using the app role on the Dev DB.

I've double checked everything. (Presumable I shouldn't remove guest permissions to systems objects in the ProductionDB?)

It's not a major deal at the moment because I'm just volume testing, however it would be good to know if its possible to continue using app roles when a system accesses more than one DB.

If you can offer anything else I'll try it. If not I'll leave it for now.

Thanks again, Clive


"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:101BB10D-713C-4826-8542-D2485A5D2DB4@xxxxxxxxxxxxxxxx
However when testing the app I get the error: "Server User '<servername>\<dbusername>' is not a valid user in database <separate DB>"

One of the drawbacks to application roles is that the security context is valid only in the database in which the role was activated. All other databases are accessed as the guest user.

Can anyone provide any pointers or tips on this? (Apart from moving the big table to the Development DB)

If your UNION ALL query is encapsulated in a view or stored procedure, you can address your security requirement with cross-database ownership chaining. However, due to the security issues with development and production on the same box, I suggest you consider cross-database chaining only if sysadmin role members are the only users that can create dbo-owned objects. See the Books Online for more information on cross-database chaining.

Assuming your objects are owned by dbo, you can enable cross-database chaining as follows:

1) enable 'db chaining' in both databases:

EXEC sp_dboption 'DevelopmentDB', 'db chaining', true
EXEC sp_dboption 'ProductionDB', 'db chaining', true

2) Ensure both databases are owned by the same login (so the 'dbo' user ownership chain is unbroken):

USE DevelopmentDB
EXEC sp_changedbowner 'OwnerLogin'
USE ProductionDB
EXEC sp_changedbowner 'OwnerLogin'

3) Enable the guest user in the ProductionDB

EXEC sp_adduser 'guest'

Importantly, make sure no permissions are granted to guest or public in the production database.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Clive" <clive.elsworth@xxxxxxxxxxxxxxxxx> wrote in message news:uje4MqFGHHA.3268@xxxxxxxxxxxxxxxxxxxxxxx
I've been using App Roles very successfully since SQL 7, but now I'm doing a volume test (with SQL 2000) and have Unioned two tables together - one big one which is in a separate DB, whilst the other one - and everything else - is still in the Development DB.

However when testing the app I get the error: "Server User '<servername>\<dbusername>' is not a valid user in database <separate DB>"

This is despite the fact that a user of the same name exists in both DBs. The only way I find I can test the app is go back to the old fashioned (and more complex) security model.

Can anyone provide any pointers or tips on this? (Apart from moving the big table to the Development DB)

Any help much appreciated.

Thank you

Clive (London UK)






.

Relevant Pages

  • Re: Is there a way to Use an Application Role with more than one database?
    ... There was a guest in the list from Enterprise Manager, but when I tried to drop it it gave an error "no such user. ... permissions to systems objects in the ProductionDB?) ... One of the drawbacks to application roles is that the security context is valid only in the database in which the role was activated. ... If your UNION ALL query is encapsulated in a view or stored procedure, you can address your security requirement with cross-database ownership chaining. ...
    (microsoft.public.sqlserver.security)
  • Re: Is there a way to Use an Application Role with more than one database?
    ... Then I tried sp_adduser guest and it worked. ... permissions to systems objects in the ProductionDB?) ... chaining only if sysadmin role members are the only users that can ... EXEC sp_dboption 'DevelopmentDB', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: Application Role & 2nd Database
    ... Since an application role is only known in a single database, ... No guest user permissions need to be granted. ... EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE' ... > EXEC sp_configure 'Cross DB Ownership Chaining', ...
    (microsoft.public.sqlserver.security)
  • Re: Guest account in SQL 2005?
    ... The guest account in SQL Server serves the same basic function as the ... just like you wouldn't grant permissions to the Everyone group in Windows. ... priviledges in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Guest account in SQL 2005?
    ... You should not need go grant permissions to the guest user as that defeats ... Did you set DB_CHAINING ON for both database A and B? ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)