Category Archives: Utilities

How Do I Find SSIS Packages that Exist in Multiple Folders?

A script to find duplicate packages deployed to different folders on the same server. Continue reading

Posted in Utilities | Tagged , | Leave a comment

How Do I Kill All Sessions from a Windows Group?

The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. Continue reading

Posted in Security, User Management, Utilities | Tagged | Leave a comment

How Can I Tell if a Windows Login has Rights to My Server?

This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard. Continue reading

Posted in Security, User Management, Utilities | Tagged | 5 Comments

Script to Create A TempDB File Per Processor

To satisfy my need to be lazy and protect me from myself I have written a script to add a TempDB file per processor. Continue reading

Posted in File Management, Utilities | Tagged | 6 Comments

What Do I Need to Do After Moving a Database to SQL 2005/2008?

I recently moved a database from SQL 2000 to SQL 2005. It was really just a matter of detaching the database, copying the files, attaching it on the new server, flip the compatibility mode and run a few queries. Thats it? Could it have been so easy? It turns out the answer is no. There are some steps that need to be taken after moving a database to SQL 2005 or 2008 from SQL 2000. Continue reading

Posted in Utilities | Tagged , , , , , | Leave a comment

How Do I Identify Invalid or Unneeded Logins?

I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning. Here is the script I came up with to do the analysis. Continue reading

Posted in User Management, Utilities | Tagged , , , , | Leave a comment

Stored Procedure to Sequentially Run SQL Agent Jobs

Here is another one of my utility scripts. Lately it seems like all I am blogging about lately is utility scripts, but I have a ton of them and need to get them documented. This stored procedure is used to run a series of SQL Agent jobs sequentially. It is one of my favorite stored procedures because it clears up a lot of headaches for me without having to spend a bunch of money or turn my maintenance schedules over to another team. Continue reading

Posted in Utilities | Tagged | 8 Comments

Stored Procedure to Get SQL Job Execution Status

Ever need a programmatic way to figure out if a SQL Agent job is running? I did. After spending a while searching on Google I did not turn up anything I liked so I turned to Profiler and started looking at what calls SQL Management Studio uses. After a bit of poking around I was able to come up with a stored procedure that I could call. Continue reading

Posted in Utilities | Tagged , , , , , , | Leave a comment

Stored Procedures to Store and Get Database Users with All Permissions

Introduction Traditionally when doing a restore, moving a database, etc. a DBA would simply go into Management Studio and use the Generate Scripts wizard to script all logins and permissions. This approach can take several minutes and as I recently … Continue reading

Posted in Security, User Management, Utilities | Tagged , , , , | 3 Comments