SQL SERVER – Account Added Automatically in SQL Server Used by SharePoint

A client kept removing an account from the security roles in their SharePoint databases. It kept coming back, added automatically. The account belonged to someone who had left the company. It turned out to be SharePoint itself. At the time I found that with a Profiler trace. Here is how I would catch it today, tested on SQL Server 2025, with the answer printed in plain words.

The Situation

The account kept reappearing in roles inside three databases: SharedServices_Search_SSPDB, SharedServices_SSPDB and SharePoint_Config. Someone removed it, and some time later it was back.

The trace showed a SharePoint service adding it back on a schedule. The most likely story is that the former employee had used their own account to set up the SharePoint Shared Service Provider. SharePoint remembered that account as the one it was configured with, and something it ran on a schedule kept making sure that account still had the access it expected.

Catching It Today

Profiler is deprecated. SQL Server Audit does the same job with far less overhead. And one server-level specification watches every database at once. This is what I ran:

USE master;
GO
CREATE SERVER AUDIT role_audit
TO FILE (FILEPATH = 'C:\SQLAudit\');
ALTER SERVER AUDIT role_audit WITH (STATE = ON);

CREATE SERVER AUDIT SPECIFICATION role_spec
FOR SERVER AUDIT role_audit
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

DATABASE_ROLE_MEMBER_CHANGE_GROUP records every time anyone is added to or removed from a database role, in every database on the server. That is exactly the event we were hunting.

To test it, I made a login, gave it a user in a test database, and added it to db_owner. Then I read the audit file:

SELECT CONVERT(varchar(19), DATEADD(MINUTE,
           DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME()), event_time), 121) AS local_time,
       database_name,
       object_name                     AS role_name,
       target_database_principal_name  AS added,
       server_principal_name           AS who,
       application_name
FROM   sys.fn_get_audit_file('C:\SQLAudit\role_audit*.sqlaudit', DEFAULT, DEFAULT)
WHERE  action_id = 'APRL';

local_time           database_name  role_name  added        who        application_name
2026-09-22 23:22:29  BlogLab        db_owner   LeaverLogin  CORP\dba   SQLCMD

Look at the last two columns. who is the account that made the change. application_name is the program it came from. Mine said SQLCMD, because that is what I used. On my client’s server, the same row would have named the SharePoint service account and the SharePoint program that made the change. One query, and no trace to set up and read through.

Note that the audit file stores times in UTC, which is why the query converts them. APRL is the action code for “add member to role”.

Where Else Does the Account Live?

Before you decide what to do, find every database the login has a user in, and its roles there. This loops through every online database:

DECLARE @sid varbinary(85) = SUSER_SID(N'DOMAIN\FormerEmployee');
DECLARE @out TABLE (database_name sysname, user_name sysname, role_name sysname NULL);
DECLARE @db sysname, @sql nvarchar(max);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases WHERE state_desc = 'ONLINE';
OPEN c; FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N';
        SELECT DB_NAME(), u.name, r.name
        FROM sys.database_principals u
        LEFT JOIN sys.database_role_members m ON m.member_principal_id = u.principal_id
        LEFT JOIN sys.database_principals r ON r.principal_id = m.role_principal_id
        WHERE u.sid = @sid';
    INSERT @out EXEC sp_executesql @sql, N'@sid varbinary(85)', @sid = @sid;
    FETCH NEXT FROM c INTO @db;
END
CLOSE c; DEALLOCATE c;

SELECT * FROM @out;

For my test login it returned one row: BlogLab, db_owner. It matches on the login’s SID rather than on the user name, because a database user does not have to be named after its login.

What to Actually Do About It

Removing the account from SQL Server alone does not work, as my client found out. SharePoint puts it back. The fix belongs on the SharePoint side. Use SharePoint’s own tools to move its farm and service accounts to dedicated service accounts, so it stops treating a person’s account as part of its configuration. Then remove the old account from SQL Server. This time it should stay gone.

My suggestion at the time was to disable the account in Active Directory. SharePoint could keep granting it rights, but nobody could log in with it. That is still a sensible first step, because it closes the risk the same day. But it leaves a dead account’s name sitting in your database roles. Treat it as a stopgap until SharePoint is reconfigured.

The lesson is older than SharePoint. Never install or configure a product with your own account. Products remember who set them up, and people leave.

The account is not coming back by itself, it is being put back by the product it once set up.

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