Sql-server – Delete orphaned SQL Users

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db       VARCHAR(70),
                        SID      VARBINARY(85),
                        stat     VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
     'INSERT INTO #all_users  
         SELECT ''?'', CONVERT(varbinary(85), sid) , 
          CASE WHEN  r.role_principal_id IS NULL AND p.major_id IS NULL 
          THEN ''no_db_permissions''  ELSE ''db_user'' END
         FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p 
           ON u.principal_id = p.grantee_principal_id  
           AND p.permission_name <> ''CONNECT''
          LEFT JOIN [?].sys.database_role_members r 
           ON u.principal_id = r.member_principal_id
          WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''

IF EXISTS 
   (SELECT l.name
       FROM   sys.server_principals l
              LEFT JOIN sys.server_permissions p
                   ON  l.principal_id = p.grantee_principal_id
                   AND p.permission_name <> 'CONNECT SQL'
              LEFT JOIN sys.server_role_members r
                   ON  l.principal_id = r.member_principal_id
              LEFT JOIN #all_users u
                   ON  l.sid = u.sid
       WHERE  r.role_principal_id IS NULL
              AND l.type_desc <> 'SERVER_ROLE'
              AND p.major_id IS NULL
   )
BEGIN
    SELECT DISTINCT l.name     LoginName,
           l.type_desc,
           l.is_disabled,
           ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users') 
           db_perms,
           CASE 
                WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN 
                     'no_srv_permissions'
                ELSE 'na'
           END                 srv_perms
    FROM   sys.server_principals l
           LEFT JOIN sys.server_permissions p
                ON  l.principal_id = p.grantee_principal_id
                AND p.permission_name <> 'CONNECT SQL'
           LEFT JOIN sys.server_role_members r
                ON  l.principal_id = r.member_principal_id
           LEFT JOIN #all_users u
                ON  l.sid = u.sid
    WHERE  l.type_desc <> 'SERVER_ROLE'
           AND ((u.db IS NULL
                       AND p.major_id IS NULL
                       AND r.role_principal_id IS NULL
                 )
                   OR (u.stat = 'no_db_permissions'
                          AND p.major_id IS NULL
                          AND r.role_principal_id IS NULL
                      ))
    ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName            type_desc  is_disabled         db_perms                                        srv_perms
app_agentjobreader   SQL_LOGIN      0       no_db_permissions, but is user in mydb DB           no_srv_permissions
app_web_general      SQL_LOGIN      0       no_db_permissions, but is user in mydb_second DB    no_srv_permissions
app_web_maker        SQL_LOGIN      0       no_db_permissions, but is user in mydb_third DB     no_srv_permissions

There are a couple of possibilities.

First if the orphaned user is a windows login/group (type U or G) then no problem.

-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If it's a "SQL Login" (type S) and the login exists then again no problem.

-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.

-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';

Don't forget to make sure your password meets your windows policy requirements. If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).

I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/