08-30-2017 11:42 AM
09-12-2017 12:23 AM
09-11-2017 10:17 PM
DECLARE @ADObjectID varchar(50), @AccountType varchar(50);
DECLARE @AssetID varchar(50), @AssetName varchar(50);
DECLARE @UserName varchar(50), @DomainName varchar(50);
DECLARE @UserName1 varchar(50), @DomainName1 varchar(50);
DECLARE @Name varchar(50), @SAMName varchar(50), @GroupName varchar(50);
declare EnumerateGroups Cursor
for
Select distinct tblAssets.AssetID,
tblAssets.AssetName,
G.Username,
G.Domainname,
G.Groupname,
s.sAMAccountName,
u.Username,
u.Userdomain,
AG.Name,
s.ADObjectID,
(CASE s.ADObjectID WHEN (select adobjectid from tbladusers where adobjectid = s.ADObjectID ) THEN 'USER' ELSE 'GROUP' END) AS AccountType
From tblAssets
Inner Join tblUsersInGroup G
On tblAssets.AssetID = G.AssetID
Inner Join tblAssetCustom
On tblAssets.AssetID = tblAssetCustom.AssetID
inner join tbladobjects S on
s.sAMAccountName = G.Username
and
s.domain = G.Domainname
left outer join tblADusers U on
U.ADObjectID = S.ADObjectID
left outer join tblADgroups AG on
AG.ADObjectID = S.ADObjectID
left outer join tblADMembership M on
AG.ADGroupID = m.ParentAdObjectID
left outer join tblADusers on
u.ADObjectID = m.ChildAdObjectID
Where (G.Groupname = 'administrators' Or
G.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
-- tblassets.assetid = 96
Order By tblAssets.AssetName ;
open EnumerateGroups;
Fetch Next from EnumerateGroups
Into @AssetID, @AssetName, @UserName, @DomainName, @GroupName,@SAMName, @UserName1, @DomainName1, @Name, @ADObjectID, @AccountType ;
While @@FETCH_STATUS = 0
Begin
if @AccountType = 'GROUP'
select @AssetID as 'Asset ID', tbladusers.Userdomain, tbladusers.Username, @AccountType as 'Permission From', tbladgroups.Name
from tblADMembership
inner join tbladusers on childadobjectid = tbladusers.adobjectid
inner join tbladGroups on parentadobjectid = tblADGroups.ADObjectID
inner join tblAssets on tblAssets.AssetID = @AssetID
where tblADMembership.ParentAdObjectID = @ADObjectID
order by tblassets.AssetID
else
select @AssetID as 'Asset ID', tbladusers.Userdomain, tbladusers.Username, @AccountType as 'Permission From'
from tblADusers
inner join tblAssets on tblAssets.AssetID = @AssetID
where tblADusers.ADObjectID = @ADObjectID
order by tblassets.AssetID
Fetch next from EnumerateGroups
Into @AssetID, @AssetName, @UserName, @DomainName, @GroupName,@SAMName, @UserName1, @DomainName1, @Name, @ADObjectID, @AccountType ;
End
Close EnumerateGroups;
DeAllocate EnumerateGroups;
Go
09-11-2017 07:33 PM
09-11-2017 05:49 PM
09-11-2017 03:23 PM
09-11-2017 03:18 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblUsersInGroup.Username,
tblUsersInGroup.Domainname,
tblUsersInGroup.Groupname
From tblAssets
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where(tblUsersInGroup.Groupname = 'administrators' Or
tblUsersInGroup.Groupname = 'remote desktop users') And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now