sp_addlogin error - SQL Server Forums

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 Administration (2000)

sp_addlogin error

Author

Topic

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-04-29 : 10:18:42

SELECT 'exec sp_addlogin @loginame = ''' + su.name + ''', @sid = ' + su.sid + ' go '
FROM sysusers su
LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid
WHERE su.islogin = 1 --su.issqluser = 1 --AND sl.sid IS NULL
AND su.name NOT IN ('guest', 'INFORMATION_SCHEMA')
And su.name NOT IN ('dbo', 'INFORMATION_SCHEMA')

how to add su.sid to a string as above. I couldn't make it right.

Thanks
Feng

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-04-29 : 11:11:37

There may be an easier way, but the only way I know how is to create a function to do the conversion for you. Something like this will work.


CREATE FUNCTION dbo.converthexstring 
(@binpass AS varbinary(32))
RETURNS varchar(255)
AS
BEGIN
	DECLARE @charpass varchar(255)
	EXEC master.dbo.xp_varbintohexstr @binpass, @charpass OUTPUT
	RETURN(@charpass)
END

SELECT 'exec sp_addlogin @loginame = ''' + su.name + ''', @sid = ' + dbo.converthexstring(cast(su.sid as varbinary(32))) + ' go ' 
FROM sysusers su 
LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid 
WHERE su.islogin = 1 --su.issqluser = 1 --AND sl.sid IS NULL 
AND su.name NOT IN ('guest', 'INFORMATION_SCHEMA') 
And su.name NOT IN ('dbo', 'INFORMATION_SCHEMA')

Jeff Banschbach, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources