Dropping a SQL Server database that is in use

We have been trying to find an effective way of successfully dropping a SQL Server database regardless of whether it is in use or not.

Originally, we wrote scripts to kill all users before proceeding with the database drop. However, we found that users were reconnecting before the execution of the drop command was successful. These users were not ordinary users but rather system connections for monitoring or replication management.

One method to kick users prior to drop is to

USE master

GO

ALTER DATABASE MyDatabaseNameHere SET SINGLE_USER;

However, this does not handle active database replication well.

A much more effective option is to take the database completely offline prior to the drop. In this case SQL Server will gracefully clean up the replication.

USE master

GO

ALTER DATABASE MyDatabaseNameHere SET OFFLINE WITH ROLLBACK IMMEDIATE;

DROP DATABASE MyDatabaseNameHere;

PJ Naughter presents on Encryption using SQL CLR

On June 14th, PJ Naughter will present on Encryption using SQL CLR

He will provide a walkthrough of a custom encryption framework developed using SQL CLR. It covers the history of SQL Plugins using XP’s and then goes on to describe the development, testing and deployment of the project on SQL Server 2005/2008.

PJ runs Naughter Software, a software development consultancy firm, specializing in native mode Windows development. Before Naughter Software, PJ was the technical architect and a software developer working for Soft-ex Communications, a developer of PBX Call Monitoring software located in Dublin, Ireland. He has been a Microsoft C++ MVP since April 2007. In his spare time,  he runs a popular web site (www.naughter.com),  which provides open source and shareware of interest to Windows native mode developers. At last count, well over 250,000 lines of production quality Win32, MFC & ATL C++ source code is available to download. Areas covered include: Calendrical Calculations, Image Processing, Mathematical Astronomy, GPS, GIS and OpenStreetMap, GUI controls, OS Version Detection, PC Ports and various Internet protocols including POP3, SMTP, MIME, ICMP, SNTP, HTTP, SSL, CDDB and FTP. One of the most popular downloads available is a class framework for writing NT Services in C++. He has the honour of having this software control some of the experiments in the MSG (Microgravity Science Glovebox) on board the International Space Station. Outside of computers and Astronomy, he also has a keen interest in Pub Quizzes, 10-pin bowling, and the odd game of golf, Irish weather permitting of course.

Location is on the Ground Floor, Microsoft Atrium Building Block B, Carmenhall Road, Sandyford Industrial Estate, Dublin 18 (close to the Sandyford Luas stop). Limited underground parking is available at the back of the building

Registration is at http://www.mtug.ie/UserGroups/SQLServer/tabid/82/ctl/Details/Mid/413/ItemID/85/Default.aspx?ContainerSrc=[G]Containers/_default/No+Container

Name:  Encryption using SQL CLR

Date:  June 14th, 18:30

Location: Auditorium, Microsoft Atrium

Problems when creating logins for users who already exist in a SQL Server database

I came across this one again today and realised that I had never blogged about it previously.

I had a development manager contact me because she could not create a user (e.g. xyz) to access a database (e.g. userdb1) on a SQL Server 2005 server (e.g. server2) without getting “User, group, or role ‘xyz’ already exists in the current database. (Microsoft SQL Server, Error: 15023)”

This is quite common when dealing with multiple servers (development, test, stress, production , & business continuity). In this example, both the user database, userdb1, and the user, xyz, were created on a different server, server1, originally and xyz was given access to userdb1. Then the database, userdb1, is backed up, copied to server2 and restored there.

The login xyz does not exist on server2 yet and when one tries to recreate it, it finds a reference to xyz already in the restored userdb1 and cannot match the login to the user of the same name.

USE userdb1

GO

EXEC sp_change_users_login ‘report’

will report all users there for which no login information can be found in the master database.

You can then run this to link the master login back to the user in userdb1

USE userdb1

GO

EXEC sp_change_users_login ‘update_one’, ‘xyz’, ‘xyz’

Pinal Dave has a more comprehensive blog post than mine on this common problem.

%d bloggers like this: