"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" <Anat***@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"
> ...