Listing Server Credentials and Where They Are Used

Server credentials hide their secrets, but they still show you who depends on them. A few catalog queries can connect a credential to its logins, its Agent proxies and the job steps behind them. Do that before anyone rotates a password.

Why you check before a password changes

Picture the request: “Security will rotate the password of a service account tonight. Is that safe?” Your server has a credential named after that account. But who uses it? If you do not know, the answer shows up at 2 AM, when a nightly job fails with a login error.

Let me build a small web of dependencies so you can see the queries return real rows. The demo creates server-level objects: two credentials, a login, an Agent proxy and a job. It removes all of them in the last block.

Create a small web of dependencies

The first credential gets a login. The second one is a spare that nothing uses. The identity is your own Windows account, because an Agent proxy only accepts a real Windows user. The secrets are made-up text. Connect with Windows authentication for this demo.

DECLARE @Sql nvarchar(max) =
    N'CREATE CREDENTIAL DemoCredential WITH IDENTITY = N''' + SUSER_SNAME() + N''', SECRET = N''Made up secret 1'';';
EXEC (@Sql);
SET @Sql =
    N'CREATE CREDENTIAL DemoSpareCredential WITH IDENTITY = N''' + SUSER_SNAME() + N''', SECRET = N''Made up secret 2'';';
EXEC (@Sql);

CREATE LOGIN DemoLogin WITH PASSWORD = N'Made-Up-Pw-2026-Aa1', CHECK_POLICY = OFF, CREDENTIAL = DemoCredential;

Now a proxy built on the first credential, and a disabled job whose step runs under that proxy. The job has no schedule and never runs.

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'DemoProxy', @credential_name = N'DemoCredential', @enabled = 1;
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'DemoProxy', @subsystem_name = N'CmdExec';

EXEC msdb.dbo.sp_add_job @job_name = N'DemoJob', @enabled = 0;
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoJob', @step_name = N'Copy the nightly file',
     @subsystem = N'CmdExec', @command = N'echo demo', @proxy_name = N'DemoProxy';

List the credentials without any secrets

Start with the credentials themselves. The first query lists the columns of the catalog view, and none of them holds a secret. The second shows both demo credentials with their dates. The third covers database-scoped credentials, which live inside each database. It is empty here because the demo makes none.

SELECT name FROM sys.all_columns WHERE object_id = OBJECT_ID(N'sys.credentials') ORDER BY column_id;

SELECT credential_id, name, credential_identity, create_date, modify_date
FROM sys.credentials ORDER BY name;

SELECT name, credential_identity, create_date, modify_date
FROM sys.database_scoped_credentials ORDER BY name;

The names and identities are not secret, but treat the output as private anyway. It tells an attacker which accounts matter.

Follow each credential to logins and proxies

Now connect credentials to what uses them. The first query joins to logins. The second reads the mapping catalog, which also lists logins tied to an external key provider. The third joins to Agent proxies. I use LEFT JOIN so a credential without a visible user still appears.

SELECT c.name AS CredentialName, p.name AS LoginName
FROM sys.credentials AS c
LEFT JOIN sys.server_principals AS p ON p.credential_id = c.credential_id
ORDER BY c.name, p.name;

SELECT c.name AS CredentialName, p.name AS LoginName
FROM sys.server_principal_credentials AS m
JOIN sys.credentials AS c ON c.credential_id = m.credential_id
JOIN sys.server_principals AS p ON p.principal_id = m.principal_id
ORDER BY c.name, p.name;

SELECT c.name AS CredentialName, p.name AS ProxyName, p.enabled
FROM sys.credentials AS c
LEFT JOIN msdb.dbo.sysproxies AS p ON p.credential_id = c.credential_id
ORDER BY c.name, p.name;

DemoCredential shows DemoLogin and DemoProxy. DemoSpareCredential shows NULL in both places. Do not read that as “safe to delete”. Other features, such as a backup to a URL, call a credential by name, and these joins cannot see them. Search your jobs and scripts before you drop one.

Find the job steps behind a proxy

The proxy is only the middle of the chain. The people who feel a failure are the owners of the jobs. This query lists every step that runs under a proxy, including the step name and subsystem but not the command text. Notice JobEnabled is 0. I keep disabled jobs in the list, because someone may switch one on during a recovery.

SELECT c.name AS CredentialName, p.name AS ProxyName, j.name AS JobName,
       j.enabled AS JobEnabled, s.step_id, s.step_name, s.subsystem
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
JOIN msdb.dbo.sysproxies AS p ON p.proxy_id = s.proxy_id
JOIN sys.credentials AS c ON c.credential_id = p.credential_id
ORDER BY c.name, j.name, s.step_id;

Rotate, then test as the real identity

After you have the map, talk to the owner of the outside account and agree on the change. A new modify_date only tells you the metadata changed. It does not tell you the login works. Run a harmless test as the real identity, and know your way back before you start.

Now remove everything the demo created.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoJob';
EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'DemoProxy';
DROP LOGIN DemoLogin;
DROP CREDENTIAL DemoCredential;
DROP CREDENTIAL DemoSpareCredential;

Keep the dependency map next to the rotation plan, and the 2 AM page stays quiet.

A credential list is not a secret list, it is a map of who depends on what.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.