I have a user table and I need to create a VIEW so a user can only see their own data/row.
How could I achieve this? I have been searching online but I can't find anything useful
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You need to know who the user is and that information needs to be accessible through the view.
CREATE VIEW dbo.tPatientForUser AS SELECT patient_id, name, address, gender, loginame FROM tPatient WHERE loginame = SUSER_SNAME();
Or, depending on your application you might need:
WHERE loginame = ORIGINAL_LOGIN();
This can help you filter the rows to only those with the same user.
EDIT: Per your latest comment:
To create a login for someone, and to make them a user of your database, you can read the KB article at: https://msdn.microsoft.com/en-us/library/aa337562.aspx (Look at the examples toward the bottom of the page.)
Examples: -- Create a login for SQL Server by specifying a server name and a Windows domain account name.
CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS; GO
-- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty" -- The user login starts with the password "Baz1nga," but that password must be changed after the first login.
CREATE LOGIN shcooper WITH PASSWORD = 'Baz1nga' MUST_CHANGE, CREDENTIAL = RestrictedFaculty; GO
Remember though that the View or the Stored Procedure must implement your security plans, which you have not really explained. This tip is just showing you how to make use of a users identity, not how to create your system.
A view and individual SQL logins would be appropriate if each patient accessed the database directly using an ad-hoc query tool, like Access. However, in the case of a front-end application, I suggest performing patient authentication in the app code (perhaps with individual user credentials stored securely in a database) and then just use the patient identifier in queries involving patient data. The application can use direct table access, views, or stored procedures.