Replication runs under the SQL Agent account.
sp_replcmds executing under non admin user
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
2 answers
Sort by: Most helpful
-
-
CathyJi-MSFT 21,096 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.