Application Role for multiple databases

15 Jul 2005 5:35 PM

Perayu

I need to use Application Role for our security issue. But my application use
three (3) different databases. After I setup Application Role in one
database, my application can not access to other 2 database, unless create a
'guest" account, which I don't want to do that. Is there anyway that I can
do it without merging those 3 databases to one?

Thankss for any help .

16 Jul 2005 10:59 AM

ML

Create a special SQL or (preferably) NT user with appropriate privileges in
all relevant databases and connect through that special user with your
application.

ML

16 Jul 2005 7:16 PM

Perayu

I still want to use Windows Authentication mode as we are using now. Is there
any other choise?

Thanks!

Show quote

"ML" wrote:

> Create a special SQL or (preferably) NT user with appropriate privileges in
> all relevant databases and connect through that special user with your
> application.
>
>
> ML

17 Jul 2005 3:27 PM

ML

As I've said before, using a dedicated NT user is the prefered solution. I
usually create an NT user with a meaningful name constructed from the name of
the module (application). This special user must exist in the domain, of
course. Then you grant him SQL privileges.

In SQL Server you can manage privileges for both SQL and NT users.

ML

18 Jul 2005 1:34 PM

Perayu

Hi, ML:

Does that means I can not use Application Role account and my application
need to pass the UserName and password for that specific account to connect
to my database? When I run sp_setapprole, all my privilege for another
database is gone. According to BOL, I need to have 'guest' account to access
to another databases.

Thanks, ML.

Perayu

Show quote

"ML" wrote:

> As I've said before, using a dedicated NT user is the prefered solution. I
> usually create an NT user with a meaningful name constructed from the name of
> the module (application). This special user must exist in the domain, of
> course. Then you grant him SQL privileges.
>
> In SQL Server you can manage privileges for both SQL and NT users.
>
>
> ML

18 Jul 2005 3:00 PM

ML

The Application Role is pretty useless in SQL 2000 (compared to SQL 2005).
It's easier using a dedicated user - especially in a multi-db environment.
I'd advise against using the Guest account, for two main reasons:

1) insufficient privileges for most cases (most of the time applications
will not just read from other databases, but will require more privileges,
for which the Guest account simply isn't suited); and
2) compromised security of databases with the Guest account enabled.

Just create a special windows user, a SQL login account for that user and
grant all the necessary privileges. Then connect to the db's using this
special user.

ML

19 Jul 2005 1:09 PM

Perayu

Thanks for help. I will try to use a special user account to do the job.

Regard,

Perayu

Show quote

"ML" wrote:

> The Application Role is pretty useless in SQL 2000 (compared to SQL 2005).
> It's easier using a dedicated user - especially in a multi-db environment.
> I'd advise against using the Guest account, for two main reasons:
>
> 1) insufficient privileges for most cases (most of the time applications
> will not just read from other databases, but will require more privileges,
> for which the Guest account simply isn't suited); and
> 2) compromised security of databases with the Guest account enabled.
>
>
> Just create a special windows user, a SQL login account for that user and
> grant all the necessary privileges. Then connect to the db's using this
> special user.
>
>
> ML