How to force a powershell copy-item file copy

Question : I'm attempting to use Powershell copy-item , to copy a file from a remote location onto a local drive. This is the code I'm executing : Copy-Item \\myremotelocation\servers.csv \\mylocaldrive\temp The problem I'm getting is a "denied" type of error: Copy-Item : Access to the path ' \\mylocaldrive\temp\servers.csv ' is denied. At line:1 char:1 + Copy-Item - \\myremotelocation\servers.csv... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : PermissionDenied: (\\myremotelocation...servers.csv:FileInfo) [Copy-Item], Unauthorized AccessException + FullyQualifiedErrorId : CopyFileInfoItemUnauthorizedAccessError,Microsoft.PowerShell.Commands.CopyItemCommand I can copy the file manually i.e outside of the Powershell environment and I am using the same logon for both the Powershell console and the manual... Read more →

How to change current_user to display dbo

Question: Currently when I execute the sql code - SELECT CURRENT_USER , the return is my login name . I'd like to change the result to display dbo , as there are a number of application code which rely on the login name to be dbo. How can I change the recordset to display dbo? Answer: The easiest way to change the SELECT CURRENT_USER - result is to change the privilege to sysadmin. There are more details on select current_user shows dbo . The quick summary is if the current user is a sysadmin privilege , the recordset returns “dbo”,... Read more →

Database compatibility changing to 100 when restoring SQL 2005 to SQL 2017

Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and there is some database compatibility behaviour I was not expecting. When the database is attached to SQL Server 2005 - the database compatibility is 90. But when restored to SQL Server 2017 - the database compatibility is set at 100. Why is this happening? Answer: This is expected behaviour. i.e when changing from a lower version to a higher version - if the database compatibility of the source db is lower than the lowest compatibility of the target SQL Server version... Read more →

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 →