2

I am trying to drop a credential using this query:

if exists (select * from sys.credentials where name = 'SSISProxyCredentials_ABC')
    drop credential SSISProxyCredentials_ABC

But I am getting an error message saying:

Msg 15541, Level 16, State 1, Line 17 Cannot drop the credential 'SSISProxyCredentials_ABC' because it is used by a server principal.

I have tried to delete manually, to delete from sys.credentials but still getting error.

Does anybody know how can I delete this credential?

Thanks

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Trying to be DBA
  • 25
  • 3
  • Based on the error, it seems something is using it. A credential can be used as `Proxy` which is then used in SQL Agent jobs so you'd have to swap out the credential associated to the proxy and then maybe you've hit all the references? Note: I am also "trying to be a DBA" ;) – billinkc Jul 27 '23 at 15:18

1 Answers1

1

The error you're seeing is because a SQL Server principal is using the credential you are trying to delete. You need first to find out which server principal (or principals) is using this credential and then remove the association before you can delete the credential.

SELECT 
    SP.name AS principal_name,
    SP.type_desc AS principal_type,
    C.name AS credential_name
FROM 
    sys.server_principals SP
JOIN 
    sys.credentials C ON SP.credential_id = C.credential_id
WHERE 
    C.name = 'SSISProxyCredentials_ABC'

After you get the name of the server principal(s) which is using your credential, you have to remove the credential from the server principal using the following script:

ALTER LOGIN [principal_name] WITH CREDENTIAL = NULL

And then you can delete the credential:

DROP CREDENTIAL [SSISProxyCredentials_ABC]

Don't forget that you need user with sufficient permissions (typically a sysadmin role). And please be careful when changing settings for server principals, as it can affect the functionality of your applications that depend on this principal.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60