Solved

Cannot Delete users in SQL 2005 Enterprise

Posted on 2009-04-02
22
643 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:mgartley
  • 12
  • 10
22 Comments
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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.
0
 

Author Comment

by:mgartley
Comment Utility
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
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
actually this sp will give you better results
http://www.sqlservercentral.com/scripts/Administration/63631/
0
 

Author Comment

by:mgartley
Comment Utility
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.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
So you created the stored procedure. Then you need to do this:
EXEC spLogin_OwnedObjects 'TRACKIT80_4'
 
0
 

Author Comment

by:mgartley
Comment Utility
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
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

0
 

Author Comment

by:mgartley
Comment Utility
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
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
 
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.
0
 

Author Comment

by:mgartley
Comment Utility
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!
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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'
0
 

Author Comment

by:mgartley
Comment Utility
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
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Can you try this?

exec sp_revokelogin 'TRACKIT80_4'
And the same for all logins.
0
 

Author Comment

by:mgartley
Comment Utility
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...
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Can you try this:
select * from sys.server_principals where name = 'TRACKIT80_4'
and post result here.
0
 

Author Comment

by:mgartley
Comment Utility
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
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
Also, I think you don't have Service Pack 2 installed. Try installing it and then try dropping the login.
0
 

Author Comment

by:mgartley
Comment Utility
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.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Any luck?
0
 

Author Closing Comment

by:mgartley
Comment Utility
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!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now