How to list all database Users without Logins

Question

How to list all database Users without Logins

G Sunny

SSC Eights!

Points: 988
More actions
August 30, 2017 at 10:51 am

#402447

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

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

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 More actions · Answer 2

Chris,

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

Thanks
Sunny

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

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 More actions · Answer 4

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

Perry Whittle SSC Guru Points: 233855 More actions · Answer 5

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 [?];selectDB_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

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

"Ya can't make an omelette without breaking just a few eggs" 😉