Category Archives: Tips

SQL Agent jobs, linked servers and minimal privileges

Posted on 2016/05/02 by brentm

Basically, this issue is all described here… https://support.microsoft.com/en-us/kb/2492477 In a nutshell, a T-SQL job executes in the context of the job owner. If the job owner is sa or another member of the sysadmin role is is able to make … Continue reading →

Posted in Security, T-SQL, Tips | Comments Off on SQL Agent jobs, linked servers and minimal privileges

SQL Server Job Execution Status Output

Posted on 2015/05/17 by brentm

It is not possible to use INSERT….EXEC to call sp_help_job to output the results into a table for querying e.g you want to get the execution status of a job using sp_help_job through some form of automation.The error you will … Continue reading →

Posted in T-SQL, Tips | Comments Off on SQL Server Job Execution Status Output

Disk allocation unit size

Posted on 2014/09/25 by brentm

How to determine what allocation unit size was selected when a disk was formatted? If a disk was formatted incorrectly i.e. a SQL Server data volume,  but you want to check what allocation unit size was selected  when the disk … Continue reading →

Posted in Performance, Tips | Comments Off on Disk allocation unit size

Changes to SSMS to open files in new tab, not new instance

Posted on 2014/02/05 by brentm

On a new clean install, and because SSMS is built upon Visual Studio, the default behaviour is to open a new SSMS instance when you double click on a .sql file. To fix this, so that a each double click … Continue reading →

Posted in Tips | Comments Off on Changes to SSMS to open files in new tab, not new instance

Creating a SQL Database

Posted on 2012/12/02 by brentm

Writing an article about how to create a database might seem a bit too simple to many but I was surprised when talking to people that they didn’t know why I would do it this way.  When you create a … Continue reading →

Posted in Tips | Comments Off on Creating a SQL Database

Startup stored procedures

Posted on 2012/05/06 by brentm

Stored procedures are enabled for execution with the proc sp_procoption. Create your stored procedure in the master database and then call sp_procoption like so… sp_procoption @ProcName = ‘my_stored_procedure’ , @OptionName = ‘startup’ , @OptionValue = ‘on’ By calling sp_procoption, you … Continue reading →

Posted in Tips | Comments Off on Startup stored procedures

Adding DTS support to SSMS

Posted on 2012/02/25 by brentm

I have done this on my recent rebuild of my laptop at work so know that this does work. SQL 2000 tools are no longer required. The following add-ons are required to provide DTS support in SSMS 2008… 1. SQL … Continue reading →

Posted in Tips | Comments Off on Adding DTS support to SSMS

Rebuilding system databases on SQL 2005 (or adding additional components)

Posted on 2012/02/25 by brentm

SQL server is corrupt – master is damaged. You didn’t do the original install and you have to supply the media for the repair. In the situation I’m thinking of, SQL Server was installed as part of the server build … Continue reading →

Posted in Tips | Comments Off on Rebuilding system databases on SQL 2005 (or adding additional components)

Adding a location for your saved SQL scripts into SSMS

Posted on 2012/01/31 by brentm

I was searching for this on the internet because the registry keys for the default save location can’t be modified – well actually they can, but they just keep getting over-written. So, I found this which is a neat way … Continue reading →

Posted in Tips | Comments Off on Adding a location for your saved SQL scripts into SSMS