User Validation

[A] – Table Schema to be needed

If not exists(Select 1 from sysobjects where Xtype = 'U' and Name = 'DB_Owner_Mapping')

Begin

CREATE TABLE [dbo].[DB_Owner_Mapping](

[DBName] [nvarchar](255) NULL,

[Team] [nvarchar](255) NULL,

[Reporting_Officer] [nvarchar](255) NULL,

[RO_Display_Name] [nvarchar](255) NULL,

[RO Email Id] [nvarchar](255) NULL,

[DBA_Mapping_Name] [nvarchar](255) NULL,

[DBA_Mapping_email] [nvarchar](255) NULL

) ON [PRIMARY]

End

If not exists(Select 1 from sysobjects where Xtype = 'U' and Name = 'User_Master')

Begin

CREATE TABLE [dbo].[User_Master](

[Rowid] [int] IDENTITY(1,1) NOT NULL,

[User_Id] [varchar](50) NULL,

[User_Name] [varchar](100) NULL,

[Team] [varchar](100) NULL,

[Reporting_Officer] [varchar](100) NULL,

[Account_Type] [varchar](50) NULL,

[Mod_Date] [datetime] NULL,

[User_Status] [varchar](100) NULL,

[Email_id] [varchar](255) NULL,

[User_Alias] [varchar](255) NULL,

[TL] [varchar](100) NULL,

[Resigned_Status] [bit] Default(0),

CONSTRAINT [PK__User_Mas__FFE968E92AC04CAA] PRIMARY KEY CLUSTERED

(

[Rowid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

End

[B] – SP for extracting user details

-- SP Detail : List of users from all databases

-- ======================================================================

-- Exec USP_UserList_All_DB 'ALL'

-- Exec USP_UserList_All_DB 'Login id'

Create Proc [dbo].[USP_UserList_All_DB]

(

@UserName Varchar(100)

)

BEGIN

If not exists(Select 1 from sysobjects where Xtype = 'U' and Name = 'User_Details')

Begin

CREATE TABLE [dbo].[User_Details](

[DBName] [nvarchar](128) NULL,

[DATABASE_ROLE] [sysname] NOT NULL,

[DATABASE_USER] [sysname] NOT NULL,

[TYPE_DESC] [nvarchar](60) NULL,

[CREATE_DATE] [datetime] NOT NULL,

[Timestamp] datetime Default(Getdate())

) ON [PRIMARY]

End

Truncate table User_Details

DECLARE @command varchar(8000)

SELECT @command = 'USE [?] INSERT INTO [DBA_ADMIN].[dbo].[User_Details]

([DBName]

,[DATABASE_ROLE]

,[DATABASE_USER]

,[TYPE_DESC]

,[CREATE_DATE])

SELECT DB_NAME() As DBName,rp.NAME AS DATABASE_ROLE, mp.NAME AS DATABASE_USER, mp.TYPE_DESC,mp.CREATE_DATE

from sys.database_role_members drm

join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)

join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)

order by rp.name '

EXEC sp_MSforeachdb @command

If (ISNULL(@UserName,'')=''OR @UserName = 'ALL')

Select * from [User_Details] Order By DATABASE_USER,Database_Role

Else

Select * from [User_Details] Where DATABASE_USER = @UserName Order By DBName,DATABASE_USER,Database_Role

END

Print 'Select * from [User_Details] Order By DATABASE_USER,Database_Role '

[C] – SQL Job for notification

Step-I- Call above SP

Exec USP_UserList_All_DB 'ALL'

Step-II – Notification code

Declare @SR_No Int, @SR_No2 Int, @DBName Varchar(100),@Team Varchar(100),@Reporting_Officer Varchar(100),@RO_Display_Name Varchar(100),@RO_Email_Id Varchar(100),@DBA_Mapping_Name Varchar(100),@DBA_Mapping_email Varchar(100)

Declare @DATABASE_ROLE Varchar(100),@DATABASE_USER Varchar(100),@TYPE_DESC Varchar(100),@CREATE_DATE Datetime

SET NOCOUNT ON

-- Notification pattern

Declare @Head_Cl Varchar(10),@Grid_Cl_En Varchar(10),@Grid_Cl_Od Varchar(10),@Grid_Cl_Sc Varchar(10),@Grid_Cl_Fl Varchar(10),@Used Int, @Free Int,@Used_Space Int

Select @Head_Cl = Header_Color,@Grid_Cl_En = Grid_Color_Even,@Grid_Cl_Od = Grid_Color_Odd,@Grid_Cl_Sc = Success,@Grid_Cl_Fl = Failed

From dbo.Notification_Color_Pattern

Where Month_Id = DATEPART(M,GETDATE())

Declare @profile_name_P Varchar(100)

Select @profile_name_P = Name from Vw_DB_Profile

Declare @Body1 Varchar(MAX),@Subj Varchar(500),@Server Varchar(100),@Clr bit, @T_Head Varchar(MAX)

Declare @Today Date, @Rank Tinyint ,@Reporting_Officer_Main Varchar(255),@RO_Name_Main Varchar(255),@Team_Main Varchar(255)

Select @Today = CONVERT(Date,Getdate())

Set @T_Head = ''

Set @T_Head = @T_Head + '<table border=4 ><tr><td bgcolor="'+Convert(Varchar(20),@Head_Cl)+'">'

Set @T_Head= @T_Head+'DATABASE_USER '+'</td>'

Set @T_Head= @T_Head+'<td bgcolor="'+Convert(Varchar(20),@Head_Cl)+'">'+'DATABASE_ROLE '+'</td>'

Set @T_Head= @T_Head+'<td bgcolor="'+Convert(Varchar(20),@Head_Cl)+'">'+'TYPE_DESC '+'</td>'

Set @T_Head= @T_Head+'<td bgcolor="'+Convert(Varchar(20),@Head_Cl)+'">'+'CREATE_DATE '+'</td>'

Set @T_Head= @T_Head+'<td bgcolor="'+Convert(Varchar(20),@Head_Cl)+'">'+'REMOVE/RETAIN? '+'</td></tr>'

-- ==================== ==================== ==================== ====================

-- ==================== S T A R T M A I N C U R S O R ====================

-- ==================== ==================== ==================== ====================

Declare CRSR_MAIN Cursor for

Select Distinct [Reporting_Officer], [RO_Display_Name],[Team], [RO Email Id], [DBA_Mapping_Name], [DBA_Mapping_email]+'' FROM DB_Owner_Mapping

open CRSR_MAIN

Fetch next from CRSR_MAIN INTO @Reporting_Officer_Main, @RO_Name_Main, @Team_Main, @RO_Email_Id, @DBA_Mapping_Name, @DBA_Mapping_email

while @@Fetch_Status=0

BEGIN

Select @Subj = '',@Body1 = ''

Select @Subj = 'QUARTELY U?E? VALIDATION'+' ('+UPPER(Convert(Varchar(100),@Team_Main))+') - '+'Q'+Convert(char(1),datepart(qq,GETDATE()))+'-'+Convert(char(4),Year(Getdate()))

Select @Body1 = '<body style="background-color:powderblue;"><td>Hi '+Convert(Varchar(15),@RO_Name_Main)+',</td> </tr>'

Select @Body1 = @Body1 + '<td>Following are the list of ???? having access on following databases on server '+Convert(Varchar(100),@@SERVERNAME)+' </td> '

Select @Body1 = @Body1+' <td>'+UPPER(Convert(Varchar(100),@Team_Main))+' DB LIST:</td></tr> '

-- ==================== S T A R T S U B C U R S O R N O 2 ====================

Declare CRSR_NO_2 Cursor for

Select Row_number() Over(order by [DBName]) As [SR_NO],[DBName], [Team], [Reporting_Officer]

FROM DB_Owner_Mapping

Where [Reporting_Officer] = @Reporting_Officer_Main

And [DBName] In(Select Distinct [DBName] FROM dbo.User_Details A

Where DATABASE_USER Not In('dbo') And Left(DATABASE_USER ,2) != '##' And DATABASE_ROLE != 'DDL_Track')

And [DBName] Not In('master','msdb','model','ReportServer','ReportServerTempDB')

open CRSR_NO_2

Fetch next from CRSR_NO_2 INTO @SR_No,@DBName, @Team, @Reporting_Officer

while @@Fetch_Status=0

BEGIN

Select @Body1 = @Body1+' <td>'+Convert(Varchar(100),@SR_No)+' - '+ UPPER(Convert(Varchar(100),@DBName))+'</td></tr> '

Select @Body1 = @Body1+@T_Head

-- ==================== S T A R T S U B C U R S O R 3 ====================

Declare CRSR__SUB_User Cursor for

Select Row_number() Over(order by [DATABASE_USER],[DATABASE_ROLE]) As [SR_NO], [DATABASE_ROLE]

, Case

when Isnull(B.Account_Type,'') = 'Service Account' Then [DATABASE_USER]+' (Service Account)'

--when Isnull(B.User_Alias,'')='' Then [DATABASE_USER]

When Isnull(B.User_Alias,'') != '' And [DATABASE_USER] not like '%'+B.User_Alias And B.Resigned_Status = 1 Then [DATABASE_USER]+' ('+B.User_Alias+') - RESIGNED'

When Isnull(B.User_Alias,'') != '' And [DATABASE_USER] not like '%'+B.User_Alias And B.Resigned_Status = 0 Then [DATABASE_USER]+' ('+B.User_Alias+')'

Else [DATABASE_USER] END aS [DATABASE_USER], [TYPE_DESC], [CREATE_DATE]

FROM dbo.User_Details A Left Join dbo.User_Master B

On A.DATABASE_USER = B.[User_Id]

Where [DBName] = @DBName

And DATABASE_USER Not In('dbo')

And DATABASE_USER Not like '%AUTHORITY\ANONYMOUS%'

And Left(DATABASE_USER ,2) != '##'

And DATABASE_ROLE != 'DDL_Track'

Order By [DATABASE_USER],[DATABASE_ROLE]

open CRSR__SUB_User

Fetch next from CRSR__SUB_User INTO @SR_No2,@DATABASE_ROLE, @DATABASE_USER,@TYPE_DESC, @CREATE_DATE

while @@Fetch_Status=0

BEGIN

If (@SR_No2%2 = 0)

Begin

If (@DATABASE_USER Like '%RESIGNED')

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+Convert(Varchar(100),@DATABASE_USER)+'</td>'

Else

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+Convert(Varchar(100),@DATABASE_USER)+'</td>'

If (@DATABASE_ROLE = 'db_datawriter' OR @DATABASE_ROLE = 'db_ddladmin' OR @DATABASE_ROLE = 'db_owner')

Else

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+Convert(Varchar(100),@DATABASE_ROLE)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+Convert(Varchar(100),@TYPE_DESC)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+Convert(Varchar(100),@CREATE_DATE)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_En)+'">'+SPACE(1)+'</td></tr>'

End

ELSE

Begin

If (@DATABASE_USER Like '%RE?IGNED')

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+Convert(Varchar(100),@DATABASE_USER)+'</td>'

Else

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+Convert(Varchar(100),@DATABASE_USER)+'</td>'

If (@DATABASE_ROLE = 'db_datawriter' OR @DATABASE_ROLE = 'db_ddladmin' OR @DATABASE_ROLE = 'db_owner')

Else

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+Convert(Varchar(100),@DATABASE_ROLE)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+Convert(Varchar(100),@TYPE_DESC)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+Convert(Varchar(100),@CREATE_DATE)+'</td>'

Set @Body1 = @Body1 + '<td bgcolor="'+Convert(Varchar(20),@Grid_Cl_od)+'">'+SPACE(1)+'</td></tr>'

End

Fetch next from CRSR__SUB_User INTO @SR_No2,@DATABASE_ROLE, @DATABASE_USER,@TYPE_DESC, @CREATE_DATE

END

CLOSE CRSR__SUB_User

DEALLOCATE CRSR__SUB_User

-- ==================== S T O P S U B C U R S O R 3 ====================

Select @Body1 = @Body1+'</table> '

--Fetch next from CRSR_NO_2 INTO @SR_No, @DBName, @Team, @Reporting_Officer, @RO_Display_Name, @RO_Email_Id, @DBA_Mapping_Name, @DBA_Mapping_email

Fetch next from CRSR_NO_2 INTO @SR_No,@DBName, @Team, @Reporting_Officer

END

CLOSE CRSR_NO_2

DEALLOCATE CRSR_NO_2

-- ==================== S T O P S U B C U R S O R N O 2 ====================

--Instruction

Select @Body1 = @Body1+' <td>Action Item for Application team</td></tr> '

Select @Body1 = @Body1+'<td></td></tr>'

Select @Body1 = @Body1+'<td>If you are not the right person to revert on this email then please include the relevent person who can revert and confirm.</td></tr> '

Select @Body1 = @Body1+' <td>Action Item for DB? team ('+CONVERT(Varchar(100),@DBA_Mapping_Name)+')</td></tr> '

Select @Body1 = @Body1+'<td>Please do the timely follow-up on this email and make this task closed.</td></tr> '

Select @Body1 = @Body1+' <td>Thanks & Regards,</td> </tr>'

Select @Body1 = @Body1+'<td>DBA Team</td> </tr>'

Select @Body1 = @Body1+' '

Select @Body1 = @Body1+'<td>Note : System generated mail, you can revert back to DBA team.</td></tr> '

Select @Body1 = @Body1+'</body>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile_name_P,

@recipients=@RO_Email_Id,

@copy_recipients =@DBA_Mapping_email,

@subject = @Subj,

@body = @Body1,

@body_format = 'HTML'

Fetch next from CRSR_MAIN INTO @Reporting_Officer_Main, @RO_Name_Main, @Team_Main, @RO_Email_Id, @DBA_Mapping_Name, @DBA_Mapping_email

END

CLOSE CRSR_MAIN

DEALLOCATE CRSR_MAIN

-- ==================== ==================== ==================== ====================

-- ==================== S T O P M A I N C U R S O R ====================

-- ==================== ==================== ==================== ====================

Long Term Notifications

[A] – Table Schema to be needed

CREATE TABLE [dbo].[Long_Term_Scheduled_Job](

[Jobid] [int] IDENTITY(1,1) Primary Key,

[Job_Name] [varchar](100) NULL,

[Subject] [varchar](255) NULL,

[Body] [varchar](8000) NULL,

[Email_Id] [varchar](255) NULL,

[Cc] [varchar](255) NULL,

[Bcc] [varchar](255) NULL,

[Scheduled_Date] [date] NULL,

[Create_Date] [datetime] DEFAULT (getdate()),

[Execution_Date] [datetime] NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Long_Term_Scheduled_Job]

([Job_Name]

,[Subject]

,[Body]

,[Email_Id]

,[Cc]

,[Bcc]

,[Scheduled_Date]

,[Create_Date]

,[Execution_Date])

Select

'Quartely u??? validation'

,'Quartely u??? validation'

,'Hi Team, As per the ????? abc , kindly generate the u??? details and share the details with application.Once you will get the final list from manager, remove the unwanted access from the databases. Note:-Execute the following query to generate the report. Use ?? Go Exec USP_u???List_All_DB "ALL"'

,Email_Id

,Cc

,Bcc

,Scheduled_Date

,Getdate()

,Execution_Date

from dbo.Long_Term_Scheduled_Job

Where Job_Name = '??'

And Scheduled_Date > Convert(date,GETDATE())

[B] – SP to send notification

Create Proc [dbo].[Long_Term_Scheduled_Job_Notif_Mail]

(

@profile_name_P Varchar(255),

@Recipient_List Varchar(1000)

)

Declare @Today Date

Select @Today = CONVERT(Date,Getdate())

If exists(Select Top 1 [Scheduled_Date] from Long_Term_Scheduled_Job Where [Scheduled_Date] = @Today)

Begin

Declare @Body_Tb Varchar(MAX),@Subj_Tb Varchar(8000),@Email Varchar(500),@CC Varchar(500)

Select @Body_Tb = [Body],@Subj_Tb = [Subject]

from Long_Term_Scheduled_Job Where [Scheduled_Date] = @Today

Declare @Body1 Varchar(MAX),@Subj Varchar(500)

Declare @SR_NO Int,@Category Varchar(255),@DB_Name Varchar(255),@Backup_File_Name Varchar(255),@Backup_Date Date,@Bkp_Size_MB numeric(10,2),@Backup_Status Varchar(255)

Declare CRSR_SCH_Mail Cursor for

Select [Body],[Subject],[Email_Id],CC from Long_Term_Scheduled_Job Where [Scheduled_Date] = @Today

open CRSR_SCH_Mail

Fetch next from CRSR_SCH_Mail INTO @Body_Tb,@Subj_Tb,@Email,@CC

while @@Fetch_Status=0

BEGIN

Select @Subj = @Subj_Tb

Select @Body1 = ''

Select @Body1 = @Body1 + '<td>'+@Body_Tb+' </td> '

Select @Body1 = @Body1+' <td>Thanks & Regards,</td> </tr>'

Select @Body1 = @Body1+'<td>??? Team</td> </tr>'

Select @Body1 = @Body1+' '

Select @Body1 = @Body1+'<td>Note : System generated mail, For any concern revert back to ??? team (????? ).</td></tr>'

Print 'Notification code'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile_name_P,

@recipients= @Email,

@copy_recipients =@CC,

@subject = @Subj,

@body = @Body1,

@body_format = 'HTML'

Fetch next from CRSR_SCH_Mail INTO @Body_Tb,@Subj_Tb,@Email,@CC

END

CLOSE CRSR_SCH_Mail

DEALLOCATE CRSR_SCH_Mail

End

System Proc for File purge

Declare @Dt Date

Select @Dt = DATEADD(day,-8,Getdate())

EXECUTE MASTER.dbo.xp_delete_file 0,

N'E:\backup_Test\Test_Folder\',N'bak',@Dt

16down voteaccepted

Xp_delete_file take five parameters:

1. File Type = 0 for backup files or 1 for report files.

2. Folder Path = The folder to delete files. The path must end with a backslash "\".

3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

4. Date = The cutoff date for what files need to be deleted.

5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

Note:-In SQL Server 2014 at least, the second parameter (Folder Path) can be a specific filename, which results in only that one file being removed, e.g.: EXEC master.dbo.xp_delete_file 0, 'C:\Backups\SomeDatabaseBackup.bak

https://www.codykonior.com/2015/06/02/experiments-with-master-sys-xp-delete-file/

SQL server Important Links