locked
Orphan users and deleting logins but not users RRS feed

  • Question

  • Hello there,

    First off I'm sorry if this was already posted. I've researched for the last two hours and can't seem to find the answer I'm looking for.

    So its to my understanding then when you delete a Server Login, if you have a user that uses that Login and THAT LOGIN only, once you delete the login the user is now an orphan user.

    I've been running the orphan user sp (

    USE <database_name>;
    GO;
    sp_change_users_login @Action='Report';
    GO;

    )

    but recently I've deleted a login, the user still exists on the database, but it should now be an orphan user right? I know for a fact this user is not in any other group, and it should be an orphan user. So why isn't it? :(


    Friday, January 2, 2015 6:58 PM

Answers

All replies

  • Hi Krystina,


    Your question may be that sp_change_users_login didn't detect an orphan user scenario? If so, I would recommend checking sys.server_principals and sys.database_principals to verify for certain (and if you have a reproduction of the exact issue, please post here).

    Here is a demo that walks through the by-design behavior (dropping login will not equate to dropping a user, and that user will be an orphaned user).  

    USE [master]
    GO

    -- Create the login
    CREATE LOGIN [TestUser] WITH PASSWORD=N'Test!!1234',
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    GO

    -- Create a database
    CREATE DATABASE [Test];
    GO

    -- Create the user in that database
    USE [Test]
    GO
    CREATE USER [TestUser] FOR LOGIN [TestUser] WITH DEFAULT_SCHEMA=[dbo];
    GO

    -- Drop the * login * (not user)
    DROP LOGIN [TestUser];

    -- You should no longer see this long at the SQL Server instance (server) scope
    SELECT [SP].[name], [SP].[principal_id], [SP].[sid], [SP].[type],
           [SP].[type_desc], [SP].[is_disabled], [SP].[create_date],
           [SP].[modify_date], [SP].[default_database_name],
           [SP].[default_language_name], [SP].[credential_id]
    FROM sys.[server_principals] AS [SP]
    WHERE name = 'TestUser';

    -- But we didn't drop the user, so you * should * still see it within the database (by-design)
    USE [Test];
    GO

    SELECT [DP].[name], [DP].[principal_id], [DP].[type], [DP].[type_desc],
           [DP].[default_schema_name], [DP].[create_date], [DP].[modify_date],
           [DP].[owning_principal_id], [DP].[sid], [DP].[is_fixed_role]
    FROM sys.[database_principals] AS [DP]
    WHERE name = 'TestUser';

    -- And your orphan proc call will report it as well
    EXEC sp_change_users_login @Action='Report';


    Best Regards, Joe Sack, MVP, SQL MCM | SackHQ.com

    Friday, January 2, 2015 7:18 PM
  • Hey Joe,

    Thank you for the quick response!

    I tried what you suggested an it indeed worked. I think I may have found the problem. This login is a windows login. Looking around online I see that this stored procedure may not support windows logins?

    Friday, January 2, 2015 7:44 PM
  • "Orphaned users" only applied to SQL Server logins, not Windows AD logins.

    Finding Windows logins which are no longer valid takes more work.

    Please see:

    http://www.mssqltips.com/sqlservertip/1864/identify-orphaned-windows-logins-and-groups-in-sql-server-with-spvalidatelogins/

    Friday, January 2, 2015 8:00 PM