Jefferson Elias

Cómo bajar o eliminar el inicio de sesión de SQL Server y todas sus dependencias

October 14, 2019 by

Introducción

A veces, por circunstancias especiales necesitamos eliminar el inicio de sesión de SQL Server porque por alguna causa ajena le dimos acceso a una persona que se fue o no es prioritario que se halle activo, o porque este inicio de sesión fue utilizado por una aplicación que se ha sido dado de baja, por ejemplo.

Para hacerlo, podemos usar SQL Server Management Studio (SSMS) de la siguiente manera:

  1. Abrir SSMS
  2. Conéctese a una instancia de SQL Server
  3. En el Explorador de objetos, vaya al nodo «Seguridad» y luego inicio de sesión

  4. Haga clic derecho en el inicio de sesión de SQL Server que desea colocar y luego haga clic en “Eliminar”

  5. SSMS mostrará el siguiente mensaje de advertencia

  6. Haga clic en “Aceptar”

También podríamos ejecutar una instrucción DROP LOGIN statement:

Consideramos que no importa la ruta que siguió, si todo salió bien, su inicio de sesión ahora se descarta. Sin embargo, como dijo SSMS, es posible que haya dejado algunos elementos dentro de sus bases de datos. Si hubo asignaciones entre este inicio de sesión y hubo la participación de uno o más usuarios de base de datos (uno por base de datos), estos usuarios todavía se encuentran presentes y están allí.

También podemos llegar a esa situación cuando se efectúa un proceso de restauración de una o más bases de datos de una instancia de SQL Server (servidor de origen) a otro servidor (servidor de destino) donde todos los inicios de sesión asignados a estas bases de datos no se crean en un servidor de destino, o por el contrario se crean, pero con diferente SID. De todos modos, si llegaste a la situación que esperabas, entonces está bien, pero si no, es obligatorio considerar cuidadosamente el consejo en esa advertencia que dice que necesitamos transferir la propiedad de los esquemas a otro usuario.

Vale la pena mencionar también que hay casos en los que estos pasos fallarán. Tomamos por ejemplo el caso en que el inicio de sesión que se desea eliminar se establece al propietario de una base de datos para una o más bases de datos. En ese caso, obtendrá las siguientes pantallas en SSMS:

Si usó T-SQL, recibirá el siguiente mensaje de error:

En este artículo, trataremos de eliminar un inicio de sesión de SQL Server y todas sus dependencias mediante instrucciones T-SQL. Esta prueba y sus conclusiones nos proporcionarán información relevante que podemos usar si estamos dispuestos a automatizar esta tarea o al menos algunas partes de ella.

En las siguientes secciones, primero definiremos y crearemos un caso de prueba. Nuestro objetivo será eliminar un inicio de sesión de SQL Server [ApplicationSQLUser1] y sus usuarios de bases de datos correspondientes, ambos llamados [UserA]. Primero comenzaremos a tratar de eliminar el usuario de la base de datos [UserA] de la base de datos [Db1].Una vez que logremos realizar esta tarea, deberemos revisar los pasos que seguimos y analizaremos su secuencia apropiada. Una vez hecho esto, consideraremos la eliminación del inicio de sesión y también enumeraremos las acciones que deben realizarse.

Situación del caso de prueba

Explicación

En esta sección, presentaremos la situación a la cual nos referiremos en este artículo por cuanto queremos colocar un inicio de sesión de SQL Server llamado [ApplicationSQLUser1], lque se asigna a dos bases de datos de SQL Server, [Db1] y [Db2]. Esta asignación se realiza entre este inicio de sesión de un usuario de la base de datos llamado [UsuarioA] en [Db1] y [dbo] en [Db2]. En realidad, la base de datos [Db2] es propiedad del inicio de sesión [ApplicationSQLUser1]. También hay otro inicio de sesión llamado [ApplicationSQLUser2] que se asigna a [UserB] en la base de datos [Db1].

Hay algunos otros hechos importantes:

  • A nivel del servidor::
    • El inicio de sesión [ApplicationSQLUser1] es el propietario de la base de datos [Db3]
    • El inicio de sesión [ApplicationSQLUser1] ] tiene permiso CONNECT SQL y puede otorgarlo a cualquier inicio de sesión de SQL Server
    • El inicio de sesión [ApplicationSQLUser1] otorgó permiso CONNECT SQL para iniciar sesión[ApplicationSQLUser2]
    • En las instancias de SQL Server 2012+, el inicio de sesión [ApplicationSQLUser1] es el propietario de un rol de servidor llamado [ServerRoleOwnedByUser1].

  • En la base de datos [Db1]:
    • [UserA] ] es el propietario de un esquema llamado[ApplicationSchema1]
    • [UserA] se establece como ejecutor para un procedimiento llamado:
    • [UserB] ha recibido el permiso EJECUTAR para ese procedimiento por [UserA]
    • [UserA] posee una función de base de datos llamada [RoleOwnedByUserA]

Diagrama que representa la situación

Aquí hay un diagrama que representa la situación descrita en la subsección previa.

Script de creación

A continuación, usted encontrará los comandos necesarios para crear esta situación en su entorno. Este código se puede encontrar en el documento llamado “DropLogin-DropDbUser.tests.sql” adjunto a este artículo.

Quitar un usuario de la base de datos

En esta sección, nosotros realizaremos diferentes pruebas (según la situación del caso de prueba) con el objetivo de poder concluir sobre los pasos a seguir sobre lo que debemos hacer para hacer un buen procedimiento de almacenado. El mismo que elimine a los usuarios de la base de datos.

Nota

  • Estas operaciones deben realizarse solo después de una copia de seguridad (completa, diferencial o de registro)
  • Esta copia de seguridad debe realizarse sin ninguna conexión de cliente, excepto la suya
  • Si se produce un problema, puede restaurar esta copia de seguridad

Caída manual del usuario de la base de datos [UsuarioA]

Ahora intentaremos ejecutar la siguiente consulta y luego verifiquemos que es lo que sucede.

En este instante se verifica nuestro primer mensaje de error que dice que no podemos quitar a este usuario debido que es el propietario de uno o más esquemas de base de datos.

Tome en cuenta que esto no es un problema, simplemente ejecutamos la siguiente instrucción para que este mensaje desaparezca. Tenga en cuenta que aquí la situación es bastante simple porque tenemos un esquema.

Nota

  • Aquí elegimos al usuario de la base de datos [dbo] pero no hay obligación de usarlo
  • Hay un cambio en el otorgante de permisos después de que se haya ejecutado este script. Para estar seguro, simplemente ejecute la siguiente declaración para volver a establecer [UsuarioA] como propietario del esquema:

    USE Db1 ;

    Como se puede observar, tenemos un permiso establecido por [UsuarioA]al que podremos verificar después de ejecutar la siguiente consulta:

    Obtendremos los siguientes resultados:

    Si de manera repetida volvemos a ejecutar la autorización de modificación en [ApplicationSchema1] para volver a establecerla en el usuario de la base de datos [dbo]:

    Y también se vuelve a ejecutar la consulta anterior contra sys. database_permissions, obtenemos un conjunto de resultados vacío:

    Verifiquemos los permisos de la base de datos asignados al usuario de la base de datos [UsuarioB] con la siguiente consulta:

    Obtendremos los siguientes resultados y veremos que el permiso simplemente ha sido revocado.

    Esto significa que potencialmente rompimos una o más aplicaciones de cliente y deberíamos almacenar todos los permisos asignados por el UsuarioA antes de ejecutar esta declaración.

Supongamos que todo lo ejecutado hasta este momento salió como se esperaba y vuelve a intentar eliminar el usuario de la base de datos:

Nuevamente, falló porque [UserA] posee el rol de propietario de la base de datos [RoleOwnedByUserA]:

Resolvamos este problema modificando y alterando la autorización para el rol del usuario de la base de datos [dbo] [RoleOwnedByUserA]. TEsto se logra ejecutando la siguiente instrucción:

Ahora, intentemos una vez más descartar el usuario de la base de datos. Adivine qué sucederá después de ejecutar la declaración DROP USER

En realidad, podemos observar que no tuvo éxito porque tenemos un procedimiento almacenado que hace referencia a este usuario de la base de datos…

Aquí, podríamos tener un problema porque podríamos hacer que una aplicación que funciona en este momento falle por completo. Por lo tanto, es importante que la recomendación sea de probar las modificaciones del código.

Aquí, estamos seguros de lo que estamos haciendo, por lo que modificaremos el procedimiento almacenado para que se ejecute como usuario de la base de datos [dbo].

Intentemos nuevamente eliminar al usuario de la base de datos [UserA]. Esta vez, debería tener éxito…

En efecto:

Probemos una cosa más y deberemos crear una vez más nuestra base de datos [UserA]. Para tal fin configuremos a este usuario como miembro de una función de base de datos integrada, Por ejemplo [db_ddladmin]. Para hacerlo, de esta forma podemos ejecutar el siguiente script T-SQL:

Ahora, intentemos descartar [UserA] y verificar si termina correctamente…

¡Y funcionó!

Operaciones realizadas por completo

Aquí está el documento escrito o script completo de las acciones realizadas anteriormente.

Conclusiones de las pruebas

En esta sección, hicimos pruebas nativas para descartar un usuario de la base de datos. Es importante mencionar que tuvimos que tomar decisiones y efectuar algunos cambios en nuestra base de datos para poder alcanzar nuestro objetivo. Ahora, es hora de resumir lo que hemos aprendido con el objetivo de que tengamos todo definido para escribir un procedimiento almacenado que haga todas las acciones que encontramos obligatorias para descartar un usuario de la base de datos.

Lo que se debe hacer primero es que tenemos que almacenar los permisos que nuestro usuario de la base de datos otorgó a otros usuarios diferentes en una tabla temporal. Podemos hacer esto con la siguiente consulta que ya se muestra abajo.

¿Por qué deberíamos hacer eso? Esto se debería hacer a causa de que una instrucción ALTER AUTHORIZATION en un esquema eliminará todos los permisos otorgados en los objetos de ese esquema. Este es el comportamiento esperado de esa instrucción, tal como como podemos ver en la página de documentación de Microsoft:

Entonces, una vez que ejecutamos la instrucción de ALTER AUTHORIZATION tenemos que reasignar estos permisos.

Luego, nosotros deberíamos también considerar el rol de la propiedad y tendremos que reasignar los mismos a un usuario. Nuevamente, esto se hace mediante la instrucción de ALTER AUTHORIZATIONy deberíamos cuidar de lo que hacemos aquí, pero si ya hemos guardamos los permisos de la base de datos, entonces tenemos todo lo que necesitamos para volver a establecer los roles de permisos. Para demostrar esto, ejecutemos la siguiente declaración que otorgará un permiso para la función de base de datos [RoleOwnedByUserA]:

Verifiquemos los permisos asignados a [UserB]

Consulta:

Resultados:

Finalmente, tuvimos que revisar el código existente para que no podamos encontrar ninguna referencia a [UserA] en el cuerpo de los objetos de procedimiento (procedimientos almacenados, funciones, etc.).

Sin embargo, es importante mencionar que hay una cosa más que podemos concluir. Es que hay una orden para respetar si queremos automatizar la eliminación de un usuario de la base de datos U:

  1. Verifique si todas las referencias al usuario de la base de datos U dentro de los objetos del procedimiento. Si de alguna manera se encuentra alguno, entonces genere un error
  2. Tome todas las medidas apropiadas entre las siguientes:
    1. Si ocurrió un error en el paso 1, usted deberá revisar el código y volver a intentar el paso 1
    2. Si no se produjo ningún error, vaya al paso 3
  3. Ahora podemos almacenar los permisos de la base de datos asignados por el usuario de la base de datos U en una tabla temporal
  4. Enumere todos los esquemas de la base de datos propiedad del usuario de la base de datos U y establezca un nuevo propietario para ellos
  5. Enumere todas las funciones de la base de datos de la propiedad del usuario U de la base de datos y establezca un nuevo propietario para ellas
  6. Descarte el usuario de la base de datos U

Descartar un inicio de sesión de SQL Server

Descartar a todos los usuarios de la base de datos asignados a un inicio de sesión

Como se puede observar, en nuestra situación de prueba, había dos asignaciones de bases de datos definidas para el inicio de sesión [ApplicationSQLUser1].]. Inicialmente ya logramos eliminar el más difícil, [UserA]] en la base de datos [Db1]. Debido a que necesitamos eliminar cualquier referencia en cualquier base de datos asignada, este es el momento de considerar la segunda.

En realidad, esta asignación se realiza al usuario de la base de datos [dbo] que es un usuario reservado al que no podemos descartar. Es una consecuencia del hecho de que el posee el inicio de sesión [ApplicationSQLUser1] Revisaremos esto en un momento y ya no será un problema.

Sin embargo, si estuviéramos que lidiar con otro usuario que no sea [dbo], deberíamos seguir exactamente la misma ruta que hicimos para el usuario [UserA] en la base de datos [Db1].

Eliminación manual de inicio de sesión de SQL Server [ApplicationSQLUser1]

Intentemos eliminar este inicio de sesión directamente ejecutando la siguiente instrucción:

Desafortunadamente, esta instrucción fallará con el siguiente error:

Esto significa que de forma prioritaria deberemos tomar una copia de los permisos otorgados por este inicio de sesión y revocarlos y otorgarlos nuevamente como otro inicio de sesión de SQL Server como [sa]. Es importante indicar que podremos enumerar los permisos de servidor existentes mediante la siguiente consulta:

En este caso de prueba, obtendremos los siguientes resultados:

Entonces nosotros podremos resolver esto otorgando el permiso CONNECT SQL como [sa] de la siguiente manera:

Si de alguna manera volviéramos a ejecutar la consulta para recuperar los permisos de nivel de servidor, nosotros obtendríamos un conjunto de resultados vacío:

Ahora, estamos listos para otra prueba para eliminar el inicio de sesión [ApplicationSQLUser1]

Aquí, en esta instancia recibimos otro mensaje de error que nos indica que necesitamos revisar el rol de la propiedad del servidor:

Nota

No recibirá este mensaje de error en un SQL Server 2008 R2 o una versión inferior de SQL Server ya que, en estas versiones, no es posible el poder crear una función de servidor personalizada.

Necesitamos obtener la lista de roles de propiedad del servidor del inicio de sesión [ApplicationSQLUser1]. Existe la consulta que nos ayudará para eso:

Y aquí está el conjunto de resultados para esta consulta:

Necesitamos, una vez más, usar la instrucción ALTER AUTHORIZATION para deshacernos de este mensaje de error:

Ahora, deberemos intentar una vez más eliminar nuestro inicio de sesión [ApplicationSQLUser1] login…

Recibiremos el siguiente mensaje de error:

Por consiguiente, para poder deshacernos de este error, deberemos enumerar las bases de datos cuyo propietario es el inicio de sesión [ApplicationSQLUser1] ]. Haremos esto usando la siguiente instrucción

Los resultados de esa consulta nos mostrarán que la base de datos [Db2] es propiedad de este inicio de sesión.

Podemos cambiar el propietario de la base de datos a [sa] usando la siguiente declaración:

Nota

Preste mucha atención a esta instrucción porque realmente reasignara el usuario de la base de datos [dbo] al inicio de sesión del servidor para que después pueda proporcionar la palabra reservada «TO», que es [sa] en nuestro caso.

Una vez que hayamos ejecutado la instrucción anterior, podremos finalmente eliminar el inicio de sesión.

Operaciones realizadas por completo

Aquí están las operaciones que realizamos en la última subsección.

Conclusión de las pruebas

Como hemos visto para los usuarios de la base de datos, el eliminar en un inicio de sesión de SQL Server se observa que esta situación no siempre es una tarea sencilla. Hay múltiples operaciones que hacer.

Resumamos:

  1. (Opcional) Si su instancia de SQL Server está alojada en una máquina virtual, apague esta máquina, tome una muestra de pantalla instantánea y reinicie. También puede realizar una copia de seguridad completa del disco con SQL Server inactivo.
  2. Verifique las asignaciones entre el inicio de sesión de SQL Server y una o más bases de datos de nuestra instancia de SQL Server
  3. Para cada asignación de base de datos encontrada, descarte el usuario de la base de datos correspondiente siguiendo los pasos descritos anteriormente.

    Nota

    Debe tomar en cuenta que esta operación puede fallar y podríamos agregar un paso adicional que verificaría en cada base de datos la existencia de que si hay usos de referencias a usuarios de la base de datos asignados con el inicio de sesión que queremos eliminar. De ser este el caso, Si se encuentra alguna referencia, la eliminación de inicio de sesión fallará.

  4. Obtenga y tome una copia de los permisos de servidor existentes otorgados por el inicio de sesión que queremos eliminar
  5. Debe revocar estos permisos y reasignarlos como un servidor principal diferente
  6. Debe revisar los roles del servidor que pertenecen al inicio de sesión que queremos eliminar y deberá asignarles un propietario diferente
  7. Debe revisar las bases de datos que pertenecen al inicio de sesión que queremos eliminar y así mismo deberá asignarles un propietario diferente
  8. (Revise otros tipos de objetos de servidor que no se consideraron durante nuestras pruebas y tome las medidas apropiadas)
  9. Elimine el inicio de sesión

Limpiezas

Ahora que hemos terminado todas nuestras pruebas, podremos limpiar lo que hemos creado. Posteriormente podremos ejecutar el siguiente documento para hacer eso:

Conclusión

Tal como se ha visto a lo largo de este artículo, la tarea a efectuar no es tan sencilla de quitar correctamente un inicio de sesión de SQL Server. Hay muchos pasos a seguir y algunos de estos no son muy simples. Por tanto, podríamos tener que revisar el código de procedimiento y los permisos para hacer lo que queremos realizar.

Sin embargo, hemos identificado las acciones para ser tomadas a partir de un inicio de sesión y de eliminación de usuarios de la base de datos y que además podríamos concluir que estas acciones podrían ser (parcialmente) escritas o puestas en uno o más procedimientos almacenados.

Descargas

Jefferson Elias

Jefferson Elias

Viviendo en Bélgica, obtuve un grado de maestría en Ciencias de la Computación en 2001 en la Universidad de Lieja.

Soy uno de los raros tipos que empezó a trabajar como DBA inmediatamente después de su graduación. Así que, trabajo en el hospital de la universidad de Lieja desde 2011. Inicialmente involucrado en la administración de Bases de Datos Oracle (las cuales aún están bajo mi cargo), tuve la oportunidad de aprender y manejar instancias de SQL Server en 2013. Desde 2013, he aprendido mucho acerca de SQL Server en administración y desarrollo.

Me gusta el trabajo de DBA porque uno necesita tener un conocimiento general en cada campo de IT. Esa es la razón por la que no pararé de aprender (y compartir) los productos de mis aprendizajes.

Ver todas las entradas de Jefferson Elias
Jefferson Elias
Mantenimiento de SQL Server, Relaciones y dependencias, Seguridad de SQL Server security

Acerca de Jefferson Elias

Viviendo en Bélgica, obtuve un grado de maestría en Ciencias de la Computación en 2001 en la Universidad de Lieja. Soy uno de los raros tipos que empezó a trabajar como DBA inmediatamente después de su graduación. Así que, trabajo en el hospital de la universidad de Lieja desde 2011. Inicialmente involucrado en la administración de Bases de Datos Oracle (las cuales aún están bajo mi cargo), tuve la oportunidad de aprender y manejar instancias de SQL Server en 2013. Desde 2013, he aprendido mucho acerca de SQL Server en administración y desarrollo. Me gusta el trabajo de DBA porque uno necesita tener un conocimiento general en cada campo de IT. Esa es la razón por la que no pararé de aprender (y compartir) los productos de mis aprendizajes. Ver todas las entradas de Jefferson Elias

160 Views