I have a stored procedure called 'authenticate' that returns me the user profile record if provided correct username and password. Now I am trying to get all users in system if 'authenticate' stored procedure returned me a record otherwise return nothing. I am trying something like this:
IF EXISTS EXECUTE authenticate @UserName, @Password
BEGIN
SELECT * from Users;
END
I am getting error: Incorrect syntax near the keyword 'EXECUTE'. Any ideas what am I doing wrong?
EXISTS takes a SELECT subquery not the results of a stored procedure. See MSDN. You will need to either replicate your SELECT from authenticate in your EXISTS statement or populate a table with the results prior to the EXISTS. e.g.
INSERT INTO #authenticate (col1, col2, col3)
EXEC authenticate @UserName, @Password
IF EXISTS (SELECT 1 FROM #authenticate)
BEGIN
SELECT * from Users;
END
You can transform your procedure into function:
create function dbo.authenticate(@UserName varchar(50), @Password varchar(50))
returns @found table(userName varchar(50), userPass varchar(50)) as
begin
-- some of your internal table
declare @user table (userName varchar(50), userPass varchar(50));
insert into @user values ('John', '123'), ('Jack', '345');
insert into @found (userName, userPass)
select
u.userName, u.userPass
from
@user u
where
u.userName = @UserName and u.userPass = @Password
;
return;
end;
go
if exists(select * from dbo.authenticate('John', '123')) begin
print 'exists';
end;
I see several design problems:
Method "Authenticate" returns some rowset. Why? It is called "authenticate" - not "SelectAuthenticatedUserInfo". I assume this procedure does more than one job.
Split it to real authentication method which gives probably an output variable with session-id or bit flag yes/no = ok/fault. And create another procedure something like dbo.SelectAuthUserProfile which is called once at application start, calls from within dbo.Authenticate and if it succeeds - returns rowset with according data.
This select looks like a regular work, I mean - it's not likely a one-time "low-level" work on app start or sign-in/sign-out. Why are you performing authenticate method? Are you serious that you want to pass username and (!) password every time you want to select some data? Authentication is a one-time job per session (or even some longer scenarios).
Do authenticate your user, then just check if he is already authenticated or not. Store your session data somewhere, pass only a key or something (or even don't pass anything - there are some tricks with ## or # tables, spid+login time and so on).
It does not seem to me that every user may view contents of Users table. Access can be controlled by internal sql-server's tools.
Create schema for admin-specific stored procs, create role for such super-users, grant'em this schema's procedures. This will exclude any possibility for a regular user to execute such proc.
Think further of managing access to different parts of your system. To the question "Is this user permitted to perform such an action?" you are trying to answer "He is authenticated!" Okay, authenticated. So what? Does he have a permission or not?
Authenticate user once, then - control his permissions. There cannot be "authenticate" method inside of a regular proc. But "IsPermitted" I guess is supposed to be. (this item correlates with 1st - to many responsibilities for a proc)
Furthermore, think of this: permitted to see these orders, but not permitted to see those. Can you arrange it with a proc call like if exec then?
