First of all, this system stored procedure takes two input parameters. The first one is the windows group name, and the second one is the value ‘members’. For instance, it lists the windows users that access via the windows group ' MyDomain\SQLProdUsrs'. (You need to have “sysadmin” role to execute it.)
EXEC xp_logininfo @acctname ='MyDomain\SQLPrdUsrs', @option='members'
SET NOCOUNT ON
CREATE TABLE #WindowGroup(
server_name varchar(100),
account_name varchar(300),
type char(8),
privilege char(9) ,
mapped_login_name varchar(300) ,
permission_pathsysname varchar(300)
)
DECLARE @WindowGroupName varchar(max)
DECLARE @db [NCHAR](128)
DECLARE cursor_WG CURSOR FOR SELECT [name] FROM sys.server_principals WHERE TYPE='G'
OPEN cursor_WG
FETCH NEXT FROM cursor_WG INTO @WindowGroupName
WHILE @@FETCH_STATUS= 0
BEGIN
INSERT #WindowGroup(account_name , type , privilege ,mapped_login_name ,permission_pathsysname )
EXEC xp_logininfo @acctname =@WindowGroupName , @option = 'members'
FETCH NEXT FROM cursor_WG INTO @WindowGroupName
END
CLOSE cursor_WG
DEALLOCATE cursor_WG
UPDATE #WindowGroup SET server_name=@@servername
select server_name AS ServerName, account_name as WindowsAccountName, Type, Privilege, mapped_login_name as MappedLoginName, permission_pathsysname as WindowsGroupName
FROM #WindowGroup
ORDER BY permission_pathsysname,mapped_login_name
DROP TABLE #WindowGroup
SET NOCOUNT OFF
As you have seen, the T-SQL code is simply easy to understand. Clearly, it filters the windows groups by indicating the value ‘G’ for the Type column of the system view “sys.server_principals”, and eventually through the cursor each of them is read. Now I hope you make the most out of this script to have better visibility of the windows users accessing the databases. Thanks for reading again!
No comments:
Post a Comment
Let me know any remarks or questions you may have. Please write down your name.