August 30, 2017 at 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
August 30, 2017 at 12:35 pm
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;
September 1, 2017 at 9:46 am
Chris,
Thanks for your update, When i ran the query it is giving 0 rows affected.
Thanks
Sunny
September 1, 2017 at 10:31 am
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?
September 1, 2017 at 2:00 pm
Thanks Chris, It works when i run for each database. appreciate your response.
September 4, 2017 at 5:42 am
G Sunny - Wednesday, August 30, 2017 10:51 AMHello,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"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy