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:
- Error with the script
- 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
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.
Best Answer
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.