-- This script is making an XML file showing all security setting at a SQL Server
-- specifying which users are member of which Server/Database roles
-- Perhaps I am modifying the script to show which objects user defined roles is holding
-- Created by SeoSoft ApS - Søren Eggert Lundsteen Olsen
-- Version 1.1.0
-- Now possible to write members of Domain Groups
DECLARE @Sql NVARCHAR(MAX) SET @Sql = ''
DECLARE @RoleName sysname SET @RoleName =''
DECLARE @IsNTGroup int SET @IsNTGroup = 0
DECLARE @UserType sysname SET @UserType =''
DECLARE @UserName sysname SET @UserName =''
DECLARE @DatabaseName sysname SET @DatabaseName =''
DECLARE @Empty INT SET @Empty = 0
DECLARE @vi INT
DECLARE @account_name sysname SET @account_name = ''
DECLARE @GroupMembers TABLE
(
[account_name] sysname,
[type] sysname,
[privilege] sysname,
[mapped_login_name] sysname,
[permission_path] sysname
)
DECLARE curGroupMembers CURSOR LOCAL FOR
SELECT [account_name]
FROM @GroupMembers
DECLARE curRoles CURSOR LOCAL FOR
SELECT [name] AS RoleName
FROM [master].[sys].[server_principals]
WHERE type = 'R'
DECLARE curDatabaseRoles CURSOR LOCAL FOR
SELECT [name]
FROM [sys].[database_principals]
WHERE type = 'R'
DECLARE curUsers CURSOR LOCAL FOR
SELECT [name], [TYPE]
FROM [master].[sys].[server_principals]
WHERE [TYPE] IN ('U', 'G')
DECLARE curDatabases CURSOR LOCAL FOR
SELECT [name]
FROM [master].[sys].[databases]
DECLARE curDatabaseUsers CURSOR LOCAL FOR
SELECT [name] AS UserName, [isntgroup] AS IsNTGroup
FROM [sys].[sysUsers]
WHERE [isLogin] = 1
SET NOCOUNT ON
OPEN curRoles
FETCH NEXT FROM curRoles INTO @RoleName
IF @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT ''
SET @Empty = 1
END
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
PRINT ' SERVER'
PRINT ' SERVER ROLE'
PRINT ' ' + @RoleName + ''
OPEN curUsers
FETCH NEXT FROM curUsers INTO @UserName, @UserType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @UserType = 'U'
BEGIN
SET @Sql = ' SELECT @RoleMember = IS_SRVROLEMEMBER(''' + @RoleName + ''',''' + @UserName + ''');'
BEGIN TRY
EXEC SP_EXECUTESQL
@Query = @Sql
, @Params = N'@RoleMember INT OUTPUT'
, @RoleMember = @vi OUTPUT
IF @vi > 0
BEGIN
PRINT ' ' + @UserName + ''
END
END TRY
BEGIN CATCH
PRINT ' IS_SRVROLEMEMBER FAILED' + @UserName + ''
END CATCH
END
ELSE
BEGIN
SET @Sql = ' SELECT @RoleMember = IS_ROLEMEMBER(''' + @RoleName + ''',''' + @UserName + ''');'
BEGIN TRY
EXEC SP_EXECUTESQL
@Query = @Sql
, @Params = N'@RoleMember INT OUTPUT'
, @RoleMember = @vi OUTPUT
END TRY
BEGIN CATCH
PRINT ' IS_ROLEMEMBER FAILED' + @RoleName + ':' + @UserName + ''
SET @vi = 0
END CATCH
IF @vi > 0
BEGIN
DELETE FROM @GroupMembers
BEGIN TRY
INSERT INTO @GroupMembers
EXEC MASTER.sys.xp_logininfo
@acctname= @UserName,
@option='members'
END TRY
BEGIN CATCH
PRINT ' xp_logininfo FAILED' + @UserName + ''
END CATCH
OPEN curGroupMembers
FETCH curGroupMembers INTO @account_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ' + @account_name + '' + @UserName + ''
FETCH curGroupMembers INTO @account_name
END
CLOSE curGroupMembers
END
END
FETCH NEXT FROM curUsers INTO @UserName, @UserType
END
CLOSE curUsers
FETCH NEXT FROM curRoles INTO @RoleName
PRINT ' '
END
OPEN curDatabases
FETCH NEXT FROM curDatabases INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Sql = 'USE ' + QUOTENAME(@DatabaseName);
EXEC SP_EXECUTESQL @Sql
OPEN curDatabaseRoles
FETCH NEXT FROM curDatabaseRoles INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
PRINT ' ' + @DatabaseName + ''
PRINT ' DATABASE ROLE'
PRINT ' ' + @RoleName + ''
OPEN curDatabaseUsers
FETCH NEXT FROM curDatabaseUsers INTO @UserName, @IsNTGroup
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsNTGroup = 0
BEGIN
SET @Sql = ' SELECT @RoleMember = IS_ROLEMEMBER(''' + @RoleName + ''',''' + @UserName + ''');'
BEGIN TRY
EXEC SP_EXECUTESQL
@Query = @Sql
, @Params = N'@RoleMember INT OUTPUT'
, @RoleMember = @vi OUTPUT
IF @vi = 1
BEGIN
PRINT ' ' + @UserName + ''
END
END TRY
BEGIN CATCH
PRINT ' IS_ROLEMEMBER FAILED' + @UserName + ''
END CATCH
END
ELSE
BEGIN
SET @Sql = ' SELECT @RoleMember = IS_ROLEMEMBER(''' + @RoleName + ''',''' + @UserName + ''');'
BEGIN TRY
EXEC SP_EXECUTESQL
@Query = @Sql
, @Params = N'@RoleMember INT OUTPUT'
, @RoleMember = @vi OUTPUT
END TRY
BEGIN CATCH
PRINT ' IS_ROLEMEMBER FAILED' + @RoleName + ':' + @UserName + ''
SET @vi = 0
END CATCH
IF @vi > 0
BEGIN
DELETE FROM @GroupMembers
BEGIN TRY
INSERT INTO @GroupMembers
EXEC MASTER.sys.xp_logininfo
@acctname= @UserName,
@option='members'
END TRY
BEGIN CATCH
PRINT ' xp_logininfo FAILED' + @UserName + ''
END CATCH
OPEN curGroupMembers
FETCH curGroupMembers INTO @account_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' ' + @account_name + '' + @UserName + ''
FETCH curGroupMembers INTO @account_name
END
CLOSE curGroupMembers
END
END
FETCH NEXT FROM curDatabaseUsers INTO @UserName, @IsNTGroup
END
CLOSE curDatabaseUsers
FETCH NEXT FROM curDatabaseRoles INTO @RoleName
PRINT ' '
END
CLOSE curDatabaseRoles
FETCH NEXT FROM curDatabases INTO @DatabaseName
END
DEALLOCATE curDatabaseUsers
DEALLOCATE curDatabaseRoles
CLOSE curDatabases
DEALLOCATE curDatabases
IF @Empty = 1
BEGIN
PRINT ''
END
DEALLOCATE curUsers
CLOSE curRoles
DEALLOCATE curRoles
DEALLOCATE curGroupMembers
