March 16, 2006 at 8:51 am
Hello,
I have a user on my sql server (AD user) that is no longer a part of the group, and I want to remove his account, however, now, I am getting this error:
you cannot drop the selected login id because that login id owns objects in one or more databases
I seen in other forums that this could be fixed with the EXEC sp_change_users_login 'Auto_Fix','[UserName]','[LoginName]'
but I am not sure that this user doesnt actually own anything - is there a way to see what this user may actually own?
Cory
-- Cory
March 16, 2006 at 9:11 am
Cory,
I got this script from another thread on this site and it worked for me. Thanks to Scott Coleman.
set nocount on
create table #owned (objectname varchar(500))
exec sp_msforeachdb 'insert into #owned
select ''[?].['' + RTRIM(su.name) + ''].['' + RTRIM(so.name) + '']'' as objectname
from [?].dbo.sysobjects so inner join [?].dbo.sysusers su on su.uid = so.uid
where su.sid = (select sid from master.dbo.syslogins where name = ''<login>'')'
select * from #owned
drop table #owned
Greg
Greg
March 16, 2006 at 10:46 am
The solution Greg gives works to identify the objects. It queries every database to see what objects the login actually owns.
The sp_change_users_login is used to reassociate disconnected user accounts with logins, usually in a restore situation. For instance, you've restored a user database to a different SQL Server and created the logins that were on the SQL Server the database came from. However, the unique ID for the logins, the SID, doesn't match up. The stored procedure sp_change_users_login helps fix this.
K. Brian Kelley
@kbriankelley
March 16, 2006 at 2:11 pm
This gave me a good start - I use the sp_msforeachdb and foreachtable quite often. in this case, this statement does not work because the part inside the quotes is over 128 char's long. I was able to take the select statement and run it against each database and find where this user owned an object. Thanks!
-- Cory
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply