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