J3qx

information archive

logins and roles list serves sql

Posted by j3qx на Декабрь 10, 2016

SET NOCOUNT ON;
DECLARE @SqlServers varchar(MAX) = ‘(local);PD-DB-41;PD-DB-39;PD-DB-32’;
DECLARE @SqlServer varchar(MAX), @SqlConnStr varchar(MAX);
DECLARE @Cursor CURSOR;
DECLARE @sql nvarchar(MAX);
DECLARE @AhdqValueOnStart int = 0;
SELECT @AhdqValueOnStart = CONVERT(int, value) FROM SYS.CONFIGURATIONS WHERE Name = ‘Ad Hoc Distributed Queries’

IF @AhdqValueOnStart = 0
BEGIN
— * Enabled AHDQ
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC master.dbo.sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
EXEC master.dbo.sp_configure ‘show advanced options’, 0;
RECONFIGURE;
END

SET @Cursor = CURSOR FOR
WITH SqlServers (StartPosition, EndPosition, [ServerName]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(‘;’, @SqlServers, 1), 0), LEN(@SqlServers) + 1) AS EndPosition,
SUBSTRING(@SqlServers, 1, ISNULL(NULLIF(CHARINDEX(‘;’, @SqlServers, 1), 0), LEN(@SqlServers) + 1) — 1) AS [ServerName]
WHERE @SqlServers IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(‘;’, @SqlServers, EndPosition + 1), 0), LEN(@SqlServers) + 1) AS EndPosition,
SUBSTRING(@SqlServers, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(‘;’, @SqlServers, EndPosition + 1), 0), LEN(@SqlServers) + 1) — EndPosition — 1) AS [ServerName]
FROM SqlServers
WHERE EndPosition < LEN(@SqlServers) + 1
)
SELECT [ServerName]
FROM [SqlServers]
WHERE StartPosition < EndPosition
OPTION (MAXRECURSION 0);

OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @SqlServer

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @sql = ‘DECLARE @UserName nvarchar(MAX); SELECT @UserName = [UserName] FROM OPENROWSET(»SQLNCLI», »Server=’ + @SqlServer + ‘;Trusted_Connection=yes;»,»SELECT suser_name() AS [UserName]»)’;
EXEC sp_executeSQL @sql;
PRINT ‘Successfully connected to »’ + @SqlServer + »»;
END TRY
BEGIN CATCH
DECLARE @ErrorMsg varchar(MAX) = ‘Server »’ + @SqlServer + »’ cannot be reached (‘ + ERROR_MESSAGE() + ‘)’;
RAISERROR (@ErrorMsg, 1, 1) WITH NOWAIT
CLOSE @Cursor
DEALLOCATE @Cursor
GOTO TheEnd
END CATCH
FETCH NEXT FROM @Cursor
INTO @SqlServer
END

CLOSE @Cursor

— Construct SQL query to get permissions from all servers
SET @sql = »;
OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @SqlServer

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ‘SELECT * FROM OPENROWSET(»SQLNCLI», »Server=’ + @SqlServer + ‘;Trusted_Connection=yes;»,
»SELECT »»’ + @SqlServer + »»’ AS Server
,sl.name AS Login
,ISNULL(DB_Roles.Roles,»»Public»») AS Role
,CASE pr.is_disabled
WHEN 1 THEN »»Disabled»»
ELSE »»»» END
FROM sys.syslogins sl
LEFT JOIN
(
SELECT »»sysadmin»» as Roles
UNION ALL
SELECT »»securityadmin»»
UNION ALL
SELECT »»serveradmin»»
UNION ALL
SELECT »»setupadmin»»
UNION ALL
SELECT »»processadmin»»
UNION ALL
SELECT »»diskadmin»»
UNION ALL
SELECT »»dbcreator»»
UNION ALL
SELECT »»bulkadmin»»
UNION ALL
SELECT »»No serverRole»»
UNION ALL
SELECT »»public»»
) DB_Roles
ON
CASE
WHEN sl.sysadmin=1 AND DB_Roles.Roles=»»sysadmin»» THEN »»sysadmin»»
WHEN sl.securityadmin=1 AND DB_Roles.Roles=»»securityadmin»» THEN »»securityadmin»»
WHEN sl.serveradmin=1 AND DB_Roles.Roles=»»serveradmin»» THEN »»serveradmin»»
WHEN sl.setupadmin=1 AND DB_Roles.Roles=»»setupadmin»» THEN »»setupadmin»»
WHEN sl.processadmin=1 AND DB_Roles.Roles=»»processadmin»» THEN »»processadmin»»
WHEN sl.diskadmin=1 AND DB_Roles.Roles=»»diskadmin»» THEN »»diskadmin»»
WHEN sl.dbcreator=1 AND DB_Roles.Roles=»»dbcreator»» THEN »»dbcreator»»
WHEN sl.bulkadmin=1 AND DB_Roles.Roles=»»bulkadmin»» THEN »»bulkadmin»»
ELSE NULL
END = DB_Roles.Roles
LEFT JOIN sys.server_principals pr ON sl.loginname = pr.name» ) ‘;
FETCH NEXT FROM @Cursor INTO @SqlServer
IF @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ‘ UNION ‘;
END
ELSE
BEGIN
SET @sql = @sql + ‘ ORDER BY 1 ASC, 2 ASC ‘;
END
END
CLOSE @Cursor
DEALLOCATE @Cursor

EXEC sp_executeSQL @sql;

TheEnd:
IF @AhdqValueOnStart = 0
BEGIN
— * Disable AHDQ
EXEC master.dbo.sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC master.dbo.sp_configure ‘Ad Hoc Distributed Queries’, 0;
RECONFIGURE;
EXEC master.dbo.sp_configure ‘show advanced options’, 0;
RECONFIGURE;
END

Реклама

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

 
%d такие блоггеры, как: