ADO Integrated Security Pass Through Again

The previous thread died so I'm sorry for the repost.
I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
I have access rights that a specific to my application such as menu
options, reports, specific actions, etc. The application was developed 8
years ago and supported multiple database platforms. We have moved to only
supporting MS SQL Server 7, 2000 and 2005.
What's the best way to link these together since DBAs wouldn't be able to
assign my application specific access rights via MS SQL Server Management
Studio/Enterprise Manager?
Any comments or suggestions would be great.Hi!
First, your application should use ADO connection string like
"SERVER=mymssql; Integrated Security=SSPI;"
thus application will connect to SQL with current logged user
credentials. Naturally, user must have some rights on SQL server.
You can set those right for NT group, not for individual user accounts.
The user account name is accessible in t-sql and you can build some
additional logic:
CREATE PROC GetCustomUserRights
AS
declare @.NT_login varchar(64)
set @.NT_login = SYSTEM_USER
select UserRightName,UserRightValue from USERS where UserName = @.NT_login
RETURN 0
GO
implying the table USERS has structure and data like:
UserName, UserRightName, UserRightValue
"ACME\user1", "AdvancedMenu", "True"
"ACME\user2", "AdvancedMenu ", "False"
...
Best regards, Anatoli
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote

> The previous thread died so I'm sorry for the repost.
> I am working on making my application inherit the workstation login user
> information so that users aren't presented with multiple logins.
> Does anyone have any experience linking internal application user
> management
> (ie: USERS table) with windows authentication?
> I have access rights that a specific to my application such as menu
> options, reports, specific actions, etc. The application was developed 8
> years ago and supported multiple database platforms. We have moved to only
> supporting MS SQL Server 7, 2000 and 2005.
> What's the best way to link these together since DBAs wouldn't be able to
> assign my application specific access rights via MS SQL Server Management
> Studio/Enterprise Manager?
> Any comments or suggestions would be great.
>|||"Anatoli Dontsov" <Anatoli@.dontsov.com> wrote in message
news:eXg$wQOvGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Hi!
> First, your application should use ADO connection string like
> "SERVER=mymssql; Integrated Security=SSPI;"
> thus application will connect to SQL with current logged user
> credentials. Naturally, user must have some rights on SQL server.
> You can set those right for NT group, not for individual user accounts.
> The user account name is accessible in t-sql and you can build some
> additional logic:
> CREATE PROC GetCustomUserRights
> AS
> declare @.NT_login varchar(64)
> set @.NT_login = SYSTEM_USER
> select UserRightName,UserRightValue from USERS where UserName = @.NT_login
> RETURN 0
> GO
> implying the table USERS has structure and data like:
> UserName, UserRightName, UserRightValue
> "ACME\user1", "AdvancedMenu", "True"
> "ACME\user2", "AdvancedMenu ", "False"
> ...
Thanks. Has anyone had any experience creating the list of users for the
access rights? For example: a user logs in and I check their access rights,
is the best solution to have admin type in the NT login name into my app and
add access rights? Or can I provide a dropdown of users based in some
network query?