sp_replcmds executing under non admin user

Zahid Butt 556 Reputation points
2021-10-26T15:55:02.667+00:00

Hi,

I have transactional replication running successfully.
Just a question: sp_replcmds is shown executing under a non admin account while I run below query:

SELECT distinct SPID = er.session_id
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,ObjectName = OBJECT_NAME(st.objectid)
,CPUTime = er.cpu_time
,StartTime = er.start_time
,TimeElapsed = case when GETDATE()<er.start_time then '00:00:00.0000000' else CAST(GETDATE() - er.start_time AS TIME) end
,SQLStatement = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE st.text IS NOT NULL

I am the admin & I have setup replication myself then why is it show under another user.
Any thoughts please.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-26T16:16:43.033+00:00
  2. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-10-27T03:27:49.467+00:00

    Hi @Zahid Butt ，

    sp_replcmds is used by the log reader process in transactional replication. Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_replcmds. Refer to MS document sp_replcmds (Transact-SQL).

    Did the non admin user is a member of the db_owner fixed database role?

    We can using below T-SQL to check database roles in SQL Server for a user. 

    Use DBName  
  
SELECT r.name role_principal_name, m.name AS member_principal_name  
  
FROM sys.database_role_members rm   
  
JOIN sys.database_principals r   
  
    ON rm.role_principal_id = r.principal_id  
  
JOIN sys.database_principals m   
  
    ON rm.member_principal_id = m.principal_id  
  
where m.name = 'Cathy'

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.