Re: Application roles Please Help!

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 01/14/03 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Mon, 13 Jan 2003 21:34:24 -0600

Other databases can be accessed only via the guest user security context
once an application role is activated. However, you don't need to grant
permissions on the referenced object in the 'MDB_TAB_01' databases as
long as the objects involved have the same owner. Because your objects
are owned by 'dbo', both databases have the same owner so that the
objects are owned by the same login.

Since the guest user needs no object permissions in this scenario, this
may appease your DBA. The application role needs permissions only on
the objects directly referenced in the application database. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Magnus Pettersson" <magnus@businessvision.se> wrote in message
news:e7emsB2uCHA.2568@TK2MSFTNGP12...
> If  I do this I get
> Server: Msg 916, Level 14, State 1, Procedure TestViewMP, Line 3
> Server user 'Conn2000' is not a valid user in database 'MDB_TAB_01'.
>
> This is due to the Application role
> I have a view called TestViewMP
> looks like
> CREATE VIEW dbo.TestViewMP
> AS
> SELECT     *
> FROM         MDB_TAB_01.dbo.AAR
> Granted to the Application role
>
> Magnus
>
> "Pura" <pn2kt@yahoo.com> wrote in message
> news:rPFU9.612$M%5.439875740@newssvr11.news.prodigy.com...
> > create your views as dbo then grant select to the App role. The
remote
> table
> > should also be owned by dbo.
> >
> > "Magnus Pettersson" <magnus@businessvision.se> wrote in message
> > news:e4GKHwzuCHA.1960@TK2MSFTNGP11...
> > > Hi !
> > >
> > > We are using application roles in our client.
> > >
> > > Now we would like to run a remote procedure or call a
> > > view with a table in an other database.
> > >
> > > Is there a workaround without using the guest account,
> > > the DBAdmin is not fond of using the guest account, to connect to
> another
> > > database ?
> > >
> > > Any suggestion is fine
> > >
> > > Best regards Magnus
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: public role???
    ... The guest user account cannot be removed from ... > databases, but not ... then that's the way SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Newly created SQL login cant read any databases - can I give it permissions to read all data
    ... group then will inherit permissions automatically. ... read/write access to all the databases on the server. ... I would like to be able to grant my SQL login user, ...
    (microsoft.public.sqlserver.server)
  • Re: Application Roles for Cross-Database Joins
    ... > The one way I can think of to sort this out - beside uniting the databases ... > into one - is to enable the server configuration parameter "Cross DB ... Jason could instead enable the 'db chaining' database option for only those ... if the guest user account does not ...
    (comp.databases.ms-sqlserver)
  • RE: missing system.mdw file
    ... permissions to create the file, ... >The registry key is not missing. ... Running the SCAN program from the Windows ... >>| I cannot create any new databases in Access 2002 on ...
    (microsoft.public.access.setupconfig)
  • Re: one SQL DB getting info from another - user setup?
    ... It appears from your narrative that the Main and DB1 databases ... EXEC sp_helpdb 'Main' ... SQL 2000 SP3 and is turned off by default. ... > In the permissions for Main, USER1 and USER2 are listed, but have NO ...
    (microsoft.public.sqlserver.security)