Search code examples
azureazure-sql-databaseazure-purview

How do you drop an Azure SQL scoped credential left behind by Microsoft Purview that says it is in use?

After scanning a SQL Azure database with Microsoft Purview and then deleting Microsoft Purview later in the day, it left behind a scoped credential and a user that I cannot drop from my database.

I created the Microsoft Purview instance I called YatesPurview, register my SQL Azure resource with it and then added the user as directed in Microsoft documenation using this SQL:

Create user YatesPurview FROM EXTERNAL PROVIDER
GO
EXEC sp_addrolemember 'db_owner', YatesPurview 
GO
Create master key  
Go

I was then able to scan the database. Afterwards, a scoped credential appeared in the database and was associated with the YatesPurview user I created.

Finally, I delete the Microsoft Purview resource at the end of the day in the Azure portal and then attempted to drop the YatesPurview user from my database about 12 hours later.

  • Command: DROP USER YatesPurview
  • Result: The database principal owns an database scoped credential and cannot be dropped.

I then queried for the scoped credential using select * from sys.database_scoped_credentials and then using its name I attempted to drop the scoped credential

  • Command: DROP DATABASE SCOPED CREDENTIAL [https://scanwestus2plxntat.blob.core.windows.net/7fce139c-644d-4bc9-be56-5fd9a2decdd2]
  • Result: Cannot drop the credential 'https://scanwestus2plxntat.blob.core.windows.net/7fce139c-644d-4bc9-be56-5fd9a2decdd2' because it is being used.

I dropped the Microsoft Purview resource yesterday, so it's been over 12 hours and it is still "being used".

Does anyone have any suggestion on how to get rid of this short of dropping the database and recreating it?

----Update July 23, 2022----

As per Victor's suggestion below that I've marked as the correct answer, I did the following. enter image description here

  1. I stopped the session
  2. Deleted the session
  3. Dropped the scoped credentials with the SQL listed above.
  4. Dropped the user.
Solution

  • Check the extended event sessions active on the database. Purview creates a session to track events, and it is bind with the credential.

    Purview extended event session

    It is not needed to remove it, just stop it and then try to remove the credential. It works for me :)