Help!!
I used to have an Access database which had tables for users, roles, actions etc.
This was used by a C++ client app (using ADO) which logged in, got the user ID and password (by raising a login dialog ) and then checked these against a user table and then assigned the roles and possible actions.
Now we have SWL Server Express 2005 - NT Authorisation - how do I get/pass the user ID to the C++ Application so it can get the associated roles Seems silly to have 2 logins.
Better still can I do away with the App's User table or make its password column invisible to all users bar Admin and the C++ App
SQL Server Express Client app login / password
Jurgen Meijer
Hi,
if you are using Windows Authentication you can use role based security which enabled you to Select the relevant rows based on the information that the user should be able to see according to his username. A table could be something like this:
CREATE Table Roles
(
UserName VARCHAR(500),
RoleId INT
)
INSERT INTO ROles
SELECT 'SomeDOmain\Kalen',1
INSERT INTO ROles
SELECT 'SomeDOmain\Kalen',2
INSERT INTO ROles
SELECT 'SomeDOmain\Bob',2
The Following view:
CREATE VIEW VI_Roles
AS
SELECT RoleId FROM Roles
WHERE UserName = SYSTEM_USER()
would bring back 1,2 for Kalen and only 2 for Bob.
I am sure, that this can be easily adopted to your environment :-)
HTH, Jens K. Suessmeyer.
---
http://www.sqlserver2005.de
---