SET NOCOUNT ON

USE Admin
GO

CREATE TABLE QTW_Logins
(
	loginId int IDENTITY(1, 1) NOT NULL,
	loginName nvarchar(128) NOT NULL,
	passwordHash varbinary(256) NULL,
	sid varbinary(85) NOT NULL
)

-- openquery is used so that loginproperty function runs on the remote server, otherwise we get back null
INSERT INTO QTW_Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([SDSQL01\QTW], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
	type = ''S'' AND 
	name NOT IN (''sa'', ''guest'') AND 
	create_date >= ''12/31/2005''
ORDER BY name')

DECLARE 
	@count int, @loginId int, @loginName nvarchar(128), 
	@passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
	@sid varbinary(85), @sql nvarchar(4000), @password varchar(514)

SELECT @loginId = 1, @count = COUNT(*)
FROM Admin.dbo.QTW_Logins

WHILE @loginId < = @count
BEGIN
	SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
	FROM QTW_Logins
	WHERE loginId = @loginId

	-- if the account doesn't exist, then we need to create it
	IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
	BEGIN
		EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

		SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = ' + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) + ' HASHED, CHECK_POLICY = OFF' 
		EXEC (@sql)

		PRINT 'login created'
	END
	-- if the account does exist, then we need to drop/create; can't alter as hashed isn't supported
	ELSE
	BEGIN
		SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

		IF @passwordHashOld <> @passwordHashNew
		BEGIN
			EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT

			SET @sql = 'DROP LOGIN ' + @loginName
			EXEC (@sql)

			SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = ' + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) + ' HASHED, CHECK_POLICY = OFF' 
			EXEC (@sql)

			PRINT 'login "altered"'
		END
	END

	SET @loginId = @loginId + 1
END

DROP TABLE Admin.dbo.QTW_Logins

quote:

---

Originally posted by JeffOrwick

The posts so far don't address an issue I'm having.

I need to Sync Server Level permission as well (i.e View Server State; Sysadmin role, etc.).

Has anyone developed scripts that address this?
Thanks.

Jeff Orwick, DBA

---