Ken

Thanks for the info and direction but I either miss something or it doesn't
do what I need.
It seems to me that the application role allows the user to get extra rights
that they may not have otherwise (eg. when not connecting through the
application), but they still have to connect to the database themselves.
In my scenario:
1. User my_dba owns table tbl_conn.
2. Application role app_role has been created and granted SELECT, INSERT
rights to the table.
3. Two users jsmith, bwhite don't have rights to use the tbl_conn table.

In order to use the application role, the aplication has to execute
sp_setapprole() stored procedure so there already has to be connection to
the database.

When user jsmith uses my application he logs in with his username and
password. Then my application calls sp_setapprole() to give the extra
priviledges on tbl_conn table.
When user bwhite uses my application he logs in with his username and
password and then my application calls sp_setapprole() to give the extra
priviledges on tbl_conn table.

But I end up with two connections (or two connection pools). I don't see how
I could open a connection eg. user1/user1 and when jsmith connects use the
connection but change the user context.

I know I can use the user1 connection to verify that jsmith is a valid user,
but do everything as user1. That would use one pool. But the database would
not know that the jsmith is the user unless I write it to a table...

What am I missing?

Thanks

Witold





-----Original Message-----

...

However, what you want to do (have a single pool) is possible under SQL
Server. In SQL Server Books Online, look up "Application Roles" in the
index. You can use an application role to supplant the Windows user context
with the application context. All users would be using this context to
access the database, so there would only be 1 pool, and 1 set of
connections.

Cheers
Ken

> Ken

Thanks for the info and direction but I either miss something or it doesn't
do what I need.
It seems to me that the application role allows the user to get extra 
rights
that they may not have otherwise (eg. when not connecting through the
application), but they still have to connect to the database themselves.
In my scenario:
1. User my_dba owns table tbl_conn.
2. Application role app_role has been created and granted SELECT, INSERT
rights to the table.
3. Two users jsmith, bwhite don't have rights to use the tbl_conn table.

In order to use the application role, the aplication has to execute
sp_setapprole() stored procedure so there already has to be connection to
the database.

When user jsmith uses my application he logs in with his username and
password. Then my application calls sp_setapprole() to give the extra
priviledges on tbl_conn table.
When user bwhite uses my application he logs in with his username and
password and then my application calls sp_setapprole() to give the extra
priviledges on tbl_conn table.

But I end up with two connections (or two connection pools). I don't see 
how
I could open a connection eg. user1/user1 and when jsmith connects use the
connection but change the user context.

I know I can use the user1 connection to verify that jsmith is a valid 
user,
but do everything as user1. That would use one pool. But the database would
not know that the jsmith is the user unless I write it to a table...

What am I missing?

Thanks

Witold

Here is a link to ado.net connection pooling for the SQL Server .Net data 
provider. 

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

Pretty simple. The gotcha is that the connection string for each 
connection must be the same.

If you notice, there is only one user within thePhile database. All data 
access is done as 'sa'. The connection string through out thePhile is the 
same. Perfect for connection pooling. 

A production database would, of course, have an application level user to 
perform data access. Individual users of the application's front end would 
inherit the application level user's permissions within the database.

You could, however, utilize the Roles portion of SQL Server to provide 
users with rights and permissions. This would effectively remove the 
connection pooling capability.

Again, check out the link above.
Have fun.

Ryan