Infrastructure at your Service

Stéphane Haby

SQL Server Tips: Orphan database user but not so orphan…

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.


After running the query, I find one user (that I renamed dbi_user to anonymize my blog).
I try to drop the user….


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


And now, I have only the stored procedure with the execution context of my user dbi_user.
After that, I copy the value of the definition column to see the code


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 😎

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant