FOR XML could not serialize the data for node because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

Question

FOR XML could not serialize the data for node because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

kulasekhar090

SSC Veteran

Points: 204
More actions
January 11, 2018 at 12:55 am

#325393

FOR XML could not serialize the data for node because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
WHILE RUNNING JOB GETTING THE ABOVE MENTIONED ERROR .
code looks like this , job failing with above mentioned error. Some one pls help me out.
USE [master] GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DBLoginAudit]GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DBLoginAudit]GO/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_DBLoginAudit] GO
USE [master]GO
USE [master]GOUSE [master] GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/SET ANSI_NULLS ONGO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/SET ANSI_NULLS ONGO/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ONGO
SET QUOTED_IDENTIFIER ONGOSET QUOTED_IDENTIFIER ON GO
CREATE procedure [dbo].[usp_DBLoginAudit] AsBegin
CREATE procedure [dbo].[usp_DBLoginAudit] AsBegin CREATE procedure [dbo].[usp_DBLoginAudit] As Begin
/*
/*/*
SQL Server Login Audit
SQL Server Login AuditSQL Server Login Audit
This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately.*/
This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately.*/This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately. */
IF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL    DROP TABLE #Login_Audit
IF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL    DROP TABLE #Login_AuditIF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL     DROP TABLE #Login_Audit
create table #Login_Audit(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
create table #Login_Audit(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))create table #Login_Audit (A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
insert into #Login_Audit select a,b,c,d from(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
insert into #Login_Audit select a,b,c,d from(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;insert into #Login_Audit select a,b,c,d from (select count(name)a from sys.syslogins where name not like '%#%') a, -- total count (select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
DECLARE @tableHTMLA VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLA =    N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +    N'<table border="1">' +    N'<tr><th>Total Count of Login</th>' +    N'<th>Windows User</th>' +    N'<th>SQL server User</th>' +    N'<th>Windows Group</tr>' +    CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,     td = c, ' ' ,     td = d, ' '             FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLA VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLA =    N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +    N'<table border="1">' +    N'<tr><th>Total Count of Login</th>' +    N'<th>Windows User</th>' +    N'<th>SQL server User</th>' +    N'<th>Windows Group</tr>' +    CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,     td = c, ' ' ,     td = d, ' '             FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLA VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit) BEGIN SET @tableHTMLA =     N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +     N'<table border="1">' +     N'<tr><th>Total Count of Login</th>' +     N'<th>Windows User</th>' +     N'<th>SQL server User</th>' +     N'<th>Windows Group</tr>' +     CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,      td = c, ' ' ,      td = d, ' '              FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL    DROP TABLE #Login_Audit_SA
IF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL    DROP TABLE #Login_Audit_SAIF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL     DROP TABLE #Login_Audit_SA
SELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable'when 0 then 'Enable'End INTO #Login_Audit_SAFROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_idWHERE b.role_principal_id = 3 and a.name not like 'NT%'ORDER BY a.name
SELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable'when 0 then 'Enable'End INTO #Login_Audit_SAFROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_idWHERE b.role_principal_id = 3 and a.name not like 'NT%'ORDER BY a.nameSELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable' when 0 then 'Enable' End INTO #Login_Audit_SA FROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id WHERE b.role_principal_id = 3 and a.name not like 'NT%' ORDER BY a.name
DECLARE @tableHTMLB VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA)BEGINSET @tableHTMLB =    N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +    N'<table border="1">' +    N'<tr><th>Login name</th>' +    N'<th>Login Type</th>' +    N'<th>Login Status</tr>' +    CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,     td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLB VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA)BEGINSET @tableHTMLB =    N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +    N'<table border="1">' +    N'<tr><th>Login name</th>' +    N'<th>Login Type</th>' +    N'<th>Login Status</tr>' +    CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,     td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLB VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA) BEGIN SET @tableHTMLB =     N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +     N'<table border="1">' +     N'<tr><th>Login name</th>' +     N'<th>Login Type</th>' +     N'<th>Login Status</tr>' +     CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,      td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL    DROP TABLE #Login_Audit_FxdSrRol
IF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL    DROP TABLE #Login_Audit_FxdSrRolIF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL     DROP TABLE #Login_Audit_FxdSrRol
SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRolFROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_idWHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public'ORDER BY b.name
SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRolFROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_idWHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public'ORDER BY b.name SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRol FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id WHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public' ORDER BY b.name
DECLARE @tableHTMLC VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol)BEGINSET @tableHTMLC =    N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Logins</th>' +    N'<th>Permission Type</th>' +    N'<th>Permission Desc</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,     td = permission_name, ' ',     td = state_desc, ' '             FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLC VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol)BEGINSET @tableHTMLC =    N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Logins</th>' +    N'<th>Permission Type</th>' +    N'<th>Permission Desc</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,     td = permission_name, ' ',     td = state_desc, ' '             FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'DECLARE @tableHTMLC VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol) BEGIN SET @tableHTMLC =     N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +     N'<table border="1">' +     N'<tr><th>Logins</th>' +     N'<th>Permission Type</th>' +     N'<th>Permission Desc</th>' +     N'<th>Status</tr>' +     CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,      td = permission_name, ' ',      td = state_desc, ' '              FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL    DROP TABLE #Login_Audit_DBPerm
IF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL    DROP TABLE #Login_Audit_DBPermIF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL     DROP TABLE #Login_Audit_DBPerm
Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))
Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))
Insert into #Login_Audit_DBPermexec master.dbo.sp_MSforeachdb 'use [?]SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_descFROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''
Insert into #Login_Audit_DBPermexec master.dbo.sp_MSforeachdb 'use [?]SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_descFROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''Insert into #Login_Audit_DBPerm exec master.dbo.sp_MSforeachdb 'use [?] SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc FROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''
DECLARE @tableHTMLD VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm)BEGINSET @tableHTMLD =    N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Login Name</th>' +    N'<th>Permission</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,     td = Perm, ' ',     td = status, ' '             FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLD VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm)BEGINSET @tableHTMLD =    N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Login Name</th>' +    N'<th>Permission</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,     td = Perm, ' ',     td = status, ' '             FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'DECLARE @tableHTMLD VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm) BEGIN SET @tableHTMLD =     N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +     N'<table border="1">' +     N'<tr><th>Database Name</th>' +     N'<th>Login Name</th>' +     N'<th>Permission</th>' +     N'<th>Status</tr>' +     CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,      td = Perm, ' ',      td = status, ' '              FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL    DROP TABLE #Login_Audit1
IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL    DROP TABLE #Login_Audit1IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL     DROP TABLE #Login_Audit1
create table #Login_Audit1(A nvarchar (500),B nvarchar (500)default (''))
create table #Login_Audit1(A nvarchar (500),B nvarchar (500)default (''))create table #Login_Audit1 (A nvarchar (500),B nvarchar (500)default (''))
insert into #Login_Audit1 (A,B) exec sp_validatelogins
insert into #Login_Audit1 (A,B) exec sp_validateloginsinsert into #Login_Audit1 (A,B) exec sp_validatelogins
insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys')  DECLARE @tableHTMLE VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLE =    N'<H1><font color="#000088">Orphan Users</font></H1>' +    N'<table border="1">' +    N'<tr><th>User Name</tr>' +    CAST ( ( SELECT td = a, ' '             FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>' IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL    DROP TABLE #Login_Audit2
insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys')  DECLARE @tableHTMLE VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLE =    N'<H1><font color="#000088">Orphan Users</font></H1>' +    N'<table border="1">' +    N'<tr><th>User Name</tr>' +    CAST ( ( SELECT td = a, ' '             FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>' IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL    DROP TABLE #Login_Audit2insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys') DECLARE @tableHTMLE VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit) BEGIN SET @tableHTMLE =     N'<H1><font color="#000088">Orphan Users</font></H1>' +     N'<table border="1">' +     N'<tr><th>User Name</tr>' +     CAST ( ( SELECT td = a, ' '              FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>'IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL     DROP TABLE #Login_Audit2
create table #Login_Audit2(A nvarchar (500),B nvarchar (500)default (''))
create table #Login_Audit2(A nvarchar (500),B nvarchar (500)default ('')) create table #Login_Audit2 (A nvarchar (500),B nvarchar (500)default (''))
insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
DECLARE @tableHTMLF VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2)BEGINSET @tableHTMLF =    N'<H1><font color="#000088">Database Owners</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Owner</tr>' +     CAST ( ( SELECT td = a, ' ',     td = b, ' '             FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLF VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2)BEGINSET @tableHTMLF =    N'<H1><font color="#000088">Database Owners</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Owner</tr>' +     CAST ( ( SELECT td = a, ' ',     td = b, ' '             FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLF VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2) BEGIN SET @tableHTMLF =     N'<H1><font color="#000088">Database Owners</font></H1>' +     N'<table border="1">' +     N'<tr><th>Database Name</th>' +     N'<th>Owner</tr>' +      CAST ( ( SELECT td = a, ' ',      td = b, ' '              FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
Declare @body varchar(max)Declare @subject1 varchar(max)Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAME
Declare @body varchar(max)Declare @subject1 varchar(max)Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAMEDeclare @body varchar(max) Declare @subject1 varchar(max) Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAME
Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;
Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', , @subject = @subject1, @body = @body , @body_format = 'HTML' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', , @subject = @subject1, @body = @body , @body_format = 'HTML' ;EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', --@recipients='nithya.priya@daimler.com'--@recipients='nithya.priya@daimler.com'--@recipients='nithya.priya@daimler.com', @subject = @subject1, @body = @body , @body_format = 'HTML' ;
End
End

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

kulasekhar090 - Thursday, January 11, 2018 12:55 AM
FOR XML could not serialize the data for node because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
WHILE RUNNING JOB GETTING THE ABOVE MENTIONED ERROR .
code looks like this , job failing with above mentioned error. Some one pls help me out.
USE [master] GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DBLoginAudit]GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_DBLoginAudit]GO/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DBLoginAudit]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_DBLoginAudit] GO
USE [master]GO
USE [master]GOUSE [master] GO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/SET ANSI_NULLS ONGO
/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/SET ANSI_NULLS ONGO/****** Object: StoredProcedure [dbo].[usp_DBLoginAudit]    Script Date: 12/14/2017 08:35:47 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ONGO
SET QUOTED_IDENTIFIER ONGOSET QUOTED_IDENTIFIER ON GO
CREATE procedure [dbo].[usp_DBLoginAudit] AsBegin
CREATE procedure [dbo].[usp_DBLoginAudit] AsBegin CREATE procedure [dbo].[usp_DBLoginAudit] As Begin
/*
/*/*
SQL Server Login Audit
SQL Server Login AuditSQL Server Login Audit
This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately.*/
This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately.*/This query will generate a report in one table having separate details for each task like sql server & datbase roles, orphan users details get separately & orphan logins details separately. */
IF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL    DROP TABLE #Login_Audit
IF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL    DROP TABLE #Login_AuditIF OBJECT_ID('tempdb..#Login_Audit') IS NOT NULL     DROP TABLE #Login_Audit
create table #Login_Audit(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
create table #Login_Audit(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))create table #Login_Audit (A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
insert into #Login_Audit select a,b,c,d from(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
insert into #Login_Audit select a,b,c,d from(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;insert into #Login_Audit select a,b,c,d from (select count(name)a from sys.syslogins where name not like '%#%') a, -- total count (select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user (select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login (select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
DECLARE @tableHTMLA VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLA =    N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +    N'<table border="1">' +    N'<tr><th>Total Count of Login</th>' +    N'<th>Windows User</th>' +    N'<th>SQL server User</th>' +    N'<th>Windows Group</tr>' +    CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,     td = c, ' ' ,     td = d, ' '             FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLA VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLA =    N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +    N'<table border="1">' +    N'<tr><th>Total Count of Login</th>' +    N'<th>Windows User</th>' +    N'<th>SQL server User</th>' +    N'<th>Windows Group</tr>' +    CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,     td = c, ' ' ,     td = d, ' '             FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLA VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit) BEGIN SET @tableHTMLA =     N'<H1><font color="#000088">SQL Server Security Audit Report</font></H1>' +     N'<table border="1">' +     N'<tr><th>Total Count of Login</th>' +     N'<th>Windows User</th>' +     N'<th>SQL server User</th>' +     N'<th>Windows Group</tr>' +     CAST ( ( SELECT td = a, ' ',                     td = b, ' ' ,      td = c, ' ' ,      td = d, ' '              FROM #Login_Audit                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLA = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL    DROP TABLE #Login_Audit_SA
IF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL    DROP TABLE #Login_Audit_SAIF OBJECT_ID('tempdb..#Login_Audit_SA') IS NOT NULL     DROP TABLE #Login_Audit_SA
SELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable'when 0 then 'Enable'End INTO #Login_Audit_SAFROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_idWHERE b.role_principal_id = 3 and a.name not like 'NT%'ORDER BY a.name
SELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable'when 0 then 'Enable'End INTO #Login_Audit_SAFROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_idWHERE b.role_principal_id = 3 and a.name not like 'NT%'ORDER BY a.nameSELECT a.name as Logins, a.type_desc, LoginStatus = case a.is_disabled when 1 then 'Disable' when 0 then 'Enable' End INTO #Login_Audit_SA FROM sys.server_principals a INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id WHERE b.role_principal_id = 3 and a.name not like 'NT%' ORDER BY a.name
DECLARE @tableHTMLB VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA)BEGINSET @tableHTMLB =    N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +    N'<table border="1">' +    N'<tr><th>Login name</th>' +    N'<th>Login Type</th>' +    N'<th>Login Status</tr>' +    CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,     td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLB VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA)BEGINSET @tableHTMLB =    N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +    N'<table border="1">' +    N'<tr><th>Login name</th>' +    N'<th>Login Type</th>' +    N'<th>Login Status</tr>' +    CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,     td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLB VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_SA) BEGIN SET @tableHTMLB =     N'<H1><font color="#000088">Logins with SysAdmin Permission</font></H1>' +     N'<table border="1">' +     N'<tr><th>Login name</th>' +     N'<th>Login Type</th>' +     N'<th>Login Status</tr>' +     CAST ( ( SELECT td = Logins, ' ',                     td = type_desc, ' ' ,      td = LoginStatus, ' '              FROM #Login_Audit_SA                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLB = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL    DROP TABLE #Login_Audit_FxdSrRol
IF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL    DROP TABLE #Login_Audit_FxdSrRolIF OBJECT_ID('tempdb..#Login_Audit_FxdSrRol') IS NOT NULL     DROP TABLE #Login_Audit_FxdSrRol
SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRolFROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_idWHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public'ORDER BY b.name
SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRolFROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_idWHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public'ORDER BY b.name SELECT b.name,a.type,a.permission_name,a.state_desc into #Login_Audit_FxdSrRol FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id WHERE b.name not like '%#%' and b.name not like 'NT%' and b.name <> 'public' ORDER BY b.name
DECLARE @tableHTMLC VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol)BEGINSET @tableHTMLC =    N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Logins</th>' +    N'<th>Permission Type</th>' +    N'<th>Permission Desc</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,     td = permission_name, ' ',     td = state_desc, ' '             FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLC VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol)BEGINSET @tableHTMLC =    N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Logins</th>' +    N'<th>Permission Type</th>' +    N'<th>Permission Desc</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,     td = permission_name, ' ',     td = state_desc, ' '             FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'DECLARE @tableHTMLC VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_FxdSrRol) BEGIN SET @tableHTMLC =     N'<H1><font color="#000088">Instance Level Permission Details</font></H1>' +     N'<table border="1">' +     N'<tr><th>Logins</th>' +     N'<th>Permission Type</th>' +     N'<th>Permission Desc</th>' +     N'<th>Status</tr>' +     CAST ( ( SELECT td = Name, ' ',                     td = Type, ' ' ,      td = permission_name, ' ',      td = state_desc, ' '              FROM #Login_Audit_FxdSrRol                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLC = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL    DROP TABLE #Login_Audit_DBPerm
IF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL    DROP TABLE #Login_Audit_DBPermIF OBJECT_ID('tempdb..#Login_Audit_DBPerm') IS NOT NULL     DROP TABLE #Login_Audit_DBPerm
Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))
Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))Create Table #Login_Audit_DBPerm (dbname varchar(50), Loginname varchar(100), Perm varchar(100), status varchar(100))
Insert into #Login_Audit_DBPermexec master.dbo.sp_MSforeachdb 'use [?]SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_descFROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''
Insert into #Login_Audit_DBPermexec master.dbo.sp_MSforeachdb 'use [?]SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_descFROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''Insert into #Login_Audit_DBPerm exec master.dbo.sp_MSforeachdb 'use [?] SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc FROM sys.database_permissions a INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like ''%#%'' and b.name not like ''NT%'' and b.name not like ''MS%'''
DECLARE @tableHTMLD VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm)BEGINSET @tableHTMLD =    N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Login Name</th>' +    N'<th>Permission</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,     td = Perm, ' ',     td = status, ' '             FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLD VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm)BEGINSET @tableHTMLD =    N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Login Name</th>' +    N'<th>Permission</th>' +    N'<th>Status</tr>' +    CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,     td = Perm, ' ',     td = status, ' '             FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'DECLARE @tableHTMLD VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit_DBPerm) BEGIN SET @tableHTMLD =     N'<H1><font color="#000088">Database Level Permission Details</font></H1>' +     N'<table border="1">' +     N'<tr><th>Database Name</th>' +     N'<th>Login Name</th>' +     N'<th>Permission</th>' +     N'<th>Status</tr>' +     CAST ( ( SELECT td = DBNAME, ' ',                     td = Loginname, ' ' ,      td = Perm, ' ',      td = status, ' '              FROM #Login_Audit_DBPerm                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLD = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL    DROP TABLE #Login_Audit1
IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL    DROP TABLE #Login_Audit1IF OBJECT_ID('tempdb..#Login_Audit1') IS NOT NULL     DROP TABLE #Login_Audit1
create table #Login_Audit1(A nvarchar (500),B nvarchar (500)default (''))
create table #Login_Audit1(A nvarchar (500),B nvarchar (500)default (''))create table #Login_Audit1 (A nvarchar (500),B nvarchar (500)default (''))
insert into #Login_Audit1 (A,B) exec sp_validatelogins
insert into #Login_Audit1 (A,B) exec sp_validateloginsinsert into #Login_Audit1 (A,B) exec sp_validatelogins
insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys')  DECLARE @tableHTMLE VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLE =    N'<H1><font color="#000088">Orphan Users</font></H1>' +    N'<table border="1">' +    N'<tr><th>User Name</tr>' +    CAST ( ( SELECT td = a, ' '             FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>' IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL    DROP TABLE #Login_Audit2
insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys')  DECLARE @tableHTMLE VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit)BEGINSET @tableHTMLE =    N'<H1><font color="#000088">Orphan Users</font></H1>' +    N'<table border="1">' +    N'<tr><th>User Name</tr>' +    CAST ( ( SELECT td = a, ' '             FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>' IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL    DROP TABLE #Login_Audit2insert into #Login_Audit1 (A) select u.name from master..syslogins l right join     sysusers u on l.sid = u.sid     where l.sid is null and issqlrole <> 1 and isapprole <> 1       and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'     and u.name <> 'system_function_schema'and u.name <> 'sys') DECLARE @tableHTMLE VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit) BEGIN SET @tableHTMLE =     N'<H1><font color="#000088">Orphan Users</font></H1>' +     N'<table border="1">' +     N'<tr><th>User Name</tr>' +     CAST ( ( SELECT td = a, ' '              FROM #Login_Audit1                  FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLE = N'<H1><font face="Arial" size="+1" color="#007700">No Orphan Users</font></H1>'IF OBJECT_ID('tempdb..#Login_Audit2') IS NOT NULL     DROP TABLE #Login_Audit2
create table #Login_Audit2(A nvarchar (500),B nvarchar (500)default (''))
create table #Login_Audit2(A nvarchar (500),B nvarchar (500)default ('')) create table #Login_Audit2 (A nvarchar (500),B nvarchar (500)default (''))
insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc insert into #Login_Audit2 (A,B)   select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
DECLARE @tableHTMLF VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2)BEGINSET @tableHTMLF =    N'<H1><font color="#000088">Database Owners</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Owner</tr>' +     CAST ( ( SELECT td = a, ' ',     td = b, ' '             FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
DECLARE @tableHTMLF VARCHAR(MAX) ;iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2)BEGINSET @tableHTMLF =    N'<H1><font color="#000088">Database Owners</font></H1>' +    N'<table border="1">' +    N'<tr><th>Database Name</th>' +    N'<th>Owner</tr>' +     CAST ( ( SELECT td = a, ' ',     td = b, ' '             FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +    N'</table>' ;ENDELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>' DECLARE @tableHTMLF VARCHAR(MAX) ; iF EXISTS (SELECT TOP 1 * FROM #Login_Audit2) BEGIN SET @tableHTMLF =     N'<H1><font color="#000088">Database Owners</font></H1>' +     N'<table border="1">' +     N'<tr><th>Database Name</th>' +     N'<th>Owner</tr>' +      CAST ( ( SELECT td = a, ' ',      td = b, ' '              FROM #Login_Audit2                 FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ; END ELSE SET @tableHTMLF = N'<H1><font face="Arial" size="+1" color="#007700">No Results</font></H1>'
Declare @body varchar(max)Declare @subject1 varchar(max)Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAME
Declare @body varchar(max)Declare @subject1 varchar(max)Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAMEDeclare @body varchar(max) Declare @subject1 varchar(max) Set @subject1 = 'DB Security Audit Report -'+ @@SERVERNAME
Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;
Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;Set @body = @tableHTMLA + @tableHTMLB + @tableHTMLC + @tableHTMLD + '</TABLE></BODY></HTML>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', , @subject = @subject1, @body = @body , @body_format = 'HTML' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', , @subject = @subject1, @body = @body , @body_format = 'HTML' ;EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBA_Alerts', @recipients='itservicesdatabase@daimler.com;itservices.Monitoring@daimler.com', --@recipients='nithya.priya@daimler.com'--@recipients='nithya.priya@daimler.com'--@recipients='nithya.priya@daimler.com', @subject = @subject1, @body = @body , @body_format = 'HTML' ;
End
End

My first thought is that there would be an implicit NCHAR to CHAR conversion somewhere in the process, could you post an actual execution plan of a failed execution? Another thought is that if you start with a fully fledged UTF-16 seeding for the XML, then the chances of hitting this error would be lesser.
😎

HappyGeek SSCoach Points: 18726 More actions · Answer 2

Tried to recreate error, the code runs fine for me and I get the mailed report. I can only suggest it is the data that is capturing a null or empty value, my approach would be to test each of the elements on create table and html to find the offending part and data and apply the change to each element.

...

HappyGeek SSCoach Points: 18726 More actions · Answer 3

Incidentally you should obfuscate your email address, domain and mail profile.

...