英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论