This proc, when scheduled nightly, will compare the login ids in NT to those on SQL Server. If there are new ones on NT in the appropriate groups, it will add them to SQL Server. If one has been removed from NT, it will be removed from SQL Server! This works on 6.5 because 6.5 allows inserts with sub-selects that are stored procedures. This will be most useful in integrated security.

create proc sp_SyncWithNT as

/* sp_SyncWithNT by Rob Kraft January 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. To
** become a registered user send a check for whatever you think this is worth to you
** or your company to Rob Kraft - 1105 NE Hendrix - Lee's Summit MO 64086. Please
** include an email return address if possible. My current email address is
** [email protected]*/

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 where name in ('training', 'testing')
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 mappedlogin > @nextu
end
select @nextdb = min(name) from sysdatabases where name > @nextdb and name in ('training', 'testing')
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