none
Crear Usuario con permisos de ejecutar todos los SP de todas las bases de datos RRS feed

  • Pregunta

  • Hola a todos, por favor su ayuda en lo siguiente:

    Tengo una arquitectura clásica en la que la aplicación se conecta a SQL Server pero ÚNICAMENTE ejecuta procedimientos almacenados, nunca existe un escenario de hacer DML o cualquier otro tipo de consultas al motor, solamente invocar a procedimientos almacenados mediante ADO.net.

    Lo que quiero es crear un usuario que por defecto tenga un GRANT de ejecutar todos los procedimientos almacenados de todas las bases de datos, el plus que busco es que si se crea un nuevo SP no tenga la necesidad de otorgar un GRANT (la idea es que por defecto ya lo tenga). En Resumen necesito crear un usuario que tenga los GRANT de ejecutar los procedimientos almacenados existentes y por existir (los que se creen en un futuro)

    Saludos cordiales.


    Roy Sillerico

    jueves, 1 de marzo de 2018 13:25

Respuestas

  • [...] Crear un usuario SA2 que tendría que tener [...] los GRANTS suficientes para ejecutar cualquier SP de cualquier esquema de cualquier BD [...]

    Esto es más complicado de lo que parece. El problema es que al decir "Usuario" estás mezclando el "Login" y el "User". El Login existe a nivel de Instancia, y solo se le pueden hacer GRANTs relacionados con la instancia (por ejemplo, permiso de crear base de datos o permiso de alterar logins, donde las bases de datos y los logins son objetos que existen a nivel de instancia). Y luego, cada Login de la instancia se mapea a un User a nivel de base de datos. A este User es al que se le pueden hacer GRANTs relacionados con objetos que están en la base de datos en lugar de estar en la instancia. Es decir, que el GRANT para ejecutar SPs hay que dárselo al User y no al Login.

    Esto implica que ese SA2 que mencionabas hay que mapearlo a un User en cada base de datos, y luego hacerle los GRANTs a ese User en cada base de datos. Se podría hacer programáticamente mediante un bucle que vaya recorriendo las bases de datos y asignando los permisos, pero no hay un automatismo que permita hacerlo con una sola sentencia aplicada sobre el Login.

    Si te preguntas por qué el SA no requiere ese "mapeo" a un User en cada base de datos, la razón es que el SA es miembro del rol SysAdmin, que se mapea automáticamente al User "dbo" en todas las bases de datos, y el dbo tiene permisos ilimitados dentro de su base de datos, por lo que en resumidas cuentas el SA tiene permiso para todo.

    jueves, 1 de marzo de 2018 19:12

Todas las respuestas

  • Suponiendo que los procedimientos estén todos en un mismo esquema (tal como el esquema dbo -- no confundir con el usuario dbo, que es otra cosa distinta), puedes concederle al usuario el permiso EXECUTE sobre el esquema, y automaticamente se hereda por todos los procedimientos almacenados del esquema (actuales y los que se creen en el futuro). Pero hay que repetirlo en cada base de datos, no se propaga automaticamente de unas a otras aunque el esquema se llame igual.
    jueves, 1 de marzo de 2018 15:53
  • Concuerdo al 100% con Alberto (cosa que no es de extrañar).  Mi contribución adicional sería decirle que si tiene o idea o consigue una consulta SELECT que liste las bases de datos existentes (imagino que es fácil para un DBA), puede recorrer la consulta con un cursor y armar SQL dinámico para armar sentencias GRANT EXECUTE para cada esquema de cada base de datos.

    Adicionalmente a esto y para cubrir automáticamente esquemas futuros y bases de datos futuras, debería crear un trigger DDL en cada base de datos (incluyendo la base de datos model) que hace lo mismo cada vez que se crea un nuevo esquema.  Al hacerlo en model, estará cubriendo automáticamente bases de datos futuras e inclusive tempdb (una vez que se reinicie el servicio de SQL Server).

    Conforme voy escribiendo esto el escenario se complica.  Si desea que los usuarios sean configurables, deberá entonces tenerlos en una tabla en una base de datos.  Tal vez en master.  ¿Se puede?  Nunca lo he hecho, pero imagino que un SA puede agregar tablas a master.  Esta tabla debería tener un trigger para remover permisos de usuarios que desaparecen y para asignar permisos a nuevos usuarios que se ingresan en la tabla.  Entonces deberá tener la lógica en un procedimiento almacenado.

    Wow, resultó más grande de lo que pensé al inicio.


    Jose R. MCP
    Code Samples

    jueves, 1 de marzo de 2018 16:57
  • Muchas Gracias Alberto / WebJose

    Favor su colaboración con este detalle adicional.

    El usuario SA tiene todos los privilegios, es por ellos que si me conecto con este usuario puede ejecutar cualquier SP de cualquier BD de cualquier esquema, entonces pienso que podría hacer lo siguiente: Crear un usuario SA2 que tendría que tener un subconjunto de características del SA, este subconjunto de características serian los GRANTS suficientes para ejecutar cualquier SP de cualquier esquema de cualquier BD, entonces, como puedo crear ese subconjunto?

    Saludios 


    Roy Sillerico

    jueves, 1 de marzo de 2018 18:24
  • Bueno, ya esa pregunta se me escapa de las manos.  Imagino que ahora sí necesita usted de un verdadero DBA y no un programador como yo.

    Lo que yo sé es:  SA es el administrador todopoderoso de SQL Server.  Lo es porque pertenece al rol de servidor sysadmin (¿o es sysadmins?).  Cualquier otro usuario en este grupo tendrá los mismos privilegios.  Tanto Alberto como yo respondimos la pregunta asumiendo que agregar un usuario a este rol de servidor no es una opción.  Tiene potestad absoluta sobre todo y pues tanto Alberto como yo nunca pensamos en esta opción.  Si esto es lo que busca, pues genial.  Si no, un DBA maestro en el tema tal vez puede ayudarle a crear un rol de servidor que asemeje a sysadmin en cuanto a lo relacionado a procedimientos almacenados y nada más.  Como dije antes, ya el tema se me escapa de las manos.


    Jose R. MCP
    Code Samples

    jueves, 1 de marzo de 2018 18:49
  • [...] Crear un usuario SA2 que tendría que tener [...] los GRANTS suficientes para ejecutar cualquier SP de cualquier esquema de cualquier BD [...]

    Esto es más complicado de lo que parece. El problema es que al decir "Usuario" estás mezclando el "Login" y el "User". El Login existe a nivel de Instancia, y solo se le pueden hacer GRANTs relacionados con la instancia (por ejemplo, permiso de crear base de datos o permiso de alterar logins, donde las bases de datos y los logins son objetos que existen a nivel de instancia). Y luego, cada Login de la instancia se mapea a un User a nivel de base de datos. A este User es al que se le pueden hacer GRANTs relacionados con objetos que están en la base de datos en lugar de estar en la instancia. Es decir, que el GRANT para ejecutar SPs hay que dárselo al User y no al Login.

    Esto implica que ese SA2 que mencionabas hay que mapearlo a un User en cada base de datos, y luego hacerle los GRANTs a ese User en cada base de datos. Se podría hacer programáticamente mediante un bucle que vaya recorriendo las bases de datos y asignando los permisos, pero no hay un automatismo que permita hacerlo con una sola sentencia aplicada sobre el Login.

    Si te preguntas por qué el SA no requiere ese "mapeo" a un User en cada base de datos, la razón es que el SA es miembro del rol SysAdmin, que se mapea automáticamente al User "dbo" en todas las bases de datos, y el dbo tiene permisos ilimitados dentro de su base de datos, por lo que en resumidas cuentas el SA tiene permiso para todo.

    jueves, 1 de marzo de 2018 19:12
  • Ha caray que complicado todo esto, creo que optare por crear el SA2 y ponerle como owner de todas las BD's

    Roy Sillerico

    jueves, 1 de marzo de 2018 19:23