Inside of a Stored Procedure, testing whether a SQL Server user exists, and whether their password is valid

I need create a SQL Server stored procedure that does the following:

1. Take an database user name and password as parameters.
2. If the user does not exist or the passwo开发者_运维百科rd is invalid, return an empty rowset.
3. Else, perform a query and return its result.

I don't know how do perform step 2. Any ideas?

There are two routes to go with this:

1) Query the SQL Server tables directly to find a match. You'll probably have to go through a view, instead of accessing the table directly. Maybe, select * from Sys.sql_logins

Need to find a way to hash the password, to compare against the hashed version, in Sys.sql_logins.password_hash

As of SQL Server 2008, they added the function PWDCOMPARE(). That's clearly the way MS intended for this to be solved.

So, in 2008, I would think you'd query it as select * from Sys.sql_logins where name = 'login' and PWDCOMPARE('password', Sys.sql_logins.password_hash) = 1

Oddly enough, I just tried this on SQL 2005, and the function seems to exist.

2) Attempt a connection to the SQL server via a call to OPENDATASOURCE () and test for failure. MSDN page for OPENDATASOURCE() is at: http://msdn.microsoft.com/en-us/library/aa276845(v=sql.80).aspx

Why not let SQL Server do this for you. That's what the built-in security scheme is for, after all. Simply grant permissions to the user (or role) to the stored procedure(s) that contains the query for the data. Then, in the application calling the SP, you'll open a connection to the DB with that user's login credentials. If the user is invalid, they can't connect and you can catch the exception. If the user is valid in the DB but does not have permissions to the stored procedure, you can catch the exception.