Как написать SQL-запрос, чтобы узнать, какие логины и какие права были предоставлены в Sql Server 2005?
Я отвечаю за некоторые тестовые серверы баз данных. Исторически сложилось так, что слишком многие люди имеют к ним доступ. Они работают на SQL Server 2005.
Я писал запросы и упаковывал их в скрипты, чтобы я мог проводить регулярный аудит прав. Выяснить, какие пользователи имеют права администратора на самом сервере, было хорошо, как и выяснить, у кого была роль sysadmin при входе в систему - для последнего это был однострочный запрос.
Но как узнать, какие логины имеют сопоставление пользователей с конкретной (или любой) базой данных?
Я могу найти таблицы sys.database_principals и sys.server_principals. Я обнаружил sys.databases table. Я не придумал, как узнать, какие пользователи имеют права на базу данных, и если да, то какие.
Каждый поиск Google вызывает людей вручную с помощью панели User Mapping диалогового окна Login, а не с помощью запроса. Есть идеи?
Ответы 3
select * from Master.dbo.syslogins l inner join sys.sysusers u on l.sid = u.sid
Это даст вам представление о том, какие пользователи привязаны к каким логинам в одной базе данных.
Ознакомьтесь с этой справочной статьей msdn о Has_Perms_By_Name. Думаю, вам действительно интересны примеры D, F и G
Еще одна идея ... Я запустил профилировщик SQL и щелкнул ObjectExplorer-> Security-> Users. В результате (приблизительно) был выдан следующий запрос.
SELECT *
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp
ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE (u.type in ('U', 'S', 'G', 'C', 'K'))
ORDER BY [Name] ASC
+1 Спасибо, Дэвид отлично поработал, изменил Select * на Name, и он делает все, что мне нужно: D
Вот как это сделать. В итоге я нашел ссылку на sproc в документах MSDN. Я вытащил это из sproc и заключил в цикл всех баз данных, известных этому экземпляру.
select DbRole = g.name, MemberName = u.name
from @NAME.sys.database_principals u, @NAME.sys.database_principals g, @NAME.sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
and g.name in (''db_ddladmin'', ''db_owner'', ''db_securityadmin'')
and u.name not in (''dbo'')
order by 1, 2
Затем это сообщает о пользователях, у которых есть DBO, которым, возможно, не следует. Я уже отозвал некоторые права администратора у некоторых пользователей, которые им не нужны. Спасибо всем!
Дэвид, я думаю, ты в деньгах. Мне нужно будет проверить это завтра, когда я пойду на работу. Обновлю этот вопрос. Большое спасибо!