Блог

Мне действительно нужен совет по добавлению ролей на уровне сервера и применению некоторых разрешений, которые будут применимы к каждой базе данных на моем сервере.

В принципе, мне нужны две роли: только для чтения и чтения для записи. У чтения будут разрешения на выбор и просмотр любого объекта, у записи будут разрешения на выбор / вставку / удаление и выполнение любого объекта

Я хочу создать роль сервера, затем логин, связанный с этой ролью (который может быть группой AD), и после этого для каждой базы данных создайте пользователя, который унаследует все разрешения от роли сервера.

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

Что я вижу, я не могу назначить чтение или запись роли, а затем использовать ее в каждой базе данных, напротив, на вкладке Общие роли сервера у меня есть куча разрешений, и неясно, какое из них использовать для этой цели.

Возможно, я делаю это неправильно, но я хочу иметь что-то на уровне сервера и не определять одну и ту же роль в каждой базе данных для этой цели. Я использую SQL server 2014.

Короткий ответ таков: вы не можете.

Как правило, разрешения на уровне сервера не распространяются на отдельные объекты в базах данных. Единственным исключением является sysadmin роль, которую я бы настоятельно рекомендовал вам не использовать для этой цели, поскольку вы, по сути, передадите управление всем экземпляром сервера каждому его участнику.

Для краткости вы можете использовать встроенные роли базы данных, чтобы избавить себя от лишних хлопот. Для доступа только для чтения обычно достаточно членства в db_datareader роли, если только у вас нет хранимых процедур, возвращающих наборы данных, которые, как предполагается, может выполнять эта роль. Существует также аналогичная роль для модификации, db_datawriter но она не распространяется на execute разрешения. Итак, вам нужно будет создать пользовательскую роль для этого:

 create role [DataChanger] authorization [dbo];
go
alter role [db_datareader] add member [DataChanger];
go
alter role [db_datawriter] add member [DataChanger];
go
grant execute to [DataChanger];
go

-- Now you can add your members. Here is a reader
create user [DomainMyUser1] from login [DomainMyUser1];
go
alter role [db_datareader] add member [DomainMyUser1];
go
-- Writer
create user [DomainMyUser2] from login [DomainMyUser2];
go
alter role [DataChanger] add member [DomainMyUser2];
go
  

Эти разрешения будут автоматически получать вновь созданные объекты, без необходимости явно добавлять новые разрешения после каждого изменения схемы.

Вам нужно будет сделать это в контексте каждой пользовательской базы данных, которой вы хотите управлять таким образом. Вероятно, вы можете создать задание агента SQL, которое будет периодически запускаться и вносить эти изменения в любые пользовательские базы данных, в которых их еще нет (например, если база данных была восстановлена из предыдущей резервной копии, или перенесена с другого сервера, или была создана новая). Кроме того, поскольку вы не можете перебирать базы данных в статическом коде, вам нужно будет перенести его в динамический SQL и выполнить цикл sys.databases или, возможно, с помощью недокументированной sp_MSforeachdb системной хранимой процедуры. О, и не забудьте удалить все эти go инструкции из динамического кода, поскольку они не являются частью SQL и распознаются только SSMS и sqlcmd .

P.S. При всем сказанном, я надеюсь, вы не собираетесь управлять никакими производственными базами данных таким образом. Я даже не знаю, с чего начать, насколько небезопасен этот подход.