How to list all database Users without Logins

  • G Sunny

    SSC Eights!

    Points: 988

    Hello,

    Can some one provide me script to list all database users which are not having logins? I want to  sync all logins in primary and secondary server in one of our Always-On servers.
    I just want query all databases at once, 

    Thanks in advance
    Sunny

  • Chris Harshman

    SSC-Forever

    Points: 41845

    for SQL authenticated users it's fairly easy, for Windows authenticated it's a bit more complex because you have to consider groups:
    CREATE TABLE #OrphanUsers (UserName nvarchar(128), UserType char(1));

    --SQL Users
    INSERT INTO #OrphanUsers
    SELECT dp.name, dp.type
      FROM sys.database_principals dp
        LEFT OUTER JOIN sys.server_principals sp ON dp.sid = sp.sid
      WHERE dp.type = 'S'
        AND sp.sid IS NULL
        AND dp.authentication_type_desc <> 'NONE';

    --Windows Users
    DECLARE @sqlcmd nvarchar(4000), @username nvarchar(128);

    CREATE TABLE #ADinfo (
        AccountName nvarchar(128),
        AccountType char(8), --user or group
        Privilege char(9), --admin, user, or null.
        MappedLogin nvarchar(128), --the mapped login name by using the mapped rules   
        PermissionPath nvarchar(128));

    DECLARE cur_users CURSOR FAST_FORWARD FOR
    SELECT dp.name
      FROM sys.database_principals dp
      WHERE dp.type IN ('G','U');

    OPEN cur_users;
    FETCH NEXT FROM cur_users INTO @username;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        TRUNCATE TABLE #ADinfo
        SET @sqlcmd = N'INSERT INTO #ADinfo EXEC xp_logininfo N''' + @username + N''',''all''';
        BEGIN TRY
            EXEC sp_executesql @sqlcmd;
        END TRY
        BEGIN CATCH
        END CATCH
       
        IF NOT EXISTS (SELECT * FROM #ADinfo WHERE MappedLogin IS NOT NULL)
            INSERT INTO #OrphanUsers VALUES (@username, 'W');
       
        FETCH NEXT FROM cur_users INTO @username;
    END

    CLOSE cur_users;
    DEALLOCATE cur_users;

    DROP TABLE #ADinfo;
    SELECT * FROM #OrphanUsers;
    DROP TABLE #OrphanUsers;

  • G Sunny

    SSC Eights!

    Points: 988

    Chris,

    Thanks for your update, When i ran the query it is giving 0 rows affected.

    Thanks
    Sunny

  • Chris Harshman

    SSC-Forever

    Points: 41845

    You would have to run that in each database, I couldn't figure out an easy way to do one script that automatically runs for all databases.  I probably should have explained that better.  I've used xp_logininfo a number of times to find or troubleshoot Windows authenticated users and their logins so I'm not sure offhand what the issue is.  Have you tried running xp_logininfo manually for some you suspect to be orphaned?

  • G Sunny

    SSC Eights!

    Points: 988

    Thanks Chris, It works when i run for each database. appreciate your response.

  • Perry Whittle

    SSC Guru

    Points: 233794

    G Sunny - Wednesday, August 30, 2017 10:51 AM

    Hello,

    Can some one provide me script to list all database users which are not having logins? I want to  sync all logins in primary and secondary server in one of our Always-On servers.
    I just want query all databases at once, 

    Thanks in advance
    Sunny

    For all databases use the following

    if object_id('tempdb..#users', 'U') is not null

    BEGIN

    DROP TABLE #users

    END

    CREATE TABLE #users (

    dbname varchar(128),

    dbusername varchar(128),

    create_date datetime, --user or group

    modifydate datetime,

    owningid int, --admin, user, or null.

    );

    insert into #users

    exec sp_MSforeachdb @command1 =

    'use [?];select DB_NAME()

    , dp.name

    , dp.create_date

    , dp.modify_date

    , dp.owning_principal_id

    from sys.database_principals dp

    left outer join sys.server_principals sp

    on dp.sid = sp.sid

    where sp.name is null and

    dp.type <> ''R'' and dp.principal_id > 4'

    select * from #users

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

Viewing 6 posts - 1 through 6 (of 6 total)

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