Post reply

Fix Orphan Users for Instance Migration

  • December 18, 2015 at 10:18 am

    #303388

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

    Regards,
    Ajay Dwivedi
    MCP SQL DBA

  • January 4, 2016 at 6:28 am

    #1849197

    Thank you for the script.

  • January 4, 2016 at 8:02 am

    #1849231

    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'

  • April 26, 2016 at 4:40 pm

    #1874822

    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