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?

ADO Integrated Security Pass Through

I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Is it safe enough to use Integrated Security via the connection string. If
that succeeds, read the windows user name and check that against an internal
username?
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
Any comments or suggestions would be great.First key point: By using Windows authentication, you do NOT have to have a
USERS table. In SQL Server, you assign all permissions to the Windows login
or the Windows network group. (It makes life so much easier for the DBA.)
Windows Integrated Security (for SQL 2000) is far superior to SQL
authentication, and far better than trying to keep a USERS table up to date.
In SQL Server, use the SYSTEM_USER system function to retrieve the users
login name (in the form of [domain\username].)
Example:
SELECT SYSTEM_USER
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uJVfx3ntGHA.2392@.TK2MSFTNGP05.phx.gbl...
>I am working on making my application inherit the workstation login user
>information so that users aren't presented with multiple logins.
> Is it safe enough to use Integrated Security via the connection string. If
> that succeeds, read the windows user name and check that against an
> internal username?
> Does anyone have any experience linking internal application user
> management (ie: USERS table) with windows authentication?
> Any comments or suggestions would be great.
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Mmz2potGHA.324@.TK2MSFTNGP06.phx.gbl...
> First key point: By using Windows authentication, you do NOT have to have
> a USERS table. In SQL Server, you assign all permissions to the Windows
> login or the Windows network group. (It makes life so much easier for the
> DBA.)
> Windows Integrated Security (for SQL 2000) is far superior to SQL
> authentication, and far better than trying to keep a USERS table up to
> date.
> In SQL Server, use the SYSTEM_USER system function to retrieve the users
> login name (in the form of [domain\username].)
> Example:
> SELECT SYSTEM_USER
> --
Thanks Arnie. That call is very helpful.
However, 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?