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 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?

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?

Administrating content with integrated security?

I have a two tier application with a .net client that access data from a SQL
Server. Depending on what windows user group the user belong to I want them
to get different data. The solution I would like to have is one where a could
change only in the database and nothing in the client and still get different
users to get different data.
There is only one table that should differ for different the users. One
solution would therefore be to overload the table, i.e. create one table for
each user all with the same name but with different owners. Because the
client doesn't explicitly state the owner of the object it would get the
table owned by the user running the client.
This solution would lead to far to many identical copies of the table and
for each new user at the company you would have to create a new table on the
server. A better solution would be if it were possible to connect tables to
server roles and if the client would get access the table that is connected
to the server role that the user belongs to. To me, it seems this solution is
not possible because SQL Server will identify the table with the user and
never try with the role.
Regards,
Joeluse a trigger or a view on the table to match the user with a column to
select only the rows that pertain to them (current_user)
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:64B7A8C1-FF4A-4CF4-B3B7-8BE713D61E68@.microsoft.com...
>I have a two tier application with a .net client that access data from a
>SQL
> Server. Depending on what windows user group the user belong to I want
> them
> to get different data. The solution I would like to have is one where a
> could
> change only in the database and nothing in the client and still get
> different
> users to get different data.
> There is only one table that should differ for different the users. One
> solution would therefore be to overload the table, i.e. create one table
> for
> each user all with the same name but with different owners. Because the
> client doesn't explicitly state the owner of the object it would get the
> table owned by the user running the client.
> This solution would lead to far to many identical copies of the table and
> for each new user at the company you would have to create a new table on
> the
> server. A better solution would be if it were possible to connect tables
> to
> server roles and if the client would get access the table that is
> connected
> to the server role that the user belongs to. To me, it seems this solution
> is
> not possible because SQL Server will identify the table with the user and
> never try with the role.
> Regards,
> Joel