Создание сценариев для членства в роли базы данных в SQL Server 2005

У меня есть база данных с большим количеством пользователей. Эти пользователи принадлежат к различным встроенным ролям в БД (например, db_ddladmin).

Я хочу сгенерировать script, который создает те же самые пользователи с теми же назначением роли, которые будут использоваться в другой базе данных. SQL Management Studio, похоже, генерирует только вызовы sp_addrolemember для пользовательских ролей, а не встроенных. Есть ли способ сделать это script для всех ролей?

Возможно, есть и другой, лучший инструмент для генерации сценариев базы данных из существующей базы данных (желательно, но необязательно, бесплатно)?

Ответ 1

Информация о пользователях базы данных и назначенных им ролях предоставляется в виде системных представлений sys.database_principals и sys.database_role_members. Просмотрите эти данные с помощью этих запросов:

select * from sys.database_principals
select * from sys.database_role_members

Предполагаю, что у вас есть пользователи баз данных и роли, настроенные в базе данных A, и вы хотите, чтобы они были скопированы в базу данных B. Чтобы создать пользователей в целевой базе данных:

Запустите следующий запрос в базе данных A
Вырезать, вставить, ОБЗОР и запустить полученный script в базе данных B

.

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'
 from sys.database_principals
 where Type = 'U'
  and name <> 'dbo'

Чтобы настроить новых пользователей в B с теми же функциями, что и в A:

Запустите следующий запрос в базе данных A
Вырезать, вставить, ОБЗОР и запустить полученный script в базе данных B

.

SELECT 'EXECUTE sp_addrolemember ''' + roles.name + ''', ''' + users.name + ''''
 from sys.database_principals users
  inner join sys.database_role_members link
   on link.member_principal_id = users.principal_id
  inner join sys.database_principals roles
   on roles.principal_id = link.role_principal_id

Всегда просматривайте эти сценарии. Могут быть исключения или особые случаи, и вы просто не хотите испортить безопасность.

Если новая база данных находится в другом экземпляре SQL Server, вам сначала необходимо создать SQL-логины. Если у вас есть определенные пользователем роли, вам сначала нужно их воссоздать. (Роли и права, назначенные им, очень открытые, и я никогда не хочу быть в ситуации, когда мне нужно это делать!)

Ответ 2

Здесь один вариант из Idera: http://www.idera.com/Products/Free-Tools/SQL-permissions/ Он генерирует логины и разрешения и может помочь вам выполнить то, что вы пытаетесь сделать.

Ответ 3

Для ролей пользователей БД

SELECT 'CREATE ROLE [' + name + ']' 
  FROM sys.database_principals 
  where type='R' and is_fixed_role = 0 and name != 'public'

Ответ 4

Настройка исходного ответа для использования нового синтаксиса ALTER ROLE:

SELECT 'ALTER ROLE  [' + roles.name + '] ADD MEMBER [' + users.name + '];'
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
where users.name = 'MyUser'

Это зависит от базы данных - запустите ее в базе данных, для которой вы хотите извлечь роли