April 12, 2012 at 6:14 am
The scenario: An enterprise across multiple data centers with over 80 servers from SQL 2000 through SQL 2008 R2. Audit wants disabled domain logins removed from the SQL Servers even though the domain accounts are disabled.
The goal: Create a script that can seek out where a login is used and delete it. Script must work across any version of SQL Server encountered. Script will be run by the muli-server connection capability of SSMS.
When owned objects are encountered, which will inhibit deletion of a user/login, handle it by changing the owner of said objects to DBO or SA.
In most cases this is a schema being owned by a user even though there are no objects within the schema. When a schema is encountered the script will attempt to drop the schema, which should be successful if there are not objects in that schema. If the drop fails then the schema should have the owner changed to DBO.
If it is a DB owned by a former DBA, change the owner to SA.
I have this script working pretty well at this point, but I have some weird behavior I encounter with it. The first thing is that I get errors like this, from multiple servers:
RICHMSSQLDEV01(HISNT\cstamey): Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
RICHMSSQLDEV01(HISNT\cstamey): Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
If I run the script again I get none of these errors. It's like it cannot do anything with the schema the first time around and CAN the second time around. The script first attempts to delete the schema, within a Try, and if it cannot delete the schema the Catch is to change the owner to DBO. Is there something wrong with my logic that it won't work the first time around?
The script:
/*This script is meant to go through all server DBs and drop the user from any DBs they are in before finally dropping the login from the server.
This is meant to work with SQL2000 or 2005+ transparently, and run on a single server or multiple servers at the same time.
Chris Stamey, 4-6-2012
Version .90
*/
Declare @LoginID NVarChar(255)
Declare @Command NVarChar(2000)
Declare @Command2 NVarChar(255)
Declare @sqlVersion numeric(4,2)
Set @LoginID = 'HISNT\akling'
Select @sqlVersion = left(cast(serverproperty('productversion') as varchar), 4)
If @SQLVersion >=9 --2005 or later.
Begin
Set @Command = 'Use [?];
Declare @SchemaName VarChar(255)
Declare @Command2 NVarChar(250)
Declare @DBName NVarChar(255)
--Check to see if DB is owned by the login we are deleting. If so, change owner to SA.
If Exists(Select D.name from sys.databases D
Inner Join sys.server_principals P On D.owner_sid = P.sid
Where P.name = '''+ @LoginID + ''')
Begin
While 1 = 1
Begin
Select Top 1 @DBName = D.name from sys.databases D
Inner Join sys.server_principals P On D.owner_sid = P.sid
Where DatabaseProperty(D.Name, ''IsOffline'') = 0 And P.name = '''+ @LoginID + '''
Set @Command2 = ''Use '' + @DBName + ''; Exec sp_ChangeDBOwner ''''SA''''''
Exec sp_ExecuteSQL @Command2
If @DBName Is Null Break
Else Print @DBName
End
End
If Exists(Select S.name from sys.schemas S
Inner Join sys.database_principals D On S.principal_id = D.principal_id
Inner Join sys.server_principals SP On D.sid = SP.sid Where s.name = ''' + @LoginID + ''')
Begin Try
Select @SchemaName = S.name from sys.schemas S
Inner Join sys.database_principals D On S.principal_id = D.principal_id
Inner Join sys.server_principals SP On D.sid = SP.sid Where s.name = ''' + @LoginID + '''
Print @SchemaName
Set @Command2 = N''Drop Schema '' + @SchemaName
--Print @Command2 --For debugging.
Exec sp_executeSQL @Command2
End Try
Begin Catch
Set @Command2 = N''ALTER AUTHORIZATION ON SCHEMA::'' + @SchemaName + '' TO [dbo];''
--Print @Command2 --For debugging.
Exec sp_executeSQL @Command2
End Catch
Set @Command2 = ''IF EXISTS (SELECT * FROM sys.database_principals D Inner Join sys.server_principals SP On D.sid = SP.sid WHERE SP.name = ''''' + @LoginID + ''''')
DROP USER [' + @LoginID + ']''
Exec sp_executeSQL @Command2'
--Print @Command --For debugging.
Exec sp_MSForEachDB @Command
Set @Command = 'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ''' + @LoginID + ''')
Drop Login [' + @LoginID + ']'
Print @@ServerName --So we know which server is executing the script. Useful when error messages are thrown.
Execsp_ExecuteSQL @Command
End
Else
Begin
--SQL2000
Set @Command = 'Use [?]; IF EXISTS (SELECT * FROM sysusers WHERE name = ''' + @LoginID + ''')
exec sp_revokedbaccess [' + @LoginID +']'
Set @Command = @Command2 + @Command
Exec sp_MSForEachDB @Command1 = @Command
Begin
If CharIndex('\', @LoginID) > 0
Set @Command = 'If Exists(Select * From master.dbo.syslogins where name = ''' + @LoginID + ''')
Exec sp_revokelogin [' + @LoginID + ']'
Else
Set @Command = 'If Exists(Select * From master.dbo.syslogins where name = ''' + @LoginID + ''')
Exec sp_droplogin ''' + @LoginID + ''''
Execsp_ExecuteSQL @Command
End
End
I have not yet done the code to find owner objects in SQL200 and "reown" them. Ideas on that welcomed.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 12, 2012 at 1:21 pm
So for the sytax error I would print the command or select statment in the try..catch and run it manually against one of the servers to see if you can determine the actual syntax error.
As for it running successful the second time might be that it moves to the catch after the error and changes the ownership which means it will not enter the try..catch the second time (which leads me to believe that is where the error is).
April 12, 2012 at 1:41 pm
Are you wanting to remove the server level login or the server login and the database user?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2012 at 6:33 am
The main goal is to remove the login, but I don't want to leave orphans around, so I am trying to remove the users associated with the login from each DB first, then remove the login last.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply