Как получить список пользователей для баз данных всего экземпляра
Я предполагаю, что процедура должна быть чем-то вроде этого:
declare @db varchar(100)
declare @user varchar(100)
declare c cursor for select name from sys.sysdatabases
open c
fetch next from c into @db
while @@fetch_status = 0
begin
print @db
exec ('use ' + @db)
declare u cursor for select name from sys.sysusers
where issqlrole <> 1 and hasdbaccess <> 0 and isntname <> 1
open u
fetch next from u into @user
while @@fetch_status = 0
begin
print @user
fetch next from u into @user
end
print '--------------------------------------------------'
close u
deallocate u
fetch next from c into @db
end
close c
deallocate c
Но проблема состоит в том, что должностное лицо ('использование' + @db) не работает. И я всегда получаю список пользователей в настоящее время выбираемой базы данных. Как я должен зафиксировать это?
P.S.: Я хочу, чтобы этот код работал и над 2 000 и над 2 005 SQL-серверами.
2 ответа
Вы также можете использовать недокументированную, но хорошо используемую sp_MSforeachdb хранимую процедуру - см. здесь или другую запись в блоге здесь :
exec sp_MSforeachdb 'select * from ?.sys.sysusers'
Знак "?" - это имя-заполнитель для имени базы данных, которое будет добавлено к команде по мере ее выполнения для каждой базы данных в вашей системе.
Вот хороший запрос с http://www.sqlservercentral.com/scripts/Administration/63841/ Если вы не есть учетная запись, это бесплатная регистрация и очень хороший ресурс.
Помещает все в временную таблицу, после чего вы можете делать с ней все, что захотите.
USE MASTER
GO
BEGIN
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser
(
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85)
)
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'
ELSE IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName, [name], GroupName
DROP TABLE #TUser
END