To generate a script to drop or map the orphan users in a SQL Server Instance

Question

I am very new to SQL Server. I have a script which find the orphan users in all the databases and generate the script to either drop or map the user to correct login (If available).

Since i want to run the script in vast number of servers, I thought of using PowerShell to help me. But i got 2 problem now:

1. Error with the script
2. Error with Power-shell execution

Problem 1: Following script itself returns output, but still it shows some error. I tried to Debug, But i was unable to do so.

DECLARE @orphan_users TABLE (
 Server NVARCHAR(MAX),
 DBName sysname,
 [user_name] sysname,
 type_desc nvarchar(60),
 default_schema_name sysname NULL,
 create_date datetime,
 modify_date DATETIME,
 [sid] VARBINARY(85),
 cr_type int
); 
INSERT INTO @orphan_users (Server,
 DBname,
 [user_name],
 type_desc,
 default_schema_name,
 create_date,
 modify_date,
 [sid],
 cr_type
) 
EXEC sp_msforeachdb 
'use [?] 
SELECT 
 @@SERVERNAME 
 ,DB_NAME() DBname 
 ,name 
 ,type_desc 
 ,default_schema_name 
 ,create_date 
 ,modify_date 
 ,[sid] 
 ,Datalength(sid) AA 
from sys.database_principals 
where type in (''S'') 
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') ) 
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'' ,''MS_DataCollectorInternalUser'')'; 
DECLARE @db_users TABLE (
 [LogInAtServerLevel] NVARCHAR(MAX),
 [UserAtDBLevel] NVARCHAR(MAX), 
 DBname sysname
); 
INSERT INTO @db_users ([LogInAtServerLevel], [UserAtDBLevel],DBname) 
EXEC sp_msforeachdb 
'use [?] 
SELECT 
 susers.[name],
 users.[name],
 DB_NAME() DBname       
from sys.database_principals users 
inner join sys.database_role_members link 
  on link.member_principal_id = users.principal_id 
inner join sys.database_principals roles 
  on roles.principal_id = link.role_principal_id 
inner join sys.server_principals susers 
  on susers.sid = users.sid'; 
SELECT 
 a.Server,
 a.DBName,
 a.user_name,
 b.name,cr_type,
 c.UserAtDBLevel, 
 CASE 
 WHEN (b.name IS NULL) and (cr_type > 17) 
 THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';' 
 WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is null) 
 THEN 'USE ' + QUOTENAME(a.DBname) + '; ALTER USER ' + QUOTENAME(a.user_name) + ' WITH LOGIN = ' + QUOTENAME(b.name)+';' 
 WHEN (b.name IS not NULL) and (cr_type < 17) and (c.UserAtDBLevel is not null) 
 THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';' 
 WHEN (b.name IS NULL) 
 THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';' 
 WHEN (b.name IS not NULL) and (cr_type > 17) 
 THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';' 
 END as [remediation] 
FROM ((@orphan_users a 
 left join sys.server_principals b on a.user_name = b.name) 
 left join @db_users c on c.DBname=a.DBName and c.LogInAtServerLevel=b.name) 
ORDER BY a.user_name; 

and results are

enter image description here

But with the error, similar error appear for all databases in the instance

Incorrect syntax near 'tempdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'modelfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'msdbfrom'. Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'dbamonfrom'. Msg 102, Level 15, State 1, Line 3

Problem 2: Since the Script is accurate (at least in my Environment) so i tried to run it with power-shell as follows. It only returns the errors, no Output

Invoke-SqlCmd `
 -InputFile "D:\temp\up.sql" `
 -ServerInstance 'abc\sql2012' `
 -IncludeSqlUserErrors

I have tried all the means from redirection of output using > to start and stop transcript. None of them seemed to capture the result that the query gives, However the errors are captured accurately..

It would be a great help for me if any one of the problem is solved.

Answer 1

Probably your editor has a problem or I do not know what exactly the problem is. But I cleared all blank in msforeachdb and run it, so the errors are disappeared.

DECLARE @orphan_users TABLE (
 Server nvarchar(max),
 DBName sysname,
 [user_name] sysname,
 type_desc nvarchar(60),
 default_schema_name sysname NULL,
 create_date datetime,
 modify_date datetime,
 [sid] varbinary(85),
 cr_type int
);
INSERT INTO @orphan_users (Server, DBname, [user_name], type_desc, default_schema_name, create_date, modify_date, [sid], cr_type)
EXEC sp_MSforeachdb '
USE [?]
SELECT 
@@SERVERNAME 
,DB_NAME() DBname 
,name 
,type_desc 
,default_schema_name 
,create_date 
,modify_date 
,[sid] 
,Datalength(sid) AA 
from sys.database_principals where type in (''S'') 
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') ) 
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'' ,''MS_DataCollectorInternalUser'')';
DECLARE @db_users TABLE (
 [LogInAtServerLevel] nvarchar(max),
 [UserAtDBLevel] nvarchar(max),
 DBname sysname
);
INSERT INTO @db_users ([LogInAtServerLevel], [UserAtDBLevel], DBname)
EXEC sp_MSforeachdb '
USE [?]
SELECT susers.[name],users.[name],DB_NAME() AS DBNAME
from sys.database_principals users 
inner join sys.database_role_members link 
on link.member_principal_id = users.principal_id 
inner join sys.database_principals roles 
on roles.principal_id = link.role_principal_id 
inner join sys.server_principals susers 
on susers.sid = users.sid';
SELECT
 a.Server,
 a.DBName,
 a.user_name,
 b.name,
 cr_type,
 c.UserAtDBLevel,
 CASE
 WHEN (b.name IS NULL) AND
 (cr_type > 17) THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
 WHEN (b.name IS NOT NULL) AND
 (cr_type < 17) AND
 (c.UserAtDBLevel IS NULL) THEN 'USE ' + QUOTENAME(a.DBname) + '; ALTER USER ' + QUOTENAME(a.user_name) + ' WITH LOGIN = ' + QUOTENAME(b.name) + ';'
 WHEN (b.name IS NOT NULL) AND
 (cr_type < 17) AND
 (c.UserAtDBLevel IS NOT NULL) THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
 WHEN (b.name IS NULL) THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
 WHEN (b.name IS NOT NULL) AND
 (cr_type > 17) THEN 'USE ' + QUOTENAME(a.DBname) + '; DROP USER ' + QUOTENAME(a.user_name) + ';'
 END AS [remediation]
FROM ((@orphan_users a
LEFT JOIN sys.server_principals b
 ON a.user_name = b.name)
LEFT JOIN @db_users c
 ON c.DBname = a.DBName
 AND c.LogInAtServerLevel = b.name)
ORDER BY a.user_name;

• Saravana Kumar: Thanks Yunus..You made my day..it was eating my head for last couple of days..thank you so much

Answer 2

The dbatools module has already solved this for you.

• Get-DbaDbOrphanUser - reports all users in one or more databases with no corresponding login
• Repair-DbaDbOrphanUser - finds orphan users and if there is a login with the same name, it will remap them.
• Remove-DbaDbOrphanUser - locates orphaned users and drops them from the database (you can do this with Repair-DbaDbOrphanUser -RemoveNotExisting as well).

• Saravana Kumar: Thanks Alroc for suggesting. that was the First thing i tried. But it somehow it was showing others users also as orphan..like dbo,all_schema_user..so i didn't want to take a ris and this script was inhouse and it was approved in my team..so i thought i would go by this

Answer 3

Have you considered that SQL Server accepts the use of a user without login and, although it looks like an orphaned user, it could have been created like that by design?

The CREATE USER doc says:

The WITHOUT LOGIN clause creates a user that is not mapped to a SQL Server login. It can connect to other databases as guest. Permissions can be assigned to this user without login and when the security context is changed to a user without login, the original users receives the permissions of the user without login. See example D. Creating and using a user without a login.

So, be careful when automating that task because you might break something if you're not familiar with the databases you're working with.

• Saravana Kumar: Yeah.and also users created in contained databases, all schema owner in SSIDB,MDW operator in MDW database and many more..I checked all these things..in our environmant we dont use contained databases and all the Users should be mapped to a login other it is considered as orphan and guest user is also disabled in my environment.