question

ZahidNadeem-8256 avatar image
0 Votes"
ZahidNadeem-8256 asked ZahidNadeem-8256 commented

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

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ZahidNadeem-8256,

Any update for this thread ? Did the repose(s) could help you?

0 Votes 0 ·

Hi @ZahidNadeem-8256
,
Any update for this thread?

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ZahidNadeem-8256 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

I have configured log reader agent "run under the SQL server agent service account", I mean that windows user(domain\abc) is not being used in any configuration, then why is it shown executing sp_replcmds ?

Thanks

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ZahidNadeem-8256 commented

Hi @ZahidNadeem-8256

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Cathyji-msft,

Thanks for looking into it. Yes the user is db_owner but he is not executing sp.
Actually I have created a logs table, saving output of above query (which I posted in my original post) in that table after every 15 seconds. Just to keep an eye on queries running on the server.

While looking into it I got few instances where the user is shown executing that replcmd sp but user says that he is not executing that.
He is some senior resource , I can rely on him.
Could it be that output of 2 queries running simultaneously merged & shown like that.

Any thoughts.

Thanks once again.



0 Votes 0 ·