Home Forums Notification Services Administration Urgent: cannot remove database role, ''dbo'' from my permissions RE: Urgent: cannot remove database role, ''''dbo'''' from my permissions

  • Try running orphan user script in each and every user databases. This will drop and clear the anamolies in the user-login conflicts. As you are a sysadmin, you can still be in the group and you will have all the privileges on the system. I want understand why you want to be a user again. Recreate all the database objects with "dbo".

    -- Orphan user Security Fix:

    DECLARE

    @username varchar(25)

    DECLARE fixusers CURSOR

    FOR

    SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

    and suser_sname(sid) is null and [name] in (select [name] from master.dbo.syslogins)

    ORDER BY name

    OPEN fixusers

    FETCH NEXT FROM fixusers

    INTO @username

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC sp_change_users_login 'update_one', @username, @username

    FETCH NEXT FROM fixusers

    INTO @username

    END

    CLOSE fixusers

    DEALLOCATE fixusers

    GO

    Thanks,

    .A.