[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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

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 ?
0
Majellao
Asked:
Majellao
  • 3
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Are you sure you cannot use sp_dropalias ??
http://msdn.microsoft.com/en-us/library/ms177513(v=SQL.105).aspx
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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?
0
 
MajellaoAuthor Commented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now