Script to change SQL Agent job owners
To find the jobs by a specific owner and changing it’s ownership to sa
Tag: Scripts
Script for SQL Server Agent jobs
The below script is useful in finding the important information about the jobs in your SQL Server like who is the owner of your job, if you have set any notification email or not if it set then to which Emailid the mail will be sent to, when it was created and when was the […]
SQL Agent Job for databases in availability group
When we have SQL Server job running for databases in Always-On there could be need of running a specific job on Primary database only. In such cases we are required to create the job on every server and add the logic that it runs only on the Primary server. The below template can be used […]
Script to find out repetitive failed logins
Below Script will read through the logs and as an output gives the logins which are failing repeatedly since last 5 days, you change it to as many days maximum being the number of days for which you are maintaining your logs. GitHub Link.
Database stuck in Single_user mode
Today I got a call from a colleague who was trying to restore a database, and as the generic first step had put the database in single_user mode and after that ran the Restore query which somehow failed. Now the issue began, this database which was put on single_user mode was not coming back to […]
Script to find something in SQL Server logs
It is always difficult to find some relevant information about any error or record from SQL Server logs mostly when you have 100 log files and also has backups information in the logs. Use below script to find a specific string looping through all your logs. GitHub link. It will create a temporary table and […]
PowerShell script to create SQL Server environment health check report
Sharing a simple PS script which goes through the servers stored into csv file, and run Invoke-Sqlcmd and run queries to capture the relevant information and store it into excel sheet. We are using SQLServer module and ImportExcel module for doing this task. GitHub link.
Scripting out the permissions for a database
Refreshing a database for a DBA is as common as breathing (I might be exaggerating it, or am I?). The post refresh activity is to provide the same set of permissions for all the users on the database which were there before being overwritten with a fresh prod copy. If you are doing it manually […]
Creating scripts for repetitive tasks
One of the most mundane task to do is providing permission to a user on multiple stored procedures in a database. There is not much you can do, either provide ddladmin permission onto the database (it provides much more permission than required) or manually provide permission to each SP using Grant Execute or using GUI. […]
Creating SQL Server reports using PowerShell
For one of my project we had to daily run some scripts on CMS and copy paste that information into an Excel sheet and then send it across to the different stakeholders providing them the status of current health of the environment. This being manual process was error prone and time consuming. To overcome this […]
being a dba 