How to authenticate users in the sysuser table?
-
Hi,
Need help in writing a stored procedure to authenticate users in the sysuser table. My Front End is .Net which will pass the userid and password to the stored procedure.
I want to be able to do something like this.
Select name, password
from sysuser
where name = @user-id
and password = @password
As the password is kept as varbinary, how should I go about authenticating it?
Please advise.
Thanks
-
The password in sysusers is not used as far as I know. BOL says it is for internal use, but I have never seen anything other than null for it. What are you trying to do here? Are you implementing your own application authentication level and storing the users in sysusers?
-
Yup, I am trying to implement my own application authentication. Once the users are authenticated, they will get to access the application.
I will be having intranet and internet users. which means i can't use windows authentication.
I just found out that the password column is NULL.
My users will automatically be found in sysusers, I do not have a separate users table.
Please advise
-
But how do you mean they will automatically be found in sysusers? Either you are storing rows there (by INSERTing), which you should definitely not be doing. Or you are talking about standard SQL Server users created by granting SQL Server logins access to the database.
My guess is that you have created separate SQL Server logins for each person that will be using the application. If so, then why can you not just use the builtin functionality in SQL Server for authenticating logins?
I have a hunch that there is really something else that is the trouble here. What are you trying to do with your procedure (what functionality are you adding)?
-
Users will be added by granting SQL Server logins access to the database.
Is it possible for you to show me an example of the built-in functionality for SQL Server in authenticating logins?
I just want to authenticate users to access the application and I have no idea how I can achieve that from sysusers table.
-
the sql passwords are stored (in encrypted form) in syslogins, which is found in the master database.
Why do you want to use the SQL users table for authentication, not your own? Wouldn't it be better fot the app to connect as a single, known user and do do its own authentication?
By granting all your application users access to the database using their application login and password, you're enabling them to go direct to the database rather than through your application. Imho, that's a rather large security hole.
Just my opinion.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I agree with GilaMonster. Either you create a user table of your own and authenticate the users against it but let your application run as a single login. Or you let each user have a separate SQL Server login, and use that login for the connection in each request. In general this is not something you do with web apps where the number of users is large or even infinite (naturally), so GilaMonster's suggestion is probably what you need.
-
Thanks, I know what you guys are talking about.
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply