Security tide to SQL Server 2005?

[eggy168]

Hi,
I have an Access 2003 form(s) that tide to Sql Server 2005's tables. I would like to know is there possible for an user who open the form and typed in the User ID & Password, then either Microsoft Access or SQL Server can automatically identify the user's corresponding data only.

I have the security set up in SQL Server that each user has the User ID & Password, and the a view (which is the tables that join the User password & the user's data).

These are the tables I have in the SQL Server

User table: User ID, User Password
Role: User ID, Account ID
Data: Account ID, Column 1, Column 2

Thank you.

 

[danvlas]

well...
In SQL Server:

Select data.*
From data d
Inner join role r on d.accountid=r.accountid
Inner join user u on u.userid=r.userid
Where u.userid=@userid and u.UserPassword=@UserPassword

I have 2 suggestions:

- get rid of the spaces in table/field names
- think again about Roles table. It shouldn't have the UserID attribute.
It's the Users table that needs a RoleID foreign key


Daniel Vlas
Systems Consultant

 

[eggy168]

Thank for your quick respond, Daniel Vlas,
I did get rid of the spaces in table/field names, and will consider the RoleID foregin key.

When I wrote the script in the SQL Server in the View and tried to save it, it has the error code, "Must declare the scalar variable @userid. I don't know what to do with it in order for me to save the View so I can import the View into the Access?

Thank you very much for your help.

 

[eggy168]

I tried to create the variable at the Programmability, but it is still not working?