lunes, 2 de noviembre de 2020

No se puede borrar usuario en una base de datos

 Buenas tardes queridos lectores, feliz día de muertos, el día de hoy les mostraré como borrar un usuario de la base de datos que se resiste a ser eliminado.

Los escenarios pueden ser diversos, pero en el escenario que a mi me sucedió fue que restaure una base de datos en otro servidor, lo que me ocasionó usuarios huérfanos, por lo cual, me disponía a borrar y volver a crear

ese usuario cuando me marcó el error, veamos algo de teoría primero.



Usuarios en SQL SERVER


La versión que utilizo para el ejemplo es SQL SERVER 2014, así que en versiones anteriores a esa, debe de funcionar los ejemplos.

Un usuario de SQL SERVER tiene que estar enlazado a un login, mientras que el login controla los permisos de la instancia, el usuario controla los permisos de la base de datos. En este enlace puede saber más sobre los usuarios y los logins https://www.respuestasit.com.mx/2020/06/logins-y-usuarios-seguridad-en-sqlserver.html.

Vamos a crear un login llamado admin y después vamos a crear su usuario asociado en la base de datos prueba

USE [master]

GO

CREATE LOGIN [admin] WITH PASSWORD=N'.', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO


USE [prueba]

GO

CREATE USER [leur] FOR LOGIN [admin]

GO

USE [prueba]

GO

ALTER ROLE [db_owner] ADD MEMBER [leur]

GO


Ahora vamos a crear un procedimiento que se ejecute explícitamente con ese usuario.

use prueba

go

CREATE PROCEDURE dbo.USP_DBA_Ejemplo

with execute as 'leur'

AS

print 'Procedure de Prueba'

GO

EXEC dbo.USP_DBA_Ejemplo


Ahora vamos a intentar eliminar al usuario



Y nos aparece el siguiente mensaje de error:

Msg 15136, Level 16, State 1, Line 17
La entidad de seguridad de base de datos está establecida como el contexto de ejecución de uno o varios procedimientos, funciones o notificaciones de eventos y no se puede quitar



¿Porqué nos aparece ese mensaje de error?

Bueno, ese mensaje de error nos aparece debido a que existe algún procedimiento, función, evento o lo que sea que este ligado a ese usuario, en nuestro caso sabemos perfectamente cuál es, pues lo acabamos de crear y solamente es uno, el problema empieza cuando son demasiados sps, funciones u otros objetos que dependen del usuario, pero

¿Cómo saber que objeto es el que depende del usuario?

Muy buena pregunta, para eso vamos a ejecutar algunos querys, para empezar, necesitamos saber el id del usuario

use prueba

go

 

select * from sys.sysusers

where name = 'leur'



Como podemos observar el id del usuario leur es el numero 5, entonces procederemos a ejecutar las siguientes consultas

select user_name(execute_as_principal_id) 'execute as user', OBJECT_NAME(object_id), * from sys.system_sql_modules where execute_as_principal_id = 5

select user_name(execute_as_principal_id) 'execute as user',  OBJECT_NAME(object_id),* from sys.service_queues where execute_as_principal_id = 5

select user_name(execute_as_principal_id) 'execute as user', OBJECT_NAME(object_id), * from sys.assembly_modules where execute_as_principal_id = 5

select user_name(execute_as_principal_id) 'execute as user', OBJECT_NAME(object_id), * from sys.sql_modules where execute_as_principal_id = 5

select user_name(execute_as_principal_id) 'execute as user',  OBJECT_NAME(object_id),* from sys.server_assembly_modules where execute_as_principal_id = 5

select user_name(execute_as_principal_id) 'execute as user',  OBJECT_NAME(object_id),* from sys.server_sql_modules where execute_as_principal_id = 5

Ejecutamos una por una esas consultas y nos irá arrojando los objetos que dependen de ese usuario, en el caso del sp yo lo encontré en esta consulta:

select user_name(execute_as_principal_id) 'execute as user', OBJECT_NAME(object_id), * from sys.sql_modules where execute_as_principal_id = 5



Como podemos observar, esa consulta nos dice que objeto es el que depende ese usuario, solo hay el modificar el sp para cambiar el usuario.

Una vez eliminado el o los objetos que dependen del usuario, lo podremos eliminar con normalidad sin ningún tipo de problema.

Hasta aquí el post queridos lectores, espero haya sido de su agrado y utilidad, los invito a que compartan, comenten o pregunten, si pueden dar un clic a algún anuncio se los agradeceré mucho.

Saludos










No hay comentarios.:

Publicar un comentario

LOG Shipping (Transvase de Registros) en SQL SERVER

 Log Shipping es una técnica de alta disponibilidad y recuperación de desastres que nos permitirá, restaurar una base de datos almacenada en...