Solución:
db_ddladmin frente a db_owner
Por lo que puedo decir por lo que probé y leí, en su mayor parte su lista parece precisa excepto db_ddladmin Te permite CREATE SCHEMA. Confirmé que los otros permisos de seguridad que enumeraste fueron denegados.
Denegado solo con DDLADMIN:
[ALTER ANY USER]
[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]
[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]
[DROP DATABASE]
Teniendo en cuenta que el. . .
-
db_datareaderpermitiráSELECTacceso a todas las mesas -
db_datarwriterpermitiráINSERT,UPDATE, yDELETEacceso a todas las mesas -
db_executorpermitiráEXECUTEacceso a todos los objetos ejecutables
Además, tener permisos de rol db_ddladmin puede significar. . .
Nota: Dado que tiene tantas versiones diferentes de SQL Server de 2005 a 2014, puede ser mejor que un pequeño grupo de usuarios pruebe esto inicialmente para ver quién grita para solucionar cualquier problema, etc.
-
Los objetos que poseen con este rol no serán propiedad de DBO, por lo que es posible que tenga que lidiar con problemas de cambio de propiedad si alguna vez hay un problema con algo en este nivel. No estoy 100% seguro de que esto sea un problema, pero vale la pena mencionarlo por si acaso.
Fuente: Cadenas de propiedad
-
Con este rol (puede variar según la versión de SQL Server) Es posible que puedan agregar principios de seguridad SQL definidos en la base de datos actual a los objetos que aún poseen, pero no a todos los objetos. (los que no son de su propiedad) ni agregar un nuevo principal definido a nivel de servidor al nivel de base de datos.
Además, no tener permisos de rol DBO puede significar. . .
Nota: Dado que tiene tantas versiones diferentes de SQL Server de 2005 a 2014, puede ser mejor que un pequeño grupo de usuarios pruebe esto inicialmente para ver quién grita para solucionar cualquier problema, etc.
-
No tener el rol DBO puede impedir ciertas interfaces GUI del diseñador SSMS (Variante de la versión de SQL Server) de poblar o abrir sin error (por ejemplo, al modificar tablas o columnas a través de la GUI) aunque hacerlo a través de T-SQL funciona y los permisos están en su lugar. En algunas versiones de SQL Server, esto puede resolverse permitiendo
GRANT VIEW DEFINITIONdonde esto es un problema y también puede ser solo una advertencia solo en ciertas versiones de SQL Server.Recursos
-
No ha iniciado sesión como propietario de la base de datos ni como usuario miembro del rol db_owner. No podrá guardar cambios en tablas que no sean de su propiedad.
-
El rol db_ddladmin no permite el uso de funciones de “diseño” en SSMS
“Tratamos de evitar que los usuarios / desarrolladores dbo en sus bases de datos de control de calidad tanto como podamos. Uno de los problemas con esto es que todavía necesitan poder crear y modificar objetos de base de datos como tablas de usuario. Muchos desarrolladores son nuevos en MS SQL y, por lo tanto, tienden a seguir con la GUI (SSMS) para este tipo de trabajo. El problema surge cuando les otorgamos db_ddladmin (no dbo) y ya no pueden modificar tablas o columnas a través de la GUI del diseñador de tablas. tienen que tomarse más tiempo para aprender los comandos TSQL y su sintaxis (que tal vez nunca vuelvan a necesitar) o involucrar al equipo de DBA, lo que les quita tiempo a nuestras otras actividades.
No sé si esto es un error o una solicitud de función, pero lo considero un error ya que el usuario tiene permisos suficientes para alterar la tabla a través de TSQL, pero la GUI les da mensajes que dicen:
“No ha iniciado sesión como propietario de la base de datos o administrador del sistema. Es posible que no pueda guardar los cambios en las tablas que no le pertenecen. “Y” Tabla
[schema].[table]está configurado para solo lectura, el usuario no tiene suficientes derechos en esta tabla.“Un rastro parece apuntar a que la verificación es un is_member (‘db_owner’) que excluirá a los miembros de db_ddladmin aunque de hecho tengan permisos para modificar el objeto. Microsoft SQL Server Management Studio “
Publicado por Agent DBA el 25/1/2010 a las 7:06 AM
Tuve un problema similar y logré resolverlo realizando la siguiente subvención
GRANT view definition on schema:: <schemaname> to <username>
-
Otras Consideraciones
Dado que afirma que esto se está revisando caso por caso
Uno de los permisos que actualmente está limitado son los permisos db_owner.
Este permiso se está revisando caso por caso, pero un cambio común es reemplazar los permisos db_owner con lo siguiente:
- db_datareader
- db_datawriter
- db_ddladmin
- db_executor
¿Ha considerado crear roles personalizados adicionales para obtener más acceso a nivel de base de datos de “todos los objetos” que cada persona necesita en lugar de otorgarles el db_ddladmin role, ya que probablemente les dará más de lo que realmente necesitan para los objetos de nivel de base de datos.
Por lo general, doy lo que se necesita exactamente y nada más para que hagan su trabajo y si hay una necesidad “habitual” o “estándar” de acceso a objetos de nivel de base de datos a todos los objetos en una base de datos, creo un rol de base de datos personalizado como el db_executor pero mira mi ejemplo a continuación. De esta manera, puede otorgar a las personas lo que realmente necesitan para TODOS los objetos de la base de datos en una base de datos en particular si no obtiene un nivel de objeto explícito en sus bases de datos para su seguridad.
----Custom Database Roles
/* CREATE A NEW ROLE -- Execute to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Execute
GRANT EXECUTE TO db_All_StoredProc_Execute
/* CREATE A NEW ROLE -- Alter to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Alter
GRANT ALTER ANY SCHEMA TO db_All_StoredProc_Alter
/* CREATE A NEW ROLE -- View Definition to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_View
GRANT VIEW DEFINITION TO db_All_StoredProc_View
/* CREATE A NEW ROLE - Any schema alter and create procedure permissions */
-- Database specific
CREATE ROLE db_All_CreateProc_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateProc_AlterSchema
GRANT CREATE PROCEDURE TO db_All_CreateProc_AlterSchema
GO
/* CREATE A NEW ROLE - Any schema alter and create table permissions */
-- Database specific
CREATE ROLE db_All_CreateTable_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateTable_AlterSchema
GRANT CREATE TABLE TO db_All_CreateTable_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create function permissions */
-- Database specific
CREATE ROLE db_All_CreateFunction_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateFunction_AlterSchema
GRANT CREATE FUNCTION TO db_All_CreateFunction_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create aggregate permissions */
-- Database specific
CREATE ROLE db_All_CreateAggregate_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateAggregate_AlterSchema
GRANT CREATE AGGREGATE TO db_All_CreateAggregate_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create view permissions */
-- Database specific
CREATE ROLE db_All_CreateView_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateView_AlterSchema
GRANT CREATE VIEW TO db_All_CreateView_AlterSchema
/* CREATE A NEW ROLE - Any schema alter and create schema permissions */
-- Database specific
CREATE ROLE db_All_CreateSchema_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateSchema_AlterSchema
GRANT CREATE SCHEMA TO db_All_CreateSchema_AlterSchema
También quería compartir un rol db_DDLAdmin_Restriction que quizás desee considerar para considerar crear de otra manera con explícito DENY para restringir lo que db_ddladmin dar acceso para que al menos pueda crear esto en las bases de datos donde les otorga este rol y establece el explícito DENY para los tipos de objetos reales, etc. a los que no desea que tengan acceso.
Por ejemplo, si sabe que definitivamente crearán funciones y procedimientos almacenados, puede excluir DENY CREATE FUNCTION, DENY CREATE PROCEDURE, DENY ALTER ANY SCHEMA.
---Create ddladmin restriction custom DB role
DENY ALTER ANY ASSEMBLY TO db_DDLAdmin_Restriction
DENY ALTER ANY ASYMMETRIC KEY TO db_DDLAdmin_Restriction
DENY ALTER ANY CERTIFICATE TO db_DDLAdmin_Restriction
DENY ALTER ANY CONTRACT TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_DDLAdmin_Restriction
DENY ALTER ANY DATASPACE TO db_DDLAdmin_Restriction
DENY ALTER ANY FULLTEXT CATALOG TO db_DDLAdmin_Restriction
DENY ALTER ANY MESSAGE TYPE TO db_DDLAdmin_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING TO db_DDLAdmin_Restriction
DENY ALTER ANY ROUTE TO db_DDLAdmin_Restriction
DENY ALTER ANY SCHEMA TO db_DDLAdmin_Restriction
DENY ALTER ANY SERVICE TO db_DDLAdmin_Restriction
DENY ALTER ANY SYMMETRIC KEY TO db_DDLAdmin_Restriction
DENY CHECKPOINT TO db_DDLAdmin_Restriction
DENY CREATE AGGREGATE TO db_DDLAdmin_Restriction
DENY CREATE DEFAULT TO db_DDLAdmin_Restriction
DENY CREATE FUNCTION TO db_DDLAdmin_Restriction
DENY CREATE PROCEDURE TO db_DDLAdmin_Restriction
DENY CREATE QUEUE TO db_DDLAdmin_Restriction
DENY CREATE RULE TO db_DDLAdmin_Restriction
DENY CREATE SYNONYM TO db_DDLAdmin_Restriction
DENY CREATE TABLE TO db_DDLAdmin_Restriction
DENY CREATE TYPE TO db_DDLAdmin_Restriction
DENY CREATE VIEW TO db_DDLAdmin_Restriction
DENY CREATE XML SCHEMA COLLECTION TO db_DDLAdmin_Restriction
DENY REFERENCES TO db_DDLAdmin_Restriction
GO
Usando un script SQL para enumerar todos los permisos, fui y creé usuarios para cada caso.
EXECUTE AS USER = 'test_user'
SELECT
permission_name
FROM fn_my_permissions(null, 'DATABASE')
ORDER BY subentity_name, permission_name
REVERT;
Luego comparé los resultados y llegué a la siguiente lista, con documentación principalmente de msdn (cualquier cita a la que no se haga referencia específica proviene del enlace de msdn).
A continuación se muestra parte de la documentación que utilicé para informar a las personas que perderían los permisos de dbo qué exactamente estaban perdiendo.
ALTERAR
Otorga la capacidad de cambiar las propiedades, excepto la propiedad, de un asegurable particular. Cuando se otorga en un alcance, ALTER también otorga la capacidad de alterar, crear o eliminar cualquier elemento asegurable que esté contenido dentro de ese alcance. Por ejemplo, el permiso ALTER en un esquema incluye la capacidad de crear, modificar y eliminar objetos del esquema.
ALTERAR CUALQUIER PAPEL DE APLICACIÓN
ALTERAR CUALQUIER AUDITORÍA DE BASE DE DATOS
ALTERA CUALQUIER PAPEL
ALTERA CUALQUIER USUARIO
Otorga la capacidad de CREAR, ALTERAR o SOLTAR instancias individuales de la base de datos asegurable. Por ejemplo, ALTER ANY SCHEMA confiere la capacidad de crear, alterar o eliminar cualquier esquema en la base de datos.
Los roles de la aplicación son los principales de la base de datos que permiten que una aplicación se ejecute con sus propios permisos similares a los de un usuario.
Auditar una instancia de SQL Server o una base de datos de SQL Server implica rastrear y registrar eventos que ocurren en el sistema. El objeto Especificación de auditoría de nivel de base de datos pertenece a una auditoría. Puede crear una especificación de auditoría de base de datos por base de datos de SQL Server por auditoría.
Los roles de base de datos se utilizan para administrar fácilmente los permisos en sus bases de datos, SQL Server proporciona varios roles que son principales de seguridad que agrupan a otros principales. Son como grupos en el sistema operativo Microsoft Windows. Los roles a nivel de base de datos son para toda la base de datos en su ámbito de permisos.
AUTENTICAR
Encontrado en msdn.
Los permisos AUTENTICAR Y AUTENTICAR SERVIDOR solo se usan cuando se usa EJECUTAR COMO en escenarios cruzados de bases de datos y acceso al servidor (respectivamente).
BASE DE DATOS DE RESPALDO
REGISTRO DE RESPALDO
CONECTAR REPLICACIÓN
Se utiliza para permisos de replicación de bases de datos.
CONTROL
Otorga al beneficiario capacidades similares a las de la propiedad. El beneficiario efectivamente tiene todos los permisos definidos sobre lo asegurable. Un principal al que se le ha otorgado CONTROL también puede otorgar permisos sobre el elemento asegurable.
CREAR PAPEL
Otorga al beneficiario la capacidad de crear la base de datos asegurable.
SHOWPLAN
Los permisos de Showplan se utilizan para varias opciones de declaración Showplan SET cuando se utilizan con lotes de Transact-SQL.
SUSCRIBIRSE A LAS NOTIFICACIONES DE CONSULTA
Documentación sobre notificaciones de consultas.
Construidas sobre la infraestructura de Service Broker, las notificaciones de consultas permiten que las aplicaciones sean notificadas cuando los datos han cambiado. Esta función es particularmente útil para aplicaciones que proporcionan un caché de información de una base de datos, como una aplicación web, y necesitan ser notificadas cuando se modifican los datos de origen.
TOMAR POSESIÓN
Permite al beneficiario asumir la propiedad del asegurable sobre el que se otorga.
VER ESTADO DE LA BASE DE DATOS
Se utiliza para ver funciones y vistas de administración dinámica (Transact-SQL).
VER DEFINICIÓN
Documentación sobre permisos de definición de vistas.
El permiso VER DEFINICIÓN permite al usuario ver los metadatos del elemento protegible sobre el que se otorga el permiso. Sin embargo, el permiso VER DEFINICIÓN no confiere acceso al elemento asegurable en sí. Por ejemplo, un usuario al que solo se le concede el permiso VER DEFINICIÓN en una tabla puede ver los metadatos relacionados con la tabla en la vista del catálogo sys.objects. Sin embargo, sin permisos adicionales como SELECT o CONTROL, el usuario no puede leer datos de la tabla.