¿Cuál sería el permiso correcto para permitir todo lo demás excepto sobrescribir o crear una base de datos?

Solución:

TL; DR

Para evitar que los DEV hagan algo “incorrecto”, al menos debe eliminar el sysadmin Rol de SQL Server.

Empecemos

Puede asignar a sus DEV db_owner función de base de datos para cada base de datos individual. Sin embargo, esa función permitirá a un usuario realizar una BACKUP DATABASE ... o BACKUP LOG ... que mencionaste que no quieres que puedan realizar.

Soluciones

A partir de SQL Server 2014, puede restringir un inicio de sesión de SQL Server con el db_owner rol eliminando aún más los privilegios / permisos individuales. Un ejemplo es eliminar el privilegio de copia de seguridad en la base de datos y el nivel de registro de transacciones emitiendo los siguientes comandos en la base de datos:

USE 
GO
DENY BACKUP DATABASE TO 
GO
DENY BACKUP LOG TO 

Referencia: Permisos de base de datos DENY (Transact-SQL) (Microsoft Docs)

Hay tres niveles de permisos.

GRANT  : You are granted/granting a specific permission  
REVOKE : A previously granted/denied permission is revoked (removed) again  
DENY   : You are denied/denying a specific permission  

Alternativa

Ahora porque DENY tiene precedencia sobre GRANT podría utilizar la solución alternativa de crear un nuevo rol de base de datos y agregar el a ese rol:

USE 
GO
CREATE ROLE [deny_backup]
GO
DENY BACKUP DATABASE TO [deny_backup]
GO
DENY BACKUP LOG TO [deny_backup]
GO
EXEC sp_addrolemember N'deny_backup', N''
GO

Referencia: Restringir el permiso de copia de seguridad al usuario (Social MSDN)

Guía paso por paso

Debido a que algunas personas se muestran escépticas acerca de las declaraciones que he hecho en esta publicación, agrego los pasos individuales para mostrarle cómo puede NEGAR permisos de un inicio de sesión de SQL a pesar de que ese inicio de sesión tiene la db_owner papel.

1. Creación y asignación de inicio de sesión SQL

   Creemos un inicio de sesión SQL y asígnele el db_owner rol de la base de datos:

   

2. Denegar los permisos de la base de datos de respaldo

   Ahora cambiamos a una cuenta con mayores privilegios como sa o un inicio de sesión de Windows (que sería usted, el DBA que hace esta pregunta) que tiene la sysadmin Rol de SQL Server y DENY el derecho a backup database desde el principal de la base de datos hot2use:

   

   En un mundo perfecto, también necesitaría enviar un DENY BACKUP LOG TO hot2use también.

3. Inicie sesión en el servidor con hot2use y haga una copia de seguridad de la base de datos

   Viendo como hot2use tiene el db_owner rol de base de datos para el Test base de datos, iniciamos sesión con hot2use…

   

   … e intente hacer una copia de seguridad de la base de datos:

   

   Bueno, eso no funciona y es lo que esperábamos. Esto se debe a que el inicio de sesión de SQL Server hot2use ya no tiene el privilegio de hacer una copia de seguridad de la base de datos aunque el inicio de sesión todavía tiene el rol de base de datos db_owner:

4. SQL Login quiere piratear permisos

   Dado que el inicio de sesión de SQL Server tiene la db_owner rol y, según algunas personas, puede asignarse los permisos a sí mismo, probemos eso:

   

   (La prueba se realizó en una nueva ventana de consulta de SQL Server de la base de datos)

5. Resumen

   Si tu DENY permisos con una cuenta con mayores privilegios, estos privilegios no se pueden GRANTed de la cuenta con menos privilegios, incluso aunque el inicio de sesión de SQL (p. ej. hot2use) debe tener estos privilegios del rol de base de datos db_owner. Esto es algo que cambió en SQL Server 2014 (sí, las pruebas se realizaron en una instancia de SQL Server 2014).

Recursos adicionales

Puede restringir aún más los permisos de acuerdo con la descripción general oficial que se puede encontrar aquí:

Permisos (motor de base de datos)

Más lecturas recomendadas:

Introducción a los permisos del motor de base de datos

Espera, restaura …

Oh si, solo negamos el BACKUP DATABASE privilegio ¿no? Eso no tiene nada que ver con RESTORE DATABASE, o no? Vamos a averiguar. Crearé una copia de seguridad de la base de datos con una cuenta / inicio de sesión con más privilegios:

Parece funcionar. Volvamos al hot2use Ventana de consulta de SQL Server y restaure la copia de seguridad:

Como puede ver, no va a funcionar, porque el BACKUP DATABASE El privilegio también restringe la capacidad de restauración de ese inicio de sesión de SQL Server.

Sin privilegios de copia de seguridad = Sin privilegios de restauración.

Qué tal si DENY BACKUP DATABASE FROM SYSADMIN?

Bueno, ya que preguntaste. Probémoslo. Dejaré de lado las capturas de pantalla que muestran cómo revoqué el rol de base de datos.

Asigné al inicio de sesión SQL el sysadmin Rol de SQL Server con:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [hot2use]
GO

… y luego eliminó el permiso para hacer una copia de seguridad de la base de datos con lo mencionado anteriormente DENY BACKUP DATABASE.. comandos y luego realizó una copia de seguridad de la base de datos con:

backup database Test to disk = 'C:tempTest_Full_Backup_20171121.bak'

… que resultó en:

Entonces no puedes DENY permisos de un inicio de sesión SQL con el sysadmin Función de servidor, pero puede DENY permisos si el inicio de sesión SQL no tiene el rol de base de datos ‘sysadmin’.

Permisos mínimos necesarios para hacer una copia de seguridad / restaurar una base de datos

Si asigno el inicio de sesión de SQL Server absolutamente ningún rol de SQL Server y asigno solo el db_backupoperator función de la base de datos en una base de datos determinada, luego puedo hacer una copia de seguridad y restaurar la base de datos. Esto es algo que otros creen que el inicio de sesión SQL no puede realizar. La cuenta con más privilegios creó la copia de seguridad, por lo que la cuenta con menos privilegios (que no es administrador de sistemas) no debería poder hacer una copia de seguridad / restauración.

Después de restablecer todos los permisos del inicio de sesión de SQL Server hot2use para public en el servidor y db_backupoperator en el nivel de la base de datos, se me presentan los siguientes permisos enumerados:

Según otros, no debería poder realizar una copia de seguridad porque me falta una función de SQL Server.

Entonces, de hecho, puedo hacer una copia de seguridad de una base de datos si tengo el db_backupoperator papel.

Y de nuevo, si yo DENY BACKUP DATABASE al inicio de sesión de SQL Server (en una instancia de SQL Server 2014 o superior), entonces ya no puedo hacer una copia de seguridad de mi base de datos, aunque todavía tengo el db_backupoperator rol en el nivel de la base de datos:

Si el inicio de sesión de SQL hot2use intenta REVOCAR el permiso previamente denegado, entonces eso fallará, con un mensaje similar al que mostré anteriormente.

Resumen

Determinar qué permisos / privilegios / roles desea asignar a sus DEV será una pelea / problema agotador, porque no querrán renunciar a la sysadmin Rol de SQL Server. Tendrás que averiguar cuál Roles de SQL Server y cual Roles de base de datos exactamente requerirán hacer su trabajo. El peor de los casos es que todo seguirá igual. El mejor caso es que podrá restringir los DEV en sus permisos / privilegios.

Tendrá al menos que quitar el sysadmin El rol de SQL Server de sus DEV para restringir aún más que puedan hacer todo lo que quieran. Pero una vez que se ha eliminado esa función de SQL Server, puede asignarles, por ejemplo, el db_owner papel y DENY BACKUP DATABASE ... para no permitirles realizar copias de seguridad de la base de datos o restaurarla.

Los desarrolladores aún pueden hacer lo que quieran dentro de la base de datos (incluido un DROP DATABASE), pero no pueden realizar una copia de seguridad ni una restauración. Consulte los siguientes permisos enumerados para el inicio de sesión de SQL Server hot2use con el rol de base de datos db_owner pero teniendo ambos DENY BACKUP DATABASE ... y DENY BACKUP LOG ...:

Inicio de sesión de SQL Server con función db_owner pero sin permisos de copia de seguridad

Para poder responder a su pregunta necesitamos saber qué permisos exactamente a nivel de servidor necesitan sus desarrolladores.

Te acercas por el lado equivocado: les diste sysadmin rol de nivel de servidor y tratar de limitar sysadmin, pero no es posible (hubo algunos cambios en las últimas versiones, pero se refieren al acceso a los DATOS, no a los privilegios administrativos, ahora puede ocultar sus datos al administrador del sistema, pero no para limitar su poder en las tareas administrativas).

Así que en lugar de intentar DENY Algo para sysadmin (eso es imposible) debería pensar qué permisos debería GRANT.

Tus desarrolladores no deben ser miembros de sysadmin, de lo contrario no es posible NEGARles algo.

Haga una lista de lo que sus desarrolladores deberían poder hacer y actualice su pregunta. Tal vez se pueda lograr mediante la creación de server role y otorgándole solo los permisos necesarios, o tal vez se puede hacer usando stored procedures firmado con certificate.

Lo más difícil de lograr sin derechos de administrador de sistemas es este:

Todavía deberían poder crear y ejecutar trabajos

Tan pronto como elimine sus inicios de sesión de sysadmin papel, SQL Server Agent desaparecerá para ellos de SSMS.

Para poder utilizar el agente de SQL Server, el servidor principal debe ser al menos miembro de SQLAgentUserRole, para poder ejecutar trabajos – miembro de SQLAgentOperatorRole, puede leer más sobre esto aquí: Roles fijos de base de datos del Agente SQL Server. Pero incluso SQLAgentOperatorRole todavía es limitado en comparación con sysadmin, lea el artículo y decida si esos permisos son suficientes para sus desarrolladores, si no, SQL Server Agent el uso requerirá un sysadmin afiliación.

ACERCA DE RESTAURAR PERMISOS

Si la base de datos que se está restaurando no existe, el usuario debe tener
CREATE DATABASE permisos para poder ejecutar RESTORE. Si la base de datos existe, RESTORE los permisos predeterminados para los miembros de la
sysadmin y dbcreator roles de servidor fijos y el propietario (dbo) de la base de datos (para la opción FROM DATABASE_SNAPSHOT, la base de datos siempre existe).

Los permisos RESTORE se otorgan a roles en los que la información de membresía siempre está disponible para el servidor. Debido a que la pertenencia al rol fijo de la base de datos se puede verificar solo cuando la base de datos es accesible y no está dañada, lo que no siempre es el caso cuando se ejecuta RESTORE,
los miembros del rol fijo de base de datos db_owner no tienen permisos RESTORE.

Esta es una cita de la documentación oficial de MS que puede leer aquí: RESTORE Statements (Transact-SQL) en PERMISOS.

Dice claramente que alguien NO PODERrestore/create database sin SERVER LEVEL PERMISSIONS.

Esto significa que para resolver su problema no necesita negar nada a nivel de base de datos, no le ayudará en la restricción de RESTORE capacidad.

Quien no tiene server permissions (membresía en dbcreator o sysadmin roles de servidor), ya no puede restore/create database.

Quien tiene estos permisos, no puede perderlos si deny alguna database level permission.

---

Ahora, el otro respondedor ha hecho una afirmación que creo que debe abordarse:

“Sin privilegios de copia de seguridad = Sin privilegios de restauración”

Investigámoslo. Aquí hay una reproducción sencilla.

Como se indica en el artículo de BOL, para poder RESTAURAR una base de datos, uno debe ser miembro de dbcreator o sysadminroles de servidor. Entonces, si uno que es solo db_owner (y ciertamente TIENE backup permiso) NO PUEDE restaurar la base de datos.

Creemos una nueva base de datos, hagamos una copia de seguridad de ella, creemos un inicio de sesión que se asignará a esa base de datos y se agregará a la función de base de datos db_owner, usaremos imágenes que convencen más que el código.

use master;
GO

create database test;
GO

create login hot2use with password = '*****';
GO


use test;
create user hot2use from login hot2use;
exec sp_addrolemember 'db_owner', 'hot2use';
go


execute as login = 'hot2use';
select is_member('db_owner') as is_db_owner;
select HAS_PERMS_BY_NAME('test', 'database', 'backup database') as has_backup_database;
select HAS_PERMS_BY_NAME('test', 'database', 'backup log') as has_backup_log;

Las 3 últimas consultas devuelven 1 como hot2use es db_owner, tiene backup database y backup log permisos.

Ahora comprobamos si hot2use es sysadmin o dbcreator miembro de la función del servidor (que pueden restore):

select IS_SRVROLEMEMBER('sysadmin') as is_sysadmin;
select IS_SRVROLEMEMBER('dbcreator') as is_dbcreator;

Este código devuelve 0 en ambos casos:

Ahora cierre todas las conexiones a test base de datos y hacer una copia de seguridad de ella:

backup database test to disk = 'Z:TEMPtest_full.bak';
go

Ahora nuestro hot2use intenta restaurar test base de datos:

restore database test from disk = 'Z:TEMPtest_full.bak' with replace;

Guau.

Msg 3110, nivel 14, estado 1, línea 1 El usuario no tiene permiso para RESTAURAR la ‘prueba’ de la base de datos.

---

¿Cómo puede ser que nuestro hot2use tiene ambos backup database y backup log permisos y no puede restore?

Es simple. Backup se necesita permiso para backup, ser capaz de restore, vuelva a leer el artículo de BOL y vea QUIÉN ha RESTORE permiso que es necesario para poder RESTORE:

Entonces, para poder RESTAURAR, uno debe ser miembro de sysadmin o dbcreatorroles de nivel de servidor.

Arreglemoslo y pongamos nuestro login hot2use en dbcreator papel:

exec sp_addsrvrolemember 'hot2use', 'dbcreator';

Ahora nuestro hot2use es capaz de restore:

Finalmente, ahora DENYbackup database y backup log permiso para hot2use y volverá a intentar restore:

use test;
deny backup database to hot2use;
deny backup log to hot2use;
GO

execute as login = 'hot2use';

select is_member('db_owner') as is_db_owner;
select HAS_PERMS_BY_NAME('test', 'database', 'backup database') as has_backup_database;
select HAS_PERMS_BY_NAME('test', 'database', 'backup log') as has_backup_log;
select IS_SRVROLEMEMBER('sysadmin') as is_sysadmin;
select IS_SRVROLEMEMBER('dbcreator') as is_dbcreator;
revert;

Y ahora hot2use no tiene backup database, backup log permiso, pero todavía es miembro de dbcreator rol del servidor.

¿Podrá restaurar?

Sí, por supuesto. Esto se debe a que, como se indica en el BOL y se demostró anteriormente, no se necesita permiso de copia de seguridad para poder RESTAURAR.

Sección de Reseñas y Valoraciones

Si guardas algún recelo o capacidad de progresar nuestro tutorial eres capaz de ejecutar una interpretación y con gusto lo ojearemos.

---

---

Utiliza Nuestro Buscador