SET NOCOUNT ON;
declare @user sysname
declare @domain varchar(100)
set @domain = 'XXX"
declare recscan cursor for
select name from sys.server_principals where type_desc in ('WINDOWS_GROUP','WINDOWS_LOGIN') --and name not like 'NT %'
and name like @domain+'%';
DECLARE @TempTable TABLE( Name nvarchar(100))
open recscan
fetch next from recscan into @user
DECLARE @group_members TABLE (
account_name sysname,
[type] sysname,
privilege sysname,
mapped_login_name sysname,permission_path sysname NULL
);
while @@fetch_status = 0
begin
begin try
INSERT INTO @group_members ( account_name,
[type],
privilege,
mapped_login_name, permission_path
) exec xp_logininfo @user-- exec xp_logininfo @user
end try
begin catch
--Error on xproc because login doesn't exist
-- print 'drop login '+convert(varchar,@user)
-- print @user
INSERT INTO @TempTable (name) values(@user)
end catch
fetch next from recscan into @user
end
close recscan
deallocate recscan
select @@SERVERNAME, * from @TempTable
