Chris McKenzie's Blog

Preventing Database Bloat / Rebuilding SQL Server Logins

I'm working on an XML driven installation program that will destroy and create the stored procedures, roles, users, and logins associated wtih my SQL Server Reporting Services installation.  I've created a user called ReportServer that is used by my reports to access the database.  All of my report stored procedures are given a prefix “rp,” and ReportServer is granted Execute permissions on the “rp” procedures only.

One of the things I'm trying to prevent in the future is database bloat--whereby you have tables, views, etc. that are in the database and are barely used--and only then because some piece of a stored procedure somewhere was never updated to use the more modern objects instead.  So my strategy with all of my database objects is to destroy and create.  With stored procedures and views this is very simple as you don't have to worry about juggling the data while you do it.  However, I'm also destroying my Logins and user roles and recreating them from scratch.

The problem I was having was that I couldn't use sp_droplogin to get rid of my users if that user happened to be logged in to the database at the same time.  So, I called my friend Scott The SQL Guru and asked for a pointer on how to tell whether my target user was logged in or not, and how to force them out if they were logged in. He directed me to Master.dbo.sp_who, and KILL command.

I scripted sp_who out to Query Analyzer, stole the piece I needed to get the SPID for my user, kill each SPID, and was able to drop the login.

Here's the SQL:

DECLARE @User varchar(20)

SET @User = 'TargetUser'

IF EXISTS (SELECT Name FROM sysusers WHERE name=@User) BEGIN

-- =============================================

-- Revoke dbaccess from User (User name in current Database, Windows User, or Sql Server login)

-- =============================================

PRINT 'Revoking Database Access for ' + @User

exec sp_revokedbaccess @name_in_db = @User

IF EXISTS(SELECT name FROM Master.dbo.sysLogins WHERE name=@User) BEGIN

-- =============================================

-- Drop Sql Server login

-- =============================================

-- SQL Server Login cannot be removed if it is an active process

DECLARE @Result TABLE

(

ID int IDENTITY,

SPID int,

SQL varchar(200)

)

INSERT INTO @Result (SPID, SQL)

select

SPID,

'Kill ' + CAST(SPID as varchar(20))

from master.dbo.sysprocesses

WHERE loginame=@User

 

DECLARE @i int

SET @i = 1

WHILE (SELECT COUNT(*) FROM @Result) > 0 BEGIN

DECLARE @Statement varchar(500)

SET @Statement = (SELECT SQL FROM @Result WHERE ID = @i)

PRINT 'Executing: ' + @Statement

EXEC (@Statement)

DELETE FROM @Result WHERE ID = @i

SET @i = @i + 1

END

DECLARE @Err int

EXEC @Err = sp_droplogin @loginame =@User