删除 SSIS 代理凭据

huangapple go评论54阅读模式
英文:

Drop SSIS Proxy Credential

问题

我正在尝试使用以下查询删除凭据:

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

但我收到了一个错误消息:

> Msg 15541, Level 16, State 1, Line 17 无法删除凭据
> 'SSISProxyCredentials_ABC',因为它被服务器主体使用。

我尝试手动删除,从sys.credentials删除,但仍然出现错误。

有人知道如何删除这个凭据吗?

谢谢

英文:

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

答案1

得分: 1

你看到的错误是因为一个 SQL Server 主体正在使用你尝试删除的凭据。你需要首先找出哪个服务器主体(或主体)正在使用这个凭据,然后在删除凭据之前解除关联。

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'

在获得正在使用你的凭据的服务器主体名称后,你需要使用以下脚本从服务器主体中删除凭据:

ALTER LOGIN [principal_name] WITH CREDENTIAL = NULL

然后你可以删除凭据:

DROP CREDENTIAL [SSISProxyCredentials_ABC]

不要忘记你需要具有足够权限的用户(通常是 sysadmin 角色)。在更改服务器主体的设置时,请小心,因为这可能会影响依赖于该主体的应用程序的功能。

英文:

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.

huangapple
  • 本文由 发表于 2023年7月27日 20:03:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/76779546.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定