T-SQL Tuesday: One Script to Completely Remove a Principal

Today is another T-SQL Tuesday.  The topic is Naughty or Nice and we’re supposed to talk about our environment and whether we think it’s naughty or nice.

Well today is also my wedding anniversary.  So I’m going to write about whatever I want!

Whenever someone leaves my orginization, we terminate their access in every way we can.  Usually this is pretty easy as we just set people up through active directory groups.  The groups get removed, and viola their access to everything is gone.  There are some cases where we’ve had to setup ad-hoc access for somebody, which at times can include giving them their own schema.  I also run into cases on dev machines where people with sysadmin, dbcreator, or CONTROL SERVER rights have gone through and set themselves up as the dbo user or owner of the database.  In their defense this is usually done inadvertantly by performing a restore or creating a database and not changing the owner after initial creation.  In my opinion, no live person with a windows account should own a database.  The dbo user should be mapped to either the sa account or one of the SQL Server service accounts.

With giving the user a schema I have two options for removing the user.  Since I can’t delete a user that owns a schema in the database, I can either setup the schema to be owned by dbo, or I can move all of the objects to the dbo schema.  Since changing the owner is a much less nuclear option, I prefer the former.

When a user owns a database, their login is mapped to the dbo user.  I really don’t have a choice here but to run sp_changedbowner and map the user to a different login.  I will be unable to delete the login until it does not own any databases.

Now that we know everything we have to account for, let’s look at the logic behind this disaster of a script that I’ve created:

  1. It makes the assumption that the login exists on the server.  We give it this login name.  I don’t have the script “CMS ready” at this time, because what I’m using it for won’t be utilizing CMS.  I’m automating the use of this and using Powershell like a boss instead so I never have to touch it.  If you want to change it up to look for it in server_principals and run it ad-hoc against your CMS, go for it.  It’s as simple as looking for it in sys.server_principals.  This thing will work for 2005 or greater.  Since I haven’t mentioned it yet, this script is as-is and should be tested before running against production.
  2. Next, it gets the SID of the login.  This is the best way to map the users in the database to the login.
  3. It creates a cursor based off of sys.databases if the database state = 0 and is_read_only = 0.  Unfortunately I can’t use sp_msforeachdb because it would potentially try to loop through read-only or offline databases.  We could debate here on which settings to use in sys.databases, and you’re welcome to change what I have to meet your needs, but what I have works for me.  It loops through each database, looking for the SID of a user that matches the SID of the login.
  4. When it finds a match, the first thing it does is check to see if the user the SID is mapped to is the dbo user.  If it is, it runs sp_changedbowner to map it to the default name for the sa login.  If you’ve renamed your sa account, or want to map it to a different login, feel free.  For simplicity reasons, I’ve made it sa.  Note that if the login is mapped to dbo, we’re done here.  We can’t and don’t want to delete the dbo user and we don’t have to change schema ownership since we want dbo owning most schemas in the database.
  5. If the login isn’t mapped to dbo, it then goes through and alters the owner of all schemas it owns and changes it to dbo using another cursor.  While I know you’re shuddering at the idea of nested cursors, there isn’t a way to use set based logic for this.
  6. I make the assumption here that the login isn’t mapped to a user that doesn’t match the login name.  For example, it assumes that the login [CORP\SQLCOWBELL] is mapped to user [CORP\SQLCOWBELL] and NOT mapped to something different such as just [SQLCOWBELL] or completely off the wall like [OLEMISS] or [ROLLTIDE].  It deletes the user from the database and fetches next from the cursor.
  7. After it has looped through each database, it deletes the login.

So without further rambling from me, here it is!  Send me a tweet if something seems off.

use master
declare @login sysname ,@sql nvarchar(1000),@dbname nvarchar(50),@thisid nvarchar(60),@parmdef nvarchar(500),@dbocount int,@count int,@SID varbinary(85),@schemasql nvarchar(max)
set @login = N'CORP\SQLCOWBELL'
set @SID = (select sid from sys.server_principals where name = @login)
declare dbcursor cursor fast_forward for select name from sys.databases where state = 0 and is_read_only = 0
select * from sys.databases
open dbcursor
Fetch NEXT FROM dbcursor INTO @dbname
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
set @thisid = null
set @parmdef = N'@SIDs varbinary(85), @retvalOUT int OUTPUT'
set @sql = N'select @retvalout = principal_id from ' + @dbname + N'.sys.database_principals where SID = @SIDs'
execute sp_executesql @sql, @parmdef, @SIDs = @SID, @retvalout = @thisid OUTPUT
if @thisid is not null
begin
--change owner of db if user = dbo
set @parmdef = N'@SIDs varbinary(85), @retvalOUT int OUTPUT'
set @sql = N'select @retvalOUT = count(*) from ' + @dbname + N'.sys.database_principals where name = ''dbo'' and sid = @SIDs'
execute sp_executesql @sql,@parmdef, @SIDs = @SID, @retvalout = @dbocount output
if (@dbocount > 0)
begin
set @sql = N'EXEC ' + @dbname + N'.[sys].[sp_changedbowner] @loginame = N''sa'', @map = false'
execute sp_executesql @sql
print 'Changing the database owner'
end
else
begin
--check for schema ownership; reassign to dbo user if owned; this keeps the schema name and all objects in the container intact
set @parmdef = N'@thisids nvarchar(60),@retvalOUT int OUTPUT'
set @sql = N'select @retvalOUT = count(*) from ' + @dbname + N'.sys.schemas where principal_id = @thisids'
execute sp_executesql @sql,@parmdef, @thisids = @thisid, @retvalout = @count output
if (@count > 0)
begin
print 'Found owned schemas; changing to dbo'
set @sql = N'use ' + QUOTENAME(@dbname) + N' declare @schemaname nvarchar(100), @schemasql nvarchar(max)
declare schemacursor cursor fast_forward for
select name from sys.schemas where principal_id = @thisids
open schemacursor
Fetch NEXT FROM schemacursor INTO @schemaname
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
set @schemasql = N''ALTER AUTHORIZATION ON SCHEMA::'' + quotename(@schemaname) + N'' TO [dbo]''
execute(@schemasql)
Fetch NEXT FROM schemacursor INTO @schemaname
end
close schemacursor
deallocate schemacursor'
set @parmdef = N'@thisids nvarchar(60)'
EXECUTE SP_EXECUTESQL @SQL, @parmdef, @thisids = @thisid
end
--Now Delete the user from the database
set @sql = 'use ' + quotename(@dbname) + ' DROP USER ' + quotename(@login)
execute sp_executesql @sql
end
end
Fetch NEXT FROM dbcursor INTO @dbname
end
close dbcursor
deallocate dbcursor
set @sql = 'DROP LOGIN ' + quotename(@login)
execute sp_executesql @sql

Be Sociable, Share!

Leave a Reply

Your email address will not be published. Required fields are marked *