Get-ADGroup find AD groups with like

Question: How can I find a list of AD groups with a specific search filter on the name column. ? In this case I want to find all AD groups in the default OU group with a search string. Answer: Assuming you are in the current Domain group \ OU group , find an example of Get-ADGroup which will filter on the search string "admin" Get-ADGroup -Filter "name -like '*admin*'" -Properties * | select name if you need to commit the Get_ADgroup search onto another server - than you'll need to use the -server switch Get-ADGroup -Filter "name -like '*admin'"... Read more →

Automate SQL Server Vulnerability Assessment

Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced security pane . The powershell cmdlet - SQLVulnerabilityScan - is available through the SQL Server modules , the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. The first step is to check that you can execute the cmdlet , point to a SQL Server and extract the report from the Vulnerability Scan. This powershell snippet - connects to a SQL Server and runs the scan over a specific database... Read more →

How to create a PSCredential object

Creating a Credential Object is useful when you require to pass a username and password through Powershell commands. You might see Credential used in various Powershell cmdlets - such as SqlVulnerabilityAssessmentScan. An example of invoking the SqlVulnerabilityAssessmentScan may be something like: Invoke-SqlVulnerabilityAssessmentScan -Credential "sa" -ServerInstance myserver\inst1 -DatabaseName "master" The problem with the way Credential is used above - will create a request for the password. What might be more efficient is to create a secure object with username \ password Enter the PSCredential. A PSCredential object represents a set of security credentials, i.e username and password. Here is an example... Read more →

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Question: When attempting to expand the Catalogs in the linked server object defined, I received below message.How can I fix this problem? The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’ Answer: The overall fix is to grant execute rights to the logon requiring the privileges on SYS.XP_PROP_OLEDB_PROVIDER. Step 1 >> Map the user in the master database USE [master] GO CREATE USER [myuser] FOR LOGIN [myuser] Step 2 >> GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [myuser] These are the basic steps. But quite often in an organizations SQL Server Security Policy , direct execute... Read more →

Extended Events -capture query errors

If you're troubleshooting SQL Server query errors , you'll already know Extended Events are highly useful and very effective method to capture SQL Server errors. To use the script you'll need appropriate privileges to create the Extended Event. You will also need to have some space available on the disk to store output files. I usually place them in the C:\tmp - although be careful just in case you fill the C:\ drive! The alternative is to use another drive. CREATE EVENT SESSION [CaptureErrors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_hostname,sqlserver.database_id,sqlserver.nt_username, sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username) WHERE ([severity]>=(10))) ADD TARGET package0.event_file(SET filename=N'C:\tmp\CaptureErrors.xel',metadatafile=N'C:\tmp\CaptureErrors.xem') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1... Read more →

SQL Server Docker and Container cheatsheet

I've started working with Docker platform and managing SQL Server Containers. This list are some of the commands I execute most often when using Docker and Containers. --Pull down a container from the Docker registry docker pull --(generic approach)Pull down a sql server container to a valid docker installation on RHEL and run (generic approach) docker pull microsoft/mssql-server-linux docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=LinuxSQLServer123' -p 1433:1433 -d microsoft/mssql-server-linux --(customised approach)Pull down a sql server container to a valid docker installation on RHEL and run Step 1 - Mapping volume names with customised locations **comment - data volume** docker volume... Read more →

The DELETE statement conflicted with the REFERENCE constraint

Question: In the application error logs there is a message - The DELETE statement conflicted with the REFERENCE constraint FK_xxxxxxxxx What is causing this error ? How can I troubleshoot? Answer: The error is occuring because there is an attempt to delete a parent table data before the referenced child table data is deleted When a database uses a FOREIGN KEY , its purpose is to create a link between two tables. The foreign key is one or more columns in the a table referencing another table's PRIMARY KEY. A child table is the table hosting the FOREIGN KEYS.A parent... Read more →