Server Roles en SQL Server
Funciones fijas de servidor
La administración de la seguridad con los elementos de una base de datos es una tarea que necesita de mucho cuidado, es necesario que los asegurables presentes en la base de datos tengan los permisos requeridos y asignados adecuadamente a las entidades de seguridad.
Niveles de Seguridad en SQL Server
La seguridad se puede administrar para el servidor y también para una base de datos específica. Cada nivel tiene sus elementos asegurables y sus entidades de seguridad.
Los niveles de seguridad son:
1. A nivel de Servidor
En el nivel de servidor podemos administrar las siguientes entidades de seguridad
* Funciones de servidor
* Inicios de sesión
2. A nivel de Base de datos
En el nivel de base de datos podemos trabajar con las siguientes entidades de seguridad
* Funciones de base de datos
* Usuarios de base de datos
* Funciones de aplicación
Se recomienda la revisión permanente de los cambios en los permisos.
Seguridad a nivel de Servidor
Funciones Fijas de servidor (Roles de Servidor)
Permiten administrar los permisos sobre los objetos del servidor. Desde SQL Server 2012 se pueden crear nuevas funciones de servidor y asignar permisos en los asegurables a nivel de servidor.
Las funciones fijas de servidor o roles de servidor son:
| Rol fijo de servidor | Descripción |
| bulkadmin | Pueden ejecutar las opciones de copia masiva de datos usando la instrucción BULK INSERT. |
| diskadmin | El rol fijo de servidor diskadmin se usa para administrar archivos de disco. |
| dbcreator | Pueden crear, modificar, quitar y restaurar cualquier base de datos. |
| public | Cada inicio de sesión de SQL Server pertenece al rol de servidor public. |
| sysadmin | Son los administradores del servidor y pueden realizar cualquier actividad sin restricciones. |
| processadmin | Pueden finalizar los procesos que se ejecuten en una instancia de SQL Server. |
| serveradmin | Pueden cambiar las opciones de configuración del servidor y apagarlo. |
| securityadmin | Administran los inicios de sesión y sus propiedades. Pueden administrar los permisos de los asegurables a nivel de servidor usando GRANT, DENY y REVOKE. Pueden administrar los permisos de los asegurables a nivel de base de datos usando GRANT, DENY y REVOKE siempre que tengan acceso a una base de datos.A nivel de servidor pueden restablecer contraseñas para los inicios de sesión de SQL Server. |
| setupadmin | Pueden agregar y quitar servidores vinculados mediante instrucciones Transact-SQL. |
Cada función fija de servidor tiene una relación de permisos asignados para cada inicio de sesión que pertenezca a estos roles.
La imagen muestra las diferentes funciones fijas de servidor y los permisos. Son 9 funciones fijas.
Crear Roles o funciones fijas de servidor
Desde SQL Server 2012 es posible crear nuevos roles de servidor.
Instrucción Create server role
Permite crear un rol de servidor.
Sintaxis
Create server role NombreRol [Authorization InicioSesion]
Ejemplos
Ejercicio 1
Crear un rol de servidor llamado Administradores autorizado a SecurityAdmin
use master
Create server role Administradores authorization SecurityAdmin
go
Ejercicio 2
Crear un rol de servidor llamado Vendedores autorizado a GerenteVentas
use master
Create server role Vendedores authorization GerenteVentas
go
Ejercicio 3
Listar los roles de servidor
select name, type_desc from sys.server_principals where type = ‘R’
go
Para listar los roles fijos, no incluye los creados por el usuario
sp_helpsrvrole
go
Ejercicio 4
Crear un rol de servidor llamado Logistica autorizado a ProcessAdmin
El Script debe comprobar que el rol no existe.
use master
if not exists (select name, type_desc from sys.server_principals where type = ‘R’ and name =’Logistica’)
Begin
Create server role Logistica authorization ProcessAdmin
End
go
Modificar roles de servidor
Instrucción: Alter server role
Permite agregar o quitar miembros al rol y cambiarle de nombre.
Sintaxis
Alter server role NombreRol
ADD MEMBER InicioSesion |
DROP MEMBER InicioSesion |
WITH NAME = NuevoNombre
Para agregar o quitar inicios de sesión a un rol se pueden usar los siguientes procedimientos almacenados del sistema.
Para agregar
sp_addsrvrolemember InicioSesión, Rol
Para quitar
sp_dropsrvrolemember InicioSesión, Rol
Ejemplos de modificación de roles de servidor
Ejercicio 1
Crear un inicio de sesión llamado EmpresaDBA y hacerlo miembro de Sysadmin
use master
Create login EmpresaDBA with password = ‘Unodostres’
go
Alter server role sysadmin add member EmpresaDBA
go
Para visualizar los miembros del rol Sysadmin
sp_helpsrvrolemember Sysadmin
go
Ejercicio 2
Cambiar el nombre del rol Vendedores por FuerzaVentas
use master
Alter server role Vendedores with name= FuerzaVentas
go
Ejercicio 3
Quitar el inicio de sesión EmpresaDBA de sysadmin y hacerlo miembro de SecurityAdmin
use master
Alter server role sysadmin drop member EmpresaDBA
Alter server role SecurityAdmin add member EmpresaDBA
go
Eliminación de un rol de servidor
Instrucción: Drop server role
Permite eliminar un rol creado
Sintaxis:
Drop server role NombreRol
Nota: El rol debe estar vacío, es decir, no debe tener miembros (Logins).
El listado de los roles de servidor y los inicios de sesión de cada uno
select
P.name As ‘Rol de Servidor’,
R.member_principal_id As ‘Código Inicio de Sesión’,
L.name As ‘Nombre Inicio de Sesión’
from sys.server_principals As P
join sys.server_role_members As R on P.principal_id = R.role_principal_id
join sys.sql_logins As L on R.member_principal_id = L.principal_id
where P.type = ‘R’
go
Ejemplos de eliminación de roles de servidor
Ejercicio 1
Eliminar el rol FuerzaVentas
use master
Drop server role FuerzaVentas
go
Ejercicio 2
Eliminar el rol Gerentes
use master
Drop server role Gerentes
go
Note que aparece el mensaje: El rol tiene miembros. Debe estar vacío antes de quitarlo.
Para ver los miembros se utiliza la siguiente instrucción:
select
P.name As ‘Rol de Servidor’,
R.member_principal_id As ‘Código Inicio de Sesión’,
L.name As ‘Nombre Inicio de Sesión’
from sys.server_principals As P
join sys.server_role_members As R on P.principal_id = R.role_principal_id
join sys.sql_logins As L on R.member_principal_id = L.principal_id
where P.type = ‘R’
go
El miembro que debemos eliminar es: GerenteVentas
Alter server role Gerentes drop member GerenteVentas
go
Drop server role Gerentes
go