create proc sp_SyncWithNT as

/*  sp_SyncWithNT by Rob Kraft March 1997
**  Place this stored procedure in Master and execute it from there
**  PLEASE RUN THIS AS A TEST(See notes below) ON YOUR SITE FIRST!!!
**  This Stored Procedure for MS SQL Server 6.5 will synchronize changes made to
**  groups on NT with SQL Server.  The code will only work with SQL Server 6.5
**  If an NT administrator adds or removes an NT account to one of the NT groups that
**  has been given access to SQL Server through Security Manager, this proc will 
**  add or remove the same loginid and username from/to SQL Server.  I recommend 
**  scheduling this proc to execute daily.  If you can improve on this please let
**  me know so that I can share your improvements with other registered users.
**  rkraft@msn.com*/

set nocount on
--create temporary table to store working data
create table #tempLogin (NTname varchar(30) null, AcctType char(30),
 PrivilegeType char(30), MappedLogin char(30) null, PermPath char(50) null,
 groupname varchar(30) null)

--retreive NTLogins from NTgroups in Security Manager into temp table
--following line only works in 6.5 or above
insert into #tempLogin  (NTName, AcctType, PrivilegeType, MappedLogin, PermPath) 
	execute xp_logininfo

--This procedure is written to NOT automate sa privilege to the server
delete #tempLogin where PrivilegeType = 'admin' or AcctType = 'user'

--Collect which group each user belongs to for use later
update #tempLogin set groupname = 'ThisIsAGroup'

--Loop through the NT Groups looking for subgroups until only Users are left
declare @NTGroup varchar(50)
declare @MappedGroup varchar(50)
select @NTGroup = min(NTname) from #tempLogin
while (@NTGroup <> null)
	begin
		select @MappedGroup = MappedLogin from #templogin where NTname = @NTGroup
		--add members of NT Group to the temp table
		insert into #tempLogin (NTName, AcctType, PrivilegeType, MappedLogin, PermPath) 
			execute ('xp_logininfo "' + @NTGroup + '", "members"')
		--now delete the NT Group name from the table
		delete #tempLogin where NTname = @NTGroup
		--Collect the group name for each user in the group just deleted
		update #templogin set groupname = @MappedGroup where groupname is null
		--retrieve the next NT Group name in the table
		select @NTGroup = min(NTname) from #tempLogin
			where AcctType like 'local%' or AcctType like 'global%'
	end


-- (THIS IS A TEST QUERY TO SEE WHO IS SELECTED TO BE GRANTED ACCESS)
--select MappedLogin from #tempLogin left join syslogins on #tempLogin.MappedLogin =
-- syslogins.name  where syslogins.name is null

-- run sp_addlogin for these
declare @nextxp varchar(30)
select @nextxp = min(MappedLogin) from #templogin left join syslogins
	on #tempLogin.MappedLogin = syslogins.name where syslogins.name is null
while (@nextxp is not null)
	begin
		-- Here is where new logins are actually added and granted permission
		exec ('sp_addlogin "' +@nextxp + '"')
		raiserror('granting login for %s' ,10,1, @nextxp) with log
		select @nextxp = min(MappedLogin) from #templogin left join syslogins
			on #tempLogin.MappedLogin = syslogins.name where syslogins.name
			is null and mappedlogin > @nextxp
	end

-- now add usernames to databases or change user groups
declare @nextdb varchar(30)
select @nextdb = min(name) from sysdatabases
while (@nextdb is not null)
	begin
		-- retrieve group names from database
		exec ('select name from ' + @nextdb + '..sysusers where uid = gid and uid <> 0
			and name in (select groupname from #templogin)')
		-- if there are no groups then skip this database
		if @@rowcount = 0 
			begin
				select @nextdb = min(name) from sysdatabases where name > @nextdb
				continue
			end
		-- process each new login to see if it should be added to THIS database
		declare @nextu varchar(30)
		declare @nextg varchar(30)
		select @nextu = min(mappedlogin) from #templogin
		while (@nextu is not null)
			begin
				select @nextg = groupname from #templogin where mappedlogin = @nextu
				-- see if users group is in this database
				exec('select name from ' + @nextdb + '..sysusers where name = "' + @nextg + '"')
				if @@rowcount <> 0 
				   begin	
					-- if group is in db, is user already in db				
					exec('select name from ' + @nextdb + '..sysusers where name = "' + @nextu + '"')
					if @@rowcount = 0
						exec ('use ' +@nextdb + ' exec sp_adduser ' + @nextu + ', ' + @nextu + ', ' + @nextg)
					else
						exec('use ' + @nextdb + ' exec sp_changegroup ' + @nextg + ',' + @nextu)
				   end
				select @nextu = min(mappedlogin) from #templogin where
						mappedlogin > @nextu
			end
	select @nextdb = min(name) from sysdatabases where name > @nextdb
end


-- (THIS IS A TEST QUERY TO SEE WHO IS SELECTED TO BE REVOKED ACCESS)
--select name from syslogins left join #tempLogin on #tempLogin.MappedLogin =
--syslogins.name  where #tempLogin.MappedLogin is null and
--	syslogins.name not in ('probe','repl_publisher', 'repl_subscriber', 'sa', 'guest')
-- First, remove usernames from databases
select @nextu = null
select @nextdb = min(name) from sysdatabases 
while (@nextdb is not null) 
	begin 
		-- process each new login to see if it should be added to THIS database
		select @nextu = min(name) from syslogins left join #tempLogin on 
			#tempLogin.MappedLogin = syslogins.name where #tempLogin.MappedLogin 
			is null and syslogins.name not in ('probe','repl_publisher', 'repl_subscriber', 'sa', 'guest')
		while (@nextu is not null)
			begin
				exec('select name from ' + @nextdb + '..sysusers where name = "' + @nextu + '"')
				if @@rowcount <> 0
					exec ('use ' +@nextdb + ' exec sp_dropuser ' + @nextu)
				select @nextu = min(name) from syslogins left join 
					#tempLogin on #tempLogin.MappedLogin = syslogins.name 
					where #tempLogin.MappedLogin is null and syslogins.name
					not in ('probe','repl_publisher', 'repl_subscriber', 'sa', 'guest')
					and name > @nextu
			end
	select @nextdb = min(name) from sysdatabases where name > @nextdb
end

-- run sp_droplogin for these
select @nextxp = min(name) from syslogins left join #tempLogin on #tempLogin.MappedLogin =
syslogins.name  where #tempLogin.MappedLogin is null and
	syslogins.name not in ('probe','repl_publisher', 'repl_subscriber', 'sa', 'guest')
while (@nextxp is not null)
	begin
		-- Here is where new logins are actually dropped
		exec ('sp_droplogin "' +@nextxp + '"')
		raiserror('dropping login for %s' ,10,1, @nextxp) with log
		select @nextxp = min(name) from syslogins left join #tempLogin 
			on #tempLogin.MappedLogin = syslogins.name 
			where #tempLogin.MappedLogin is null and syslogins.name 
			not in ('probe','repl_publisher', 'repl_subscriber', 'sa', 'guest')
			and name > @nextxp
	end

		

drop table #templogin
GO