User has lost Login Name

Posted on 2006-03-23
Last Modified: 2008-01-09
Hello all. We have a Citrix database with a server login called Citrix_User, and User in that database with the same name. I had to restart the server yesterday to swap some drives. Today when I go to the properties of Citrix_User in the database the login shows <none>. When I try and delete that user it says "The selected user cannot be dropped because the user owns objects". Can you guys tell me how to both get that login back and how to delete the user account (I've created another one that Citrix is now using, but need to know in case that happens again).  Thanks, Jordan
Question by:JordanKingsley
    LVL 75

    Accepted Solution

    Hi JordanKingsley,
    in order to change the ownership of the objects you can use the following

    DECLARE @old sysname, @new sysname, @sql varchar(1000)
    SELECT @old = 'Citrix_User' ,
    @new = 'dbo' ,

    EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''EXECUTE sp_MSforeachtable @sql

    then try to recreate the login

    Author Comment

    I'm sure I understand what that did. How do I delete that user?
    LVL 10

    Expert Comment

    In order to be able to log in with the user you should just need to set the (default) database to a valid database. This normally gets set to none if the database that was originally the user's default database has been dropped. This includes if the database was dropped and re-created.

    Author Comment

    I see, but how to I delete a user that I don't need anymore and I still get the message:

    "The selected user cannot be dropped because the user owns objects"

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    731 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

    16 Experts available now in Live!

    Get 1:1 Help Now