Getting logins account information from the server who are having sysadmin Privilege
/******
Getting logins account information from the server who are having sysadmin Privilege.
But we can use this for other servers also if need.
Script Date: 10/4/2017
Author: Ramesh. M
******/
SET NOCOUNT ON
USE master
go
DECLARE @Table TABLE
(ServerRole VARCHAR(50),[Member] VARCHAR(100),MemberSID VARCHAR(1000))
DECLARE @Table1 TABLE
(Sno INT IDENTITY(1,1),Name VARCHAR(2000))
DECLARE @CaptureLoop TABLE
(accountname varchar(200),types varchar(200),privelege varchar(100),
MappedLogin varchar(100),PermissionPath VARCHAR(100))
INSERT INTO @Table1
(Name)
SELECT NAME FROM sys.server_principals
WHERE type IN('g');
DECLARE @i int
declare @Count INT
declare @Catchvalue VARCHAR(50)
SET @i=0
SELECT @Count=COUNT(*) FROM sys.server_principals
WHERE type IN('g')
--PRINT @Count
INSERT INTO @Table
(ServerRole,[Member],MemberSID)
exec sp_helpsrvrolemember 'sysadmin'
WHILE @I<@Count
BEGIN
set @i=@i+1
SELECT @Catchvalue=Name
FROM @Table1
WHERE Sno=@i
--SELECT @Catchvalue
INSERT INTO @CaptureLoop
EXEC xp_logininfo @Catchvalue,'members'
END
select Member,[GroupOrIndiviudal]='Sysadmin/Individual' from @Table
union
select accountname,PermissionPath from @CaptureLoop
order by [GroupOrIndiviudal]
Comments