Date when User was added to server-level role

Question

Date when User was added to server-level role

Yuri55

SSCrazy Eights

Points: 8449
More actions
June 21, 2023 at 9:52 pm

#4216329

How to get (if possible) date when Login was added to any server-level role? Like temporarily- to sysadmin.
Need to monitor this and drop after N h (do not ask- why?)
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

Have you checked SQL Server audit data?

/*
Generate Server Audit event session to trace action in a database

DBA_ServerAudit

*/

Declare @CreateSession bit = 0;

Declare @DropSession bit = 0 ;

Declare @StartSession bit = 0 ;

Declare @DoNotImportData bit = 0;

Declare @Debug bit = 1 ;


if @CreateSession = 1
begin
---
set nocount on
Declare @Today char(8) = convert(char(8), getdate(),112 )

-- Select @TargetDbid
Declare @TraceFileName Nvarchar(1000)
set @TraceFileName = 'C:\temp\DBA_ServerAudit_' + replace(replace(replace(convert(char(13),getdate(),121),'-',''),' ','_'),':','') + '_' -- + '.trc' wordt automatisch toegevoegd

declare @LogFolderName nvarchar(245) 

SET @LogFolderName = ''
/*
* Get SQLServer Errorlog path
*/
Create table #tmpRegValues ([Value] varchar(50), [Data] varchar(1000))
insert into #tmpRegValues 
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters' 

Select @LogFolderName = substring(Data, 3,datalength(Data) - charindex('\',reverse(Data)) - 2) 
from #tmpRegValues 
where Data like '-e%'

DROP TABLE #tmpRegValues

--select @TraceFileName = replace(@TraceFileName,'C:\temp',@LogFolderName)
select @TraceFileName = @LogFolderName ;

Print '@TraceFileName ' + @TraceFileName ;

Declare @DDL nvarchar(max) = N'
if exists (Select *
from sys.server_audits AS XS
WHERE XS.name = ''DBA_ServerAudit''
)
begin
ALTER SERVER AUDIT [DBA_ServerAudit] WITH (STATE = OFF);  
DROP SERVER AUDIT [DBA_ServerAudit];
DROP SERVER AUDIT SPECIFICATION [DBA_ServerAudit_Specification] ;
print ''SERVER Audit [DBA_ServerAudit] dropped'';
end

CREATE SERVER AUDIT DBA_ServerAudit  
    TO FILE ( FILEPATH =N'''+@TraceFileName+''' 
,MAXSIZE = 25 MB
,MAX_ROLLOVER_FILES = 5
,RESERVE_DISK_SPACE = OFF )
 WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)


Print ''ServerAudit SESSION [DBA_ServerAudit] created'';

/*Creates a server audit specification called "DBA_ServerAudit_Specification" that audits SERVER_ROLE_MEMBER_CHANGE_GROUP for the SQL Server audit "DBA_ServerAudit" created above.  
*/  

CREATE SERVER AUDIT SPECIFICATION DBA_ServerAudit_Specification  
FOR SERVER AUDIT DBA_ServerAudit  
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);  
GO  
-- Enable the audit.   
ALTER SERVER AUDIT DBA_ServerAudit  
    WITH (STATE = ON);  
GO


'
if @Debug = 1 
begin
Select convert(xml, @DDL ) as DDL ;
end

exec sp_executesql @stmt = @DDL ;

end
else if @DropSession = 1 
begin
Declare @DropDDL nvarchar(max) = N'
if exists (Select *
from sys.server_audits AS XS
WHERE XS.name = ''DBA_ServerAudit''
)
begin
ALTER SERVER AUDIT [DBA_ServerAudit] WITH (STATE = OFF);  
DROP SERVER AUDIT [DBA_ServerAudit];
DROP SERVER AUDIT SPECIFICATION [DBA_ServerAudit_Specification] ;
print ''SERVER Audit [DBA_ServerAudit] dropped'';
end'
if @Debug = 1 
begin
Select convert(xml, @DropDDL ) as DropDDL
end

exec sp_executesql @stmt = @DropDDL ;

end

else

begin

-- Process Server Audit results

SET NOCOUNT ON;
if @DoNotImportData = 0
begin
DECLARE @AuditFilename [NVARCHAR](500)
, @RwCount int ;

/* get session target information */
SELECT top (1) @AuditFilename = SFA.log_file_path + N'DBA_ServerAudit_*.sqlaudit'
FROM   sys.server_audits AS SA 
INNER JOIN sys.server_file_audits SFA
on SFA.audit_id = SA.audit_id
WHERE  SA.name = 'DBA_ServerAudit'
ORDER BY SA.name ;

Set @RwCount = @@rowcount ;

if @RwCount > 0 
begin
-- Check the audit for the filtered content  
SELECT  * 
FROM fn_get_audit_file(@AuditFilename,default,default)
where action_id like '%PRL'
order by event_time desc ;  
end
Else
begin
Select 'No Audit files found' Remark
end
END

end