Siguiendo con temas de permisos, seguridad y roles ¿alguna vez has tenido que copiar/replicar permisos de un usuario de tus instancias SQL Server? Hace poco, nos tocó transferir todos los permisos que tenía un usuario a otro ¿conoces alguna manera ágil para hacerlo?
Nosotros conocemos dos procedimientos almacenados libres y desarrollados por Kenneth Fisher (http://www.sqlstudies.com), muy útiles y que nos van a facilitar este tipo de tareas que pueden llegar a ser muy complejas. Imagina que puede haber permisos asignados hasta nivel de tabla/columna, esto sería muy difícil sin ayuda de este tipo de procedimientos almacenados:
sp_SrvPermissions
Este primer procedimiento nos devuelve tres resultados, el listado y código del login, los roles asignados a nivel de instancia y los permisos asignados a nivel de instancia.
Por ejemplo, al usuario prueba le asignamos el rol de instancia “dbcreator” y le asignamos el permiso de VIEW SERVER STATE sobre la instancia.
ALTER SERVER ROLE [dbcreator] ADD MEMBER [prueba]
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [prueba]
GO
Lo primero que hay que hacer es crear el procedimiento en la instancia (el código lo puedes obtener en este enlace https://sqlstudies.com/free-scripts/sp_srvpermissions/)
El siguiente paso es ejecutar el procedimiento con el parámetro @Principal, pasándole el valor del usuario:
exec dbo.sp_SrvPermissions @Principal = 'prueba'
Como puedes ver en la imagen, los resultados son correctos mostrándonos que es miembro del rol dbcreator y tiene el permiso de VIEW SERVER STATE. Pero lo más útil en nuestra opinión es que te autogenera el script para asignar los permisos o revocarlos:
Con un simple “replace” en cualquier editor de texto o en nuestro propio SSMS podríamos ejecutar el script sobre otro usuario.
sp_DBPermissions
El segundo procedimiento nos devuelve tres resultados. El primero es un listado de los usuarios, el segundo si es miembro de algún rol de base de datos y el tercero los permisos a nivel de objetos.
Continuando realizando pruebas con el usuario “prueba” le asignamos una BBDD llamada “SICUEL”, agregándole a un rol dentro de la BBDD, db_datareader, y por último otorgándole un permiso de delete sobre una tabla.
USE [SICUEL]
GO
CREATE USER [prueba] FOR LOGIN [prueba]
GO
USE [SICUEL]
GO
ALTER ROLE [db_datareader] ADD MEMBER [prueba]
GO
use [SICUEL]
GO
GRANT DELETE ON [dbo].[Usuarios] TO [prueba]
GO
Debemos crear el procedimiento en nuestra instancia al igual que con el procedimiento anterior. El código lo podemos encontrar en: https://sqlstudies.com/free-scripts/sp_dbpermissions/
Una vez creado procedemos a ejecutarlo:
EXEC sp_DBPermissions 'All',@LoginName = 'prueba'
Pasándole el parámetro de @LoginaName el nombre del usuario, en nuestro caso, prueba:
La salida es muy similar al primer procedimiento. Nos aparece un listado con los permisos asignados, y lo más útil para nosotros es que nos genera un script automáticamente para crear el usuario en cada base de datos y asignar los permisos/roles.
La hostia estos procedimientos ¿no? Desde aquí agradecer a Kenneth Fisher su creación ya que a nosotros nos han salvado en más de una ocasión de volvernos locos picando permisos. Os invitamos a pasar por su linkedin y seguirle
Os sugerimos guardar en favoritos o marcadores esta entrada ya que creemos que estos dos procedimientos hay que tenerlos siempre en la recámara ya que son tan útiles cómo frecuentes de utilizar y nos ahorra muchísimo tiempo de tener que hacer todo el proceso manualmente.
Por cierto, ya que nos estas visitando y estás buscando info sobre seguridad, permisos y roles te sugerimos pasar por otra de nuestras entrada y tener siempre a mano el poster que nos proporciona Microsoft:.
¡Nos vemos pronto debeas!
