SQLRU.net
Разработка приложений баз данных

Начало » Использование СУБД » Microsoft SQL Server » Выборка ролей с их свойствами из инстанса (Окажите помощь в доработке скрипта)
- Выборка ролей с их свойствами из инстанса [сообщение #5632] Tue, 29 October 2024 11:48 Переход к следующему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Как сделать выборку из инстанса (описание ролей в базах):
Список баз, роль в базе, информация из свойств роли (см.скрин) - Schema, Name, Type - и по каждой строчке выбранные Permitions (например Select - Grant и т.д.)
Grantor, Grant, With Grant, Deny


1. Первые три колонки работают по скрипту:
EXEC sp_msforeachdb 'USE [?];
--Исключаем системные базы
IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    SELECT DB_NAME() AS [DB_NAME],  RP.name AS [Role_NAME],  UP.default_schema_name AS [User_Schema] --, --sys.schemas
   FROM sys.database_role_members AS R
   INNER JOIN sys.database_principals AS UP
       ON UP.principal_id = R.member_principal_id
   INNER JOIN sys.database_principals AS RP
       ON RP.principal_id = R.role_principal_id
   ORDER BY RP.name, UP.name
'
GO



2. --Выборка table_name, table_type, TABLE_SCHEMA работает:
SELECT table_name, table_type, TABLE_SCHEMA
                FROM information_schema.table


3. --Выборку уникальных Permision вот нашёл:
select DISTINCT permission_name from sys.database_permissions

-- alter,  control, Permission, Delete,  Insert, References, Select, Update, Take owneship, Vew change tracking, Vew definition
---------------------


Как это слепить/объединить, чтобы показало всё вместе? Сопоставить Базы Роли Схемы Таблицы ТипыТаблиц Permissions

[Обновления: Tue, 29 October 2024 16:38]

Известить модератора

- Re: Выборка ролей с их свойствами из инстанса [сообщение #5636 является ответом на сообщение #5632] Tue, 29 October 2024 21:55 Переход к предыдущему сообщениюПереход к следующему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Аукцион: "Я решу эту задачу сегодня за ... руб"
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5637 является ответом на сообщение #5636] Wed, 30 October 2024 09:40 Переход к предыдущему сообщениюПереход к следующему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Можно для на начала хоть первые 2 выборки объединить?
Вывести в таблицу с колонками:
DB_NAME , Role_NAME , User_Schema , table_name , table_type , TABLE_SCHEMA
Чем объединять UNION или INNER или ...?
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5648 является ответом на сообщение #5637] Thu, 31 October 2024 11:28 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 369
Зарегистрирован: June 2022
Senior Member
Declare @T Table ([DB_NAME] nvarchar(128), [Role_NAME] nvarchar(128), [User_Schema] nvarchar(128))
Insert @T
EXEC sp_msforeachdb 'USE [?];
--Исключаем системные базы
IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    SELECT DB_NAME() AS [DB_NAME],  RP.name AS [Role_NAME],  UP.default_schema_name AS [User_Schema] --, --sys.schemas
   FROM sys.database_role_members AS R
   INNER JOIN sys.database_principals AS UP
       ON UP.principal_id = R.member_principal_id
   INNER JOIN sys.database_principals AS RP
       ON RP.principal_id = R.role_principal_id
   ORDER BY RP.name, UP.name
'

Select * from @T t
left join 
 information_schema.TABLES ist
on t.User_Schema = ist.TABLE_SCHEMA
GO
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5651 является ответом на сообщение #5648] Thu, 31 October 2024 14:12 Переход к предыдущему сообщениюПереход к следующему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Огромное спасибо!
Можно теперь сюда как то привязать Permissions?

select permission_name, T.name AS Table_name, state_desc, type_desc, U.name,  major_id from sys.database_permissions P join  sys.tables T ON P.major_id = T.object_id join  sysusers U ON U.uid = P.grantee_principal_id
-- Уникальные Permissions(разрешения):
select DISTINCT permission_name from sys.database_permissions
--Список рermission (разрешений) инстанса, имеющих права доступа к определённой таблице
select permission_name, T.name AS Table_name, state_desc, type_desc, U.name,  major_id from sys.database_permissions P join  sys.tables T ON P.major_id = T.object_id join  sysusers U ON U.uid = P.grantee_principal_id

[Обновления: Thu, 31 October 2024 14:46]

Известить модератора

- Re: Выборка ролей с их свойствами из инстанса [сообщение #5654 является ответом на сообщение #5651] Thu, 31 October 2024 14:49 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 369
Зарегистрирован: June 2022
Senior Member
У каждого объекта есть Id по нему и join.
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5662 является ответом на сообщение #5654] Fri, 01 November 2024 10:59 Переход к предыдущему сообщениюПереход к следующему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Спасибо, попробовал, пока не получилось.
Прошу помощи
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5682 является ответом на сообщение #5662] Sat, 02 November 2024 16:35 Переход к предыдущему сообщениюПереход к следующему сообщению
BlackEric в настоящее время не в онлайне  BlackEric
Сообщений: 369
Зарегистрирован: June 2022
Senior Member
Declare @Tt Table ([DB_NAME] nvarchar(128), [Role_NAME] nvarchar(128), [User_Schema] nvarchar(128), [User_Schema_Id] int)
Insert @Tt
EXEC sp_msforeachdb 'USE [?];
--Исключаем системные базы
IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
    SELECT DB_NAME() AS [DB_NAME],  UP.name AS [Role_NAME],  UP.default_schema_name AS [User_Schema], sc.schema_id
   FROM sys.database_role_members AS R
	INNER JOIN sys.database_principals AS UP
       ON UP.principal_id = R.member_principal_id
	Left JOIN sys.schemas sc
		on UP.default_schema_name = sc.[name]
   ORDER BY UP.name
'

Select tt.DB_NAME, tt.Role_NAME, tt.User_Schema, tt.User_Schema_Id, P.permission_name, T.name AS Table_name, P.state_desc, T.type_desc, U.name,  P.major_id  from @Tt tt
left join sys.TABLES st
	on tt.User_Schema_Id = st.schema_id
join sys.database_permissions P
	on p.major_id = st.object_id
join  sys.tables T 
	ON P.major_id = T.object_id 
join  sysusers U 
	ON U.uid = P.grantee_principal_id
GO
Возможно так, но я не проверял детально. Мог где-то что-то не поджойнить.
- Re: Выборка ролей с их свойствами из инстанса [сообщение #5695 является ответом на сообщение #5682] Tue, 05 November 2024 15:56 Переход к предыдущему сообщению
keepermode в настоящее время не в онлайне  keepermode
Сообщений: 23
Зарегистрирован: May 2023
Junior Member
Спасибо огромное за помощь!!!
Предыдущая тема: Состояние базы "Ожидание восстановления"
Следующая тема: Нет полных SQL-разрешений для серверной админской учётки
Переход к форуму:
  


Текущее время: Mon Dec 30 06:24:58 GMT+3 2024

Общее время, затраченное на создание страницы: 0.01330 секунд