Jun 14

Cannot generate SSPI context

I received this error message: “Cannot generate SSPI context” while attempting to connect to a SQL server:

The server had DHCP enabled so the address had the potential to be different every time the machine restarted.

Solution: This was an easy one…using the Fully Qualified Domain Name for the connection string allowed me to connect to the server. Another solution would be flushing DNS on client attempting to connect.

Jun 13

Dynamic Log Shrink (DBCC Shrinkfile)

Recently I worked with a customer that was running their databases in simple mode. During the night they would run various bulk load jobs. This would cause the transaction logs to grow quite large (even though the database is set to simple mode each transaction must be logged). This in turn caused disk space issues.

A simple solution I created was to shrink the transaction logs on a daily basis using this dynamic script:


 

Jun 11

Foreign Key Relations

One of my colleagues wanted a textual way of viewing foreign key relations. It prompted me to write this script:

 

Jun 10

List Stored Procedures for All Databases

I recently needed a way to check all databases on an instance for a Stored Procedure that was created in the last 3 days. I created this script using the sp_MSforeachdb “undocumented” stored procedure to query each database:

 

Jun 06

Manually Failover Database Mirroring

Use this command to manually failover a mirrored database. The command must be run using the master database (hence the use master preface):

 

Jun 05

Script Database User Role with Permissions

If you have ever tried to copy database security roles that included permissions or securables you might have noticed that SQL’s built-in script generator doesn’t produce a script that includes role permissions.
Here is a script that I found and modified it to work with legacy SQL versions:

Jun 05

Backup with Email Alert

Here is a clean way to perform a one-time differential backup. I also needed the server to email me when the backup was complete.

I used these two MSDN articles to create this script:

Apr 26

GRANT Schema Permissions

Background: I had a user that needed to be able to read, but not execute, stored procedures in the DBO schema. I used these methods below to achieve this task.

There are two methods to granting schema permissions in SQL server.

  • One is through T-SQL commands:

Apr 25

Reseed Identity for Table

I’ve designed an integration job that deletes all data from a table and imports new records each night. The table has an identity column in it, so the value (or seed) will always be growing.

Here is the easiest way to reseed the identity column for a table:

This will force the next inserted identity value to be 1 (0+1=1 Smile).

 

Apr 12

Checking for sysadmin on an Instance

Here is a simple script that checks for sysadmins on an instance: