June 23, 2020 at 9:04 pm
I am looking for some assistance on having a simple, easy SQL Agent job I can run that I can email a report to security Admin types showing what ID's within a SQL Server don't meet password enforce policy. Something like the below SQL statement is some type of readable format like HTML or something. Does anyone have anything like this? Trying to get this in a above a DBA level somewhat pretty report.
select
@@SERVERNAME as servername,
name,
create_date,
is_policy_checked,
is_disabled,
PWDCOMPARE(name, password_hash) as UsernameAsPassword
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name
June 24, 2020 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 24, 2020 at 9:52 pm
Here is a general template that can be used to include the data in the email:
Set Nocount On;
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'recipient@domain.com'
, @cc_recipients varchar(max) = 'cc1_recipient@domain.com;cc2_recipient@domain.com';
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
, html.hdr4 As th, ''
, html.hdr5 As th, ''
, html.hdr6 As th, ''
From (
Values ('Server Name', 'Login Name', 'Create Date', 'Is Policy Checked', 'Is Disabled', 'Username as Password')
) As html(hdr1, hdr2, hdr3, hdr4, hdr5, hdr6)
For xml Path('tr'), elements) As varchar(max));
--==== Get the results as an XML table
Set @xmlResults = cast((Select @@servername As td, ''
, sl.name As td, ''
, sl.create_date As td, ''
, sl.is_policy_checked As td, ''
, sl.is_disabled As td, ''
, pwdcompare(sl.name, sl.password_hash) As td, ''
From sys.sql_logins sl
Where sl.is_policy_checked = 0
Order By
sl.name
For xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body>The following contains the counts for Advanced Directives by Age Category.
';
--==== Setup the table with the list of new document types
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{public or private profile}'
, @from_address = 'PasswordEnforce@domain.com'
, @reply_to = 'ReplyToAddress@domain.com'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @subject = 'Password Enforce Policy'
, @body_format = 'HTML'
, @body = @body;
End
Go
Here is a template you can use to send as an attachment:
Declare @query nvarchar(max) = ''
, @recipients varchar(max) = 'recipient@domain.com'
, @cc_recipients varchar(max) = 'cc1_recipient@domain.com';
Set @query = '
Set Nocount On;
Select @@servername As [Sep=,' + char(13) + char(10) + 'Server Name]
, LoginName = sl.name
, CreateDate = sl.create_date
, IsPolicyChecked = sl.is_policy_checked
, sl.is_disabled
, UsernameAsPassword = pwdcompare(sl.name, sl.password_hash)
From sys.sql_logins sl
Where sl.is_policy_checked = 0
Order By
sl.name;'
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{public or private profile}'
, @query = @query
, @subject = 'Password Enforce Policy'
, @body = 'Attached is the list of SQL logins that do not meet password policy.'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = 'DatabaseNameHere'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = 'PasswordEnforcePolicy.csv'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 24, 2020 at 9:54 pm
You can adjust the embedded CSS style-sheet to change the colors of the HTML table, the borders, padding, fonts, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2020 at 12:58 pm
This is perfect. Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply