How do I remove a migrated dbo aliased user in SQL 2008 i.e.  \username

Posted on 2011-10-14
Last Modified: 2012-05-12
I recently migrated a SQL 2005 database to SQL 2008R2 - I thought I had dropped all the old aliased users to dbo  but I missed one prior to my final migration backup.  As  sp_dropalias is no longer supported in SQL 2008 and I cannot directly delete from sysusers in SQL 2008 - any ideas on how I can remove the '\username' entry from sysusers ?
Question by:Majellao
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Are you sure you cannot use sp_dropalias ??
    LVL 59

    Expert Comment

    by:Kevin Cross
    Interesting, the documentation for Denali claims it still works there too for backwards compatibility, but it does not work. Think that is a bug if it does not work in SQL 2008 R2. Or the documentation needs serious updating.
    LVL 59

    Accepted Solution

    I forgot I had SQL 2008 R2 loaded at work. One application needed regular SQL 2008, so I had to load that but just left both instances. Anyway, I asked some SQL folks not thinking about my multiple instances and when one replied back it worked, I went back to testing myself. We have confirmed it works on SQL 2008, 2008 R2, just not SQL Denali CTP3; therefore, may original question stands: are you sure you cannot use sp_dropalias?

    Author Comment

    Tks for your help you are correct it does work - problem was that the alias in user database didnt have a matching login, I created a  login, ran the execute sp_change_users_login 'auto_fix','\username' and  then sp_dropalias worked, then deleted the login  -  all fixed

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    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!

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now