主页 / user-212831

Saravana Kumar's questions

Martin Hope
Saravana Kumar
Asked: 2020-07-22 14:26:42 +0800 CST
  • 0

我对 SQL Server 很陌生。我有一个脚本可以在所有数据库中找到孤立用户并生成脚本以删除或映射用户以正确登录（如果可用）。

由于我想在大量服务器上运行脚本，我想到了使用 PowerShell 来帮助我。但我现在有两个问题：

  1. 脚本错误
  2. Power-shell 执行错误

问题 1：以下脚本本身返回输出，但仍然显示一些错误。我试图调试，但我无法这样做。

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;

结果是

在此处输入图像描述

但是由于该错误，实例中的所有数据库都会出现类似的错误

“tempdbfrom”附近的语法不正确。消息 102，级别 15，状态 1，第 3 行
“modelfrom”附近的语法不正确。消息 102，级别 15，状态 1，第 3 行
“msdbfrom”附近的语法不正确。消息 102，级别 15，状态 1，第 3 行
'dbamonfrom' 附近的语法不正确。消息 102，第 15 级，状态 1，第 3 行

问题 2：由于脚本是准确的（至少在我的环境中），所以我尝试使用 power-shell 运行它，如下所示。它只返回错误，没有输出

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

我已经尝试了使用 > 重定向输出的所有方法来启动和停止转录。他们似乎都没有捕捉到查询给出的结果，但是错误被准确捕捉到了..

如果任何一个问题得到解决，这对我来说将是一个很大的帮助。

sql-server powershell