Como saber quien es db_owner en SQL Server

Seguramente que alguna vez se han visto en la necesidad - si, ustedes que tuvieron el infortunio de dedicarse a las TI - de revisar la infraestructura a su cargo y tratar de aplicar medidas de seguridad para evitar el uso y/o maluso indiscriminado. Bueno, pues es mi caso el dia de hoy. Y aunque he reiterado en otras ocasiones que este no es un blog de tecnología si me ha servido muy bien al propósito de no olvidar donde pongo las soluciones a esos problemas que resultan tener una solución un poco rebuscada.

En esta ocasión voy a enseñarles como auditar un servidor de bases de datos con SQL Server y asi identificar aquellos usuarios que pertenecen al rol de DB_OWNER y que quizas no sea necesario. De hecho no lo es en ningun caso y es una pésima decisión hacer esto para simplificar la implementacion de algun sistema. Bien, vamos allá.

La forma cansada: Si queremos hacer esto de forma tediosa podemos acceder via el Analizador de Consultar (el Query Analizer pues..) y conectarnos a cada base de datos y ejecutar exec sp_helpuser db_owner con lo que nos regresará los usuarios que tengan el rol db_owner para esa base de datos. Si tienes un par de bases de datos no ocupas más, pero si tu caso es como el mio y mas bien tienes muchas bases de datos te conviene mas leer como hacer:

La forma Menos cansada. Aqui vamos a utilizar un script que nos va simplificar mucho el trabajo pues solo tendremos que ejecutarlo sobre la base de datos Master y nos regresara un lista enorme con lo que queremos. Vamos por partes.

1. Primero necesitamos acceso al servidor con niveles de administrador, si ustedes administran no deberían tener problema.

2. Ahora nos conectamos con el Query Analizer a la base de datos Master porque necesitaremos tomar de alli el catalogo de bases de datos existentes en el servidor. ESta información se guarda en la tabla sysdatabases y tomares solo el nombre y el dbid. Puede revisar el contenido de esa tabla haciendo un select * from sysdatabases. Normalmente los numeros del 1 al 6 corresponden a las bases de datos nativas de SQL (master, model, northwind, etc) por lo que nuestras bases de datos empezaran a partir del dbid numero 7.

3. Ejecutamos el siguiente script, adicionalmente puede cambiar el tipo de salida que arroja el Query Analizer para que sea vaya a un archivo. Describiré paso a paso el script.

Primero declaramos las variables para controlar el ciclo y el nombre de la base de datos.

DECLARE @Contador INT
DECLARE @Rows int
DECLARE @NameDb varchar(50)

Luego inicializamos la variable @Contador en 1, igual podria ser a 7 y asi evitamos las bases de datos nativas de SQL.

Set @Contador = 1

Declaramos una base de datos temporal que llenaremos con los datos obtenidos de la tabla sysdatabases. Noten que solo creamos las columnas de Nombre de la base de datos (Name) y el identificar (dbid).

DECLARE @tbldatabase TABLE
(
Name varchar(50) NOT NULL,
dbID CHAR(11) NOT NULL
)

Ahora llenamos la tabla temporal que acabamos de crear. Ya tenemos el catalogo de bases de datos listas para que sean auditadas.

INSERT INTO @tbldatabase
SELECT name, dbid FROM sysdatabases

Ahora igualaremos la variable @Rows al numero de renglones de nuestra tabla que temporal, que serán - efectivamente estudiantes - el numero de bases de datos que auditaremos. Esta variable la usaremos para hacer un ciclo y ejecutar el script sobre todas las bases.

SET @Rows=(SELECT count(*) FROM @tbldatabase)

Aqui empieza la magia. Lo que hace es abfrir un ciclo desde 1 hasta el numero total de bases de datos almacenado en la variable @Rows y ejecutar el mismo exec sp_helpuser db_owner sobre cada base de datos. Agregare comentarios en las lineas para describir lo que hacen.

WHILE @Contador<=@Rows BEGIN
-- En este set se iguala el nombre de la base de datos segun el valor
-- del @Contador.
SET @NameDb = (SELECT Name FROM @tbldatabase WHERE dbid = @Contador)
-- Este Select es solo para que aparezca el nombre de la base de datos
-- antes y sepamos cual estamos auditando
Select @NameDb
-- Aqui ya estamos cambiando de la Master a la base de datos que estamos
-- auditando y ejecutamos el script.
exec('USE ' + @Namedb + ' exec sp_helpuser db_owner')
-- Incrementamos el contador para avanzar un registro en la tabla temporal
-- y tomar otra base de datos
SET @Contador = @Contador + 1
END

Finalmente lo ejecutamos sin miedo y con singular alegria y tendremos nuestro reporte listo para ser revisado. Y bien, que sigue?

Pues sigue quitarle esos privilegios a esos usuarios sobre las bases de datos y solo asignarle aquellos que requieran, para lo que pueden usar este post anterior.

Gracias a Fer Luna por pasarme el script en el que me base y ayudarme a adecuarlo a mis propositos.

Saludos.

Aca abajo pongo el codigo sin comentarios y listo para ser ejecutado.


------Empieza codigo ---------


DECLARE @Contador INT
DEclare @Rows int
DECLARE @NameDb varchar(50)

Set @Contador = 1

DECLARE @tbldatabase TABLE
(
Name varchar(50) NOT NULL,
dbID CHAR(11) NOT NULL
)

INSERT INTO @tbldatabase
SELECT name, dbid FROM sysdatabases

SET @Rows=(SELECT count(*) FROM @tbldatabase)
WHILE @Contador<=@Rows
BEGIN
SET @NameDb = (SELECT Name FROM @tbldatabase WHERE dbid = @Contador)
Select @NameDb
exec('USE ' + @Namedb + ' exec sp_helpuser db_owner')
SET @Contador = @Contador + 1
END

------Termina codigo --------