locked
Removing Orphaned SQL 6.5 User Aliases from Databases RRS feed

  • Question

  • Hi,

    I have inherited some databases that have old SQL Server 6.5 aliases in them.  The databases had already been transferred to SQL 2000 when I took over (how they were moved from 6.5 to 2000, I don't know).  So, they have these aliases in them, that is, I get rows from this query:

    select * from dbo.sysusers
    where isaliased = 1
    

    On row in particular is:

    uid  status name      sid                
    ------ ------ -------------- -----------------------------------
    5   16   \aDalton    0x81345DC7C969F84DA2E1C7852DFF48D9 
    
    

    Now I am working on upgrading these servers to SQL 2008 R2, and I am trying to get rid of them.  I found another thread (http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a3aa00be-b813-465d-8b55-6b0a45dd13e7) that pointed me to the [sp_dropalias] system stored procedure.  However, that proc will not work for me, as the proc takes a login as the input, and the associated logins are long gone from my systems!

    So created a login with a sid from the query above.

    create login [Dalton]
     with password = 'asdfASDF1234',
     sid = 0x81345DC7C969F84DA2E1C7852DFF48D9
    

    Then I tried...

    sp_dropalias 'Dalton'
    

    which gave me the error message "Msg 15134, Level 16, State 1, Procedure sp_dropalias, Line 36 No alias exists for the specified user." So I figured I might need a user for login as well as the alias, so I tried this:

    create user [DaltonXXX] for login [Dalton]
    
    

    which gave me the error message "Msg 15022, Level 16, State 1, Line 2 The specified user name is already aliased."  Arrgh!!!

    Does anyone know how to get rid of these aliases in SQL 2000 or SQL 2008?

     


    Thank you, Dan
    Tuesday, January 25, 2011 12:02 AM

Answers

  • Hi Dan,

    Based on my tests, resove this issue, you need to create a login with the same name and sid as alias user name (remove '\') and its sid. For your reference, I use the following T-SQL statement to resolve this issue:

    -- Get the alias user and its sid
    -- Suppose the results we get are:
    -- alias user: \AliasLogin, and sid: 0x4EAF544D095570419CF280E6C9D103A6
    USE <db_name>;
    GO
    SELECT * FROM sys.sysusers;
    EXEC sp_helpuser;
    GO
    
    USE master;
    GO
    -- Create a temp login which is required to map the alias user
    -- The login name is required to be the same of the alias user name (removing '\')
    CREATE LOGIN AliasLogin WITH password = 'Password01!',
    sid = 0x4EAF544D095570419CF280E6C9D103A6;
    GO
    
    -- Delete the alias user
    USE <db_name>;
    GO
    EXEC sp_dropalias 'AliasLogin';
    GO
    
    -- Clean up temp login
    USE master;
    GO
    DROP LOGIN AliasLogin;
    GO
    
    Hope this helps. Please let us know if you need more help.
    Best Regards,
    Chunsong Feng (MSFT)

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, January 26, 2011 10:01 AM

All replies

  • Could you please connect to the database and run below query.

    sp_change_users_login 'Report'
    

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, January 25, 2011 2:21 AM
  • Hi Dan,

    Based on my tests, resove this issue, you need to create a login with the same name and sid as alias user name (remove '\') and its sid. For your reference, I use the following T-SQL statement to resolve this issue:

    -- Get the alias user and its sid
    -- Suppose the results we get are:
    -- alias user: \AliasLogin, and sid: 0x4EAF544D095570419CF280E6C9D103A6
    USE <db_name>;
    GO
    SELECT * FROM sys.sysusers;
    EXEC sp_helpuser;
    GO
    
    USE master;
    GO
    -- Create a temp login which is required to map the alias user
    -- The login name is required to be the same of the alias user name (removing '\')
    CREATE LOGIN AliasLogin WITH password = 'Password01!',
    sid = 0x4EAF544D095570419CF280E6C9D103A6;
    GO
    
    -- Delete the alias user
    USE <db_name>;
    GO
    EXEC sp_dropalias 'AliasLogin';
    GO
    
    -- Clean up temp login
    USE master;
    GO
    DROP LOGIN AliasLogin;
    GO
    
    Hope this helps. Please let us know if you need more help.
    Best Regards,
    Chunsong Feng (MSFT)

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, January 26, 2011 10:01 AM
  • Hi,

    I found this blog when attempting to fix the same issue.  However,

    exec

    sp_dropalias @loginame='templogin' returns

    Msg 15134, Level 16, State 1, Procedure sp_dropalias, Line 36

    No alias exists for the specified user.

     

    yet sp_helpuser does return the aliases being mapped:

    LoginName UserNameAliasedTo

    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    templogin dbo

    templogin1 dbo

    templogin2 dbo

    Tuesday, January 31, 2012 6:20 PM