MSDB access removed when approle (in a user db) is set?

Our application connects to it's database using an account with minimal
rights (denydatareader and denydatawriter) and then sets an application role
with a password known only to the application.
One of the things our application is required to do is to provide details of
recent backups that have been taken of it's database, and it does this by
performing SELECTs on tables in the MSDB database. I believe it can do that
because of the existence of the guest account provided by default in that
database?
However when we install our app onto a server that has this "guest" account
removed from MSDB, our application's login can no longer access the MSDB
tables. And when we explicitly add the specific account as a user in the
MSDB database it can once again from QA but not from within the application.
I think this is because setting the approle inside the application is
somehow affecting the identity of the user in the MSDB database?
Does this sound reasonable? Do you have any ideas of what else I could look
at or for?
regards,
Paul Ritchie.> I think this is because setting the approle inside the application is
> somehow affecting the identity of the user in the MSDB database?
Yes, once an app role is activated, other databases can be accessed only as
guest.
If you don't want to rely on the guest account in msdb, you can add your
application account to msdb (default public role) and execute those queries
without the application role activated on that connection. Other queries
can be run with the application role activated.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:u0BU6FnAEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Our application connects to it's database using an account with minimal
> rights (denydatareader and denydatawriter) and then sets an application
role
> with a password known only to the application.
> One of the things our application is required to do is to provide details
of
> recent backups that have been taken of it's database, and it does this by
> performing SELECTs on tables in the MSDB database. I believe it can do
that
> because of the existence of the guest account provided by default in that
> database?
> However when we install our app onto a server that has this "guest"
account
> removed from MSDB, our application's login can no longer access the MSDB
> tables. And when we explicitly add the specific account as a user in the
> MSDB database it can once again from QA but not from within the
application.
> I think this is because setting the approle inside the application is
> somehow affecting the identity of the user in the MSDB database?
> Does this sound reasonable? Do you have any ideas of what else I could
look
> at or for?
> regards,
> Paul Ritchie.
>
>|||Dan - you're a champion. Much appreciated!
cheers,
Paul.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OCQXoarAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> Yes, once an app role is activated, other databases can be accessed only
as
> guest.
> If you don't want to rely on the guest account in msdb, you can add your
> application account to msdb (default public role) and execute those
queries
> without the application role activated on that connection. Other queries
> can be run with the application role activated.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:u0BU6FnAEHA.1548@.TK2MSFTNGP12.phx.gbl...
> role
details
> of
by
> that
that
> account
the
> application.
> look
>