How to get powershell System.Data.DataTable row count

Question: i'm working with Powershell System.Data.DataTable to get values from a SQL Server to generate a report for DBAs. the report is sent via email to the DBA team. As part of the email subject - I'd like to add the row count as a summary - so the DBAs don't need to always click into the email - i.e they only need to click in if there is a row count greater than 0. How can I get the row count from System.Data.DataTable ? Answer: When you use the Powershell Powershell System.Data.DataTable , certain properties are available . One... Read more →

How to use the SQL Assessment API through Powershell

Microsoft released a SQL Assessment API module , available to Powershell . What is the SQL Assessment API? It's a new method to assess the SQL Server configurations against a set of SQL Server best practises. The SQL Assessment API is available through the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. I've installed the SQL Assessment API and started thinking about how to apply to multiple server.s The availability of the rules via Powershell is a massive plus. This raises fantastic opportunities for automation and inclusion into regular sql server health checks or certification... Read more →

How to find database objects created with SQL default trace

How can you find information about SQL database objects created? We're talking about CREATE DATABASE,CREATE INDEX,CREATE PROCEDURE,CREATE DATABASE. There are common scenarios where you may require these details. For example: 1) It is the organisation's sql server security policy for any production database to progress through a change request and review process. Monitoring database objects added , can identify developers attempting to avoid the review process. 2) Attempting to identify rogue processes. For example - software added to manage the server introduces new database processes - such as database tables 3) Troubleshooting an outage. An unexpected service impact occurs and... Read more →

How to get user security changes with SQL default trace

Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information - via the default trace? I haven't got a dedicated Extended events monitor set up - so I'm relying on the default trace . Management want to understand the details related to this security change. Answer: It is possible to report on security changes administered on SQL Server via the default trace. In fact - it's excellent - except if the default trace configuration is set up to rollover the files. Recently I had to use the... Read more →

How to specify encoding when creating Powershell text file using Out-file

Question: I've deployed a Powershell script - which includes a process to dump out a resultset to a csv file. I'm having an issue where the file is saved with encoding type Unicode - whereas I want the file to be saved as ANSI . This is a Powershell code snippet , using the Powershell cmdlet Out-File (some_input) | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1| Out-File -filepath myoutputfile.txt When I open the file in Notepad - and attempt a Save As - the encoding displays as Unicode . How can... Read more →

How to execute a SQL batch multiple times with GO

Question: I'd like to execute a batch sql statement through SQL Server management studio multiple times and avoid using a loop statement or other similar constructs . I'm doing some testing requiring multiple returns of recordsets and would like a simple approach - for ad hoc queries Answer: Looking at the SQL Server documentation for the sql server utility GO - indicates the syntax is GO [count]. This means the queries triggered as part of the batch executed will execute the amount of times listed after GO. By default it's 1 , but if you specify a positive integer -... Read more →

How to get the Active Directory groups membership for user with Powershell

Question: How can I get the Active Directory groups for a specific user using Powershell? I'm facing a problem where a user is getting a failed login to a SQL Server. This SQL Server has multiple Active Directory groups attached to it - but I don't have visibility to the members of the groups. I know I can use the Get-ADGroupMember powershell cmdlet , which would give the member list of a specified Active Directory group, but this would be time - consuming . Is there a Powershell based method to specify the domain user and return the list of... Read more →