Link to home
Start Free TrialLog in
Avatar of mgartley
mgartleyFlag for United States of America

asked on

Cannot Delete users in SQL 2005 Enterprise

Hello Experts,

I noticed that I have 7 versions of 4 logins out on my sql server. this was caused by a failed migration of a database that took me 7 times to get correct. each time i tried to migrate it 4 logins where created, it just appended an underscore and the next sequential number. I have already deleted the other 6 versions of the database but I cannot seem to get the logins to delete. whenever I try to delete them I get an error that the drop for login etc failed. I will be attaching serveral screenshots for you all to review. Thanks in advance for your help.
sql-errors.doc
Avatar of ralmada
ralmada
Flag of Canada image

Basically, the problem is that the user you are trying to drop owns some object in database you have to identify those objects first
Either delete those objects or change the owner of that object to another user.
Avatar of mgartley

ASKER

Ok how do I tell what the logins are an owner of? the only thing they should of been tied to are the databases that I already deleted. I ran the query in the link you provided but the results it returned didn't have anything to do with these users.

below is what it returned.

dbo      dbo
guest      guest
INFORMATION_SCHEMA      INFORMATION_SCHEMA
sys      sys
IH_MAIN\SQLFullText-Cluster      IH_MAIN\SQLFullText-Cluster
db_owner      db_owner
db_accessadmin      db_accessadmin
db_securityadmin      db_securityadmin
db_ddladmin      db_ddladmin
db_backupoperator      db_backupoperator
db_datareader      db_datareader
db_datawriter      db_datawriter
db_denydatareader      db_denydatareader
db_denydatawriter      db_denydatawriter
actually this sp will give you better results
http://www.sqlservercentral.com/scripts/Administration/63631/ 
I ran the new script but the only output it gave me is:
Command(s) completed successfully.

Is there something I need to change somewhere in the script for the userid I'm looking at? I couldnt find anything. Thanks.
So you created the stored procedure. Then you need to do this:
EXEC spLogin_OwnedObjects 'TRACKIT80_4'
 
I tried excecuting that command but it gave some errors near the bottom. I've attached the output. I appologise if I'm missing something ovious but I'm not that good with SQL. I'm a windows serveradmin not a sql devloper or anything like that. I know just enough to manage some databases but couldn't right a querry to save my life. thanks for your patience and help.

sql-results.txt
There are some bugs on the procedure. run the script attached below and then call your procedure like this:
EXEC spLogin_OwnedObjects 'TRACKIT80_4'  

ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
/*
        Display all objects in all DBs owned by the Login.
 
2008-07-06 RBarryYoung  Created.
2008-08-28 RBarryYoung	Added corrections for DBs with different Collations
			(note that ReportingDBs have different Collations)
 
Test:
 EXEC spLogin_OwnedObjects 'sa'
*/
    declare @sql varchar(MAX), @DB_Objects varchar(MAX)
    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User]
	, o.name COLLATE DATABASE_DEFAULT as [name]
	, o.object_id
	, o.principal_id
	, o.schema_id
	, o.parent_object_id
	, o.type COLLATE DATABASE_DEFAULT as [type]
	, o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]
	, o.create_date
	, o.modify_date
	, o.is_ms_shipped
	, o.is_published
	, o.is_schema_published
     From [%D%].sys.objects o
      Join [%D%].sys.database_principals u 
        ON Coalesce(o.principal_id
			 , (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))
            = U.principal_id
      Left Join [%D%].sys.server_principals L on L.sid = u.sid
'
 
    Select @sql = 'SELECT * FROM
    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] = 'master'
 
    Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                     + Replace(@DB_objects, '%D%', [name])
     From master.sys.databases
     Where [name] != 'master'
 
    Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''
 
    print @sql
    EXEC (@sql)

Open in new window

ok I ran the script and it completed successfully i then ran EXEC spLogin_OwnedObjects 'TRACKIT80_4' as you had said.

it completed without any errors but in the results tab it was blank, and in the messages tab it had the below output. It seems the tweaks you did fixed the errors but somethings either still not right or this login dosn't actaully own anything? Thanks again.


output.txt
 
Then, this login sounds like an orphaned user.  If the database was restored from another server with the same SQL login, the user is in the database sysusers table with an SID value from the original server, which doesn't match the SID value for the login in the master..sysxlogins table where the database now resides.  
So try running this:
USE yourdatabase
EXEC sp_change_users_login 'Update_One', 'TRACKIT80_4', 'TRACKIT80_4'
And then try deleting it.
In the first line im assuming you want me to put in a name of a database but the databases that thesse users would of belonged to nolonger exists. Will this execute directly against some vaules stored within the login and not require actaul access to the database? Also I don't remember which versions of the IDs would go with which database. What happened was I was upgradeing a particaular product called trackit from version 7 to version 8.5. The old version had a local install of MSDE on a server that housed both the application and the database. When I upgraded the prodcut to 8.5 I replaced the old server with a new application server and also decided to move the sql to our enterprise sql cluster. Now what happened was I told the software to migrate and upgrade the database from the old location to the new the proccess failed a few times because of changes to the table structure between the 2 versions had changed. Each time it created a new database and 4 new users. Sometimes I immediatly deleted the database so that the next time I tried it reused the same name sometimes I let it just migrate as trackdata_2 trackitdata_3 etc before delteing the databases. So I guess what I'm getting at is I have no way to know what database name the user IDs used to be tied to. Again all of theese databases where deleted. My current live data is running on the database named trackitdata_3. which was also used im sure as the name of a failed migration attempt at one point that was deleted and now reused. The current users that I am using all end in _7 since I have not been able to delete any of them. So I must of tried the migration 7 times in total before getting it to work.With all that in mind should I still try your above sugestion and if so what would the impact be of putting in the wrong database names or is this all moot since the databases do not exist anymore? Thanks!
Ok, so switch to the trackitdata database and run this command. This will map all loggins in the db. Look for the ones causing trouble. If possible post it here.
EXEC sp_change_users_login 'Report'
Ok I ran it and it did list 1 ID that I'm guessing was used by the migration process from version 7 to 8. I've attached a screenshot of the output named sql.jpg which also shows all the logins for this database on the left. I also have attached another screenshoot called logins.jpg that lists the logins that I am trying to delete. they show up in the root security\logins folder but not under any database's logins folder. Thanks again.

PS I blacked out one login as it was a report writers domain account which is thier name.
sql.jpg
logins.jpg
Can you try this?

exec sp_revokelogin 'TRACKIT80_4'
And the same for all logins.
I tried revoking the login it came back saying:

Msg 15407, Level 11, State 1, Procedure sp_revokelogin, Line 25
'TRACKIT80_4' is not a valid Windows NT name. Give the complete name: <domain\username>.

it's not an nt account though its a sql account...
Can you try this:
select * from sys.server_principals where name = 'TRACKIT80_4'
and post result here.
well I think we are making progress. this has identified the database that this id is tied to. However that database no longer exists...
output.jpg
Like I said before, you cannot delete a login if you have the following situations:
1) The login has an active connection
You might need to use sp_who to logins connected to the server. And the use KILL to kill processes that are currently running with the login SID. Then drop the login.
2) Owns a securable server object
check ownership with query above. and then change ownership
3) Owns a SQL Agent job.
Try stopping SQL Agent and then drop the login.
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You know your right that box never got SP2 for some reason. I'll try to get that updated off hours and I'll also stop the sql agent afterwards and try the drop again. Thanks again for all your help, I'll let you knwo how this fares.
Any luck?
The SP level seems to have been the issue. I got a chane to update it the other night and it allowed me to drop the logins afterwards. Thanks for all your help!