Post reply

Getting an error while removing a user

  • March 16, 2006 at 8:51 am

    #113752

    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

    #627065

    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

    #627101

    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

    #627160

    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