Small query to verify user in system

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Small query to verify user in system

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-01-17 : 16:39:11

Hi Everyone -

Is there a way to query the database system and find out of a user is

1) in the system and the password is the correct one for SQL
2) return the default database that the user is to use

thanks
tony

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-17 : 18:50:54

Check these out in Books Online:
exec sp_helplogins 'sa'
exec sp_helpuser 'dbo'

The encrypted passwords are stored on master..syslogins but you obviously can't compare them to the plain text version.

Be One with the Optimizer
TG

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-01-18 : 08:48:15

This is what i came up with...

CREATE PROCEDURE [dbo].[sp_VerifyLogin]

@username as varchar(50),
@password as varchar(50)

AS

SELECT [sysdatabases].[name]
FROM (SELECT pwdcompare(@password, password) AS name_count, *
FROM sysxlogins) DERIVEDTBL INNER JOIN
sysdatabases ON DERIVEDTBL.dbid = sysdatabases.dbid
WHERE (DERIVEDTBL.name_count = 1) AND (DERIVEDTBL.name = @username)
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-18 : 10:46:10

Thanks for following up. I was not aware of this undocumented function pwdcompare - interesting (and a little surprising to me).

One thing, this will not work for windows authentication logins (thankfully).
Also, you could simplify your statement somewhat.
I took the liberty of changing the functionality slightly:


use master
go
CREATE PROCEDURE [dbo].[sp_VerifyPassword]

@password as varchar(50)
,@sqlloginname as varchar(50) = null

AS

select	pwdcompare(@password, password) [PW_Match]
	,[name]
	,db_name(dbid) [defaultDB]
from	sysxlogins 
where	[name] = isNull(@sqlloginname, [name])
order by 1 desc
---------------------------------------------------------
--test it
go
sp_addlogin 'junk', '#bF89 AA00a', 'tempdb'
go

exec sp_VerifyPassword @password = '#bF89 AA00a' ,@sqlloginName = 'junk'

exec sp_droplogin 'junk'
drop proc sp_VerifyPassword

output:
New login created.
PW_Match    name          defaultDB                                                                                                                        
----------- -----------------------------
1           junk          tempdb

(1 row(s) affected)

Login dropped.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources