Beginning of this year, it is good to clean up orphan users in SQL Server databases.
Even if this practice must be done regularly throughout the year of course. 😉
During my cleaning day, a new case appears that I never had before and enjoy to share it with you.
To find orphan database-users, I use this query:
SELECT *FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid WHERE b.sid IS NULL AND a.type In ('U', 'G') AND a.principal_id > 4
This query for orphan users is focussed on Windows Logins or Groups and not SQL Logins.
I’m not lucky! As you can see in the screenshot above, I have an error message:
Msg 15136, Level 16, State 1, Line 4
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped
What does this message means?
In my database, this user is used as execution context (EXECUTE AS) in stored procedures, functions or event notifications.
I need to find now, where this user is used.
For that, I will use the DMV sys.sql_modules combined with sys.database_principals:
Select sqlm.object_id, sqlm.definition, dp.principal_id,dp.name from sys.sql_modules sqlm join sys.database_principals dp on sqlm.execute_as_principal_id=dp.principal_id
In my case, I find one stored procedure linked to my user.
To have a good answer for my query, I add a clause where to eliminate these cases:
- execute_as_principal_id= NULL –> EXECUTE AS CALLER
- execute_as_principal_id=-2 –> execute as owner
- execute_as_principal_id=1 –> execute as dbo
- execute_as_principal_id=8 –> execute as AllSchemaOwner in SSISDB if needed
My new query will be this one:
Select sqlm.object_id, sqlm.definition, dp.principal_id,dp.name from sys.sql_modules sqlm join sys.database_principals dp on sqlm.execute_as_principal_id=dp.principal_id where sqlm.execute_as_principal_id is not null and sqlm.execute_as_principal_id!=-2 and sqlm.execute_as_principal_id!=1
As you can see my user dbi_user is not explicitly specified in the Execute as.
The stored procedure uses execute as self and if I search the user name in the definition column like this query below, I will never find the user:
Select sqlm.object_id, sqlm.definition, dp.principal_id,dp.name from sys.sql_modules sqlm join sys.database_principals dp on sqlm.execute_as_principal_id=dp.principal_id where sqlm.definition like '%dbi_user%'
You can also use the store procedure sp_MSforeachdb to find all “special users” used in modules:
exec sp_MSforeachdb N'select ''?'',sqlm.object_id, sqlm.definition, dp.principal_id,dp.name from [?].sys.sql_modules sqlm join [?].sys.database_principals dp on sqlm.execute_as_principal_id=dp.principal_id where execute_as_principal_id is not null and execute_as_principal_id!=-2 and execute_as_principal_id!=1'
What can I do now?
The only thing to do is to contact the owner of this SP and see with him what to do.
In the Microsoft documentation about Execute AS, you can read:
“If the user is orphaned (the associated login no longer exists), and the user was not created with WITHOUT LOGIN, EXECUTE AS will fail for the user.”
This means that this Stored Procedure will fail if it is used…
I hope this blog can help you 😎