Fix Orphan Users for Instance Migration

  • Comments posted to this topic are about the item Fix Orphan Users for Instance Migration

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • Thank you for the script.

  • Useful script, if you have only "sql authenticated" accounts in your environment.

    Two Drawback that I could see are:

    1. Script will NOT be useful for checking orphaned "Window users" and "Window groups" as sp_change_users_login 'report' will only declare sql authentication accounts, not window's.

    2. Script will NOT list out the users that were created with "loginless" clause. We have many of those in our environment.Read more about them here: (http://blog.sqlauthority.com/2012/10/04/sql-server-importance-of-user-without-login-t-sql-demo-script/

    To achieve number 1 above, you may need to query on something like below:

    SELECT @@servername, db_name(),

    database_principals.name,

    database_principals.type_desc,

    database_principals.Default_Schema_Name,

    SUSER_SNAME(database_principals.SID),

    CASE WHEN syslogins.loginname IS NULL THEN 'No' ELSE 'Yes' END AS LoginExists

    FROM sys.database_principals

    LEFT OUTER JOIN sys.syslogins

    ON database_principals.name COLLATE Latin1_General_CI_AS = syslogins.loginname COLLATE Latin1_General_CI_AS

    WHERE database_principals.type_desc IN ('SQL_USER','WINDOWS_USER','WINDOWS_GROUP')

    AND database_principals.principal_id > 4

    AND database_principals.SID not in (SELECT SID

    FROM sys.server_principals)

    AND database_principals.name <> 'guest'

  • Arshpreet (1/4/2016)


    Useful script, if you have only "sql authenticated" accounts in your environment.

    Two Drawback that I could see are:

    1. Script will NOT be useful for checking orphaned "Window users" and "Window groups" as sp_change_users_login 'report' will only declare sql authentication accounts, not window's.

    2. Script will NOT list out the users that were created with "loginless" clause. We have many of those in our environment.Read more about them here: (http://blog.sqlauthority.com/2012/10/04/sql-server-importance-of-user-without-login-t-sql-demo-script/

    Thanks for the extra insight.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply