sql - SQL Server 上所有数据库中的孤立用户
问题描述
我知道这个 sp 返回孤立用户:EXEC sp_change_users_login @Action='Report'
我尝试在 SQL Server 上的所有数据库中查找孤立用户,但它没有返回真实结果。
DECLARE @name NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);
DECLARE @dbname NVARCHAR(MAX);
DECLARE Crs CURSOR
FOR
SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)
OPEN Crs
FETCH NEXT FROM Crs INTO @Name
/*WHILE @@ FETCH_STATUS = 0 It means keep returning the cursor by moving to the next record until there are no records left to circulate in the cursor.*/
WHILE @@FETCH_STATUS =0
BEGIN
SELECT name FROM sys.sysdatabases where name=@name
select @dbname=name from sysdatabases where name=@name
use @dbname
EXEC sp_change_users_login @Action='Report'
FETCH NEXT FROM Crs INTO @Name
END
/*CLOSE ve DEALLOCATE commad closed Cursor*/
CLOSE Crs
DEALLOCATE Crs
我的错误:消息 102,级别 15,状态 1,第 49 行“@dbname”附近的语法不正确。
如果我将数据库名称分配给@dbname 变量,问题就会得到解决。谢谢。
解决方案
在我看来,您似乎只想列出数据库中没有相关的所有用户,LOGIN
不包括 Loginless 对象。正如我提到sp_change_users_login
的已弃用,因此您不应该使用它,但您可以从sys.database_principles
andsys.server_principles
对象中获取信息。因此,对于单个数据库,您可以执行以下操作:
SELECT db_name(),
dp.[name] AS UserName,
dp.[sid] AS UserSID
FROM sys.database_principals dp
WHERE dp.[type] IN ('U','S')
AND NOT EXISTS (SELECT 1
FROM sys.server_principals sp
WHERE sp.sid = dp.sid)
AND authentication_type > 0;
我将(无耻地)使用我的sp_foreachdatabase
proc(sp_msforeachdb 的无游标版本),然后使用动态 SQL 将所有数据放入一个临时表中,并SELECT
在最后。这给出了这样的东西(我在这个答案的末尾包含了我的对象的定义):
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @PreCommand nvarchar(MAX) = N'CREATE TABLE #Orphans (DBName sysname,' + @CRLF +
N' UserName sysname,' + @CRLF +
N' UserSID varbinary(85));';
DECLARE @Command nvarchar(MAX) = N'INSERT INTO #Orphans (DBName, UserName, UserSID)' + @CRLF +
N'SELECT db_name(),' + @CRLF +
N' dp.[name] AS UserName,' + @CRLF +
N' dp.[sid] AS UserSID' + @CRLF +
N'FROM sys.database_principals dp' + @CRLF +
N'WHERE dp.[type] IN (''U'',''S'')' + @CRLF +
N' AND NOT EXISTS (SELECT 1' + @CRLF +
N' FROM sys.server_principals sp' + @CRLF +
N' WHERE sp.sid = dp.sid)' + @CRLF +
N' AND authentication_type > 0;';
DECLARE @PostCommand nvarchar(MAX) = N'SELECT DBName, UserName, UserSID FROM #Orphans; DROP TABLE #Orphans;';
DECLARE @CommandRun nvarchar(MAX);
EXEC master.dbo.sp_foreachdatabase @Pre_Command = @PreCommand,
@Command = @Command,
@Post_Command = @PostCommand,
@Exit_On_Error = 0,
@Skip_System = 1,
@Auto_Use = 1,
@Command_Run = @CommandRun OUTPUT;
这会将所有孤立的用户放入临时表中,并使用他们的数据库名称,然后SELECT
在最后将它们放入。
对象定义:
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE [name] = N'objectlist')
CREATE TYPE dbo.objectlist AS table ([name] sysname);
GO
USE master;
GO
CREATE OR ALTER PROC sp_foreachdatabase @Command nvarchar(MAX),
@Delimit_Character nchar(1) = N'?', --Character to be replaced with a delimit identified version of the datbaase name I.e. [master]
@Quote_Character nchar(1) = N'&', --Character to be replaced with a single quoted (') version of the datbaase name I.e. 'master'
@Skip_System bit = 0, --Omits master, msdb, tempdb and model. Ignored if @Database_List has data.
@Skip_User bit = 0, --Omits all user databases. Ignored if @Database_List has data.
@Database_List dbo.objectlist READONLY, --If @Skip_System and @Skip_User equal 1, and this is empty, an error will be thrown
@Auto_Use bit = 0, --Automatically starts each command agaisnt a database with a USE
@Exit_On_Error bit = 1, --If an error is occurs against a single database, the command will still be run against the remainder. Otherwise everything is rolled back
--This does not effect the @Pre_Command and @Post_Command statements
@Pre_Command nvarchar(MAX) = NULL, --Command to run before @Command. Does not use Character Replacements. Run against master DB.
@Post_Command nvarchar(MAX) = NULL, --Command to run after @Command. Does not use Character Replacements. Run against master DB.
@Command_Run nvarchar(MAX) = NULL OUTPUT --Returns the generated and replaced command, for trouble shooting
AS BEGIN
--Do some checking of passed values first
--Check that @Skip_System, @Skip_User aren't both 0 or that @Database_List has some rows
IF (@Skip_System = 1 AND @Skip_User = 1 AND NOT EXISTS (SELECT 1 FROM @Database_List))
THROW 62401, N'System and User databases cannot be skipped if a Database List is not supplied.', 16;
IF @Delimit_Character IS NULL OR @Delimit_Character = ''
THROW 62402, N'@Delimit_Character cannot have a value of NULL or ''''.', 16;
IF @Quote_Character IS NULL OR @Quote_Character = ''
THROW 62403, N'@Quote_Character cannot have a value of NULL or ''''.', 16;
IF @Skip_User IS NULL
THROW 62404, N'@Skip_User cannot have a value of NULL.', 16;
IF @Skip_System IS NULL
THROW 62405, N'@Skip_System cannot have a value of NULL.', 16;
IF @Auto_Use IS NULL
PRINT N'@Auto_Use has a value of NULL. Behaviour will be as if the value is 0.';
DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @RC int;
--Add the Pre Command to the batch
SET @Command_Run = ISNULL(N'/* --- Pre Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Pre_Command + @CRLF + @CRLF + N'/* --- Pre Command End. --- */', N'');
--Get the databases we need to deal with
--As @Database_List might be empty and it's READONLY, and we're going to do the command in database_id order we need another variable.
DECLARE @DBs table (database_id int,
database_name sysname);
IF EXISTS (SELECT 1 FROM @Database_List)
INSERT INTO @DBs (database_id,database_name)
SELECT d.database_id,
d.[name]
FROM sys.databases d
JOIN @Database_List DL ON d.[name] = DL.[name];
ELSE
INSERT INTO @DBs (database_id,database_name)
SELECT d.database_id,
d.[name]
FROM sys.databases d
WHERE (d.database_id <= 4 AND @Skip_System = 0) OR (d.database_id > 4 AND @Skip_User = 0);
SET @Command_Run = @Command_Run + @CRLF + @CRLF +
N'/* --- Begin command for each database. --- */' + @CRLF + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT off due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT OFF;' + @CRLF + N'DECLARE @Error nvarchar(4000);' ELSE N'SET XACT_ABORT ON;' END +
(SELECT @CRLF + @CRLF +
N'/* --- Running @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */' + @CRLF + @CRLF +
CASE WHEN @Auto_Use = 1 THEN N'USE ' + QUOTENAME(DB.database_name) + N';' + @CRLF + @CRLF ELSE N'' END +
N'BEGIN TRY' + @CRLF + @CRLF +
REPLACE(REPLACE(@Command, @Delimit_Character, QUOTENAME(DB.database_name)),@Quote_Character, 'N' + QUOTENAME(DB.database_name,'''')) + @CRLF + @CRLF +
'END TRY' + @CRLF +
N'BEGIN CATCH' + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N' SET @Error = N''The following error occured during the batch, but has been skipped:'' + NCHAR(13) + NCHAR(10) + ' + @CRLF +
N' N''Msg '' + CONVERT(nvarchar(6),ERROR_NUMBER()) + '', Level '' + CONVERT(nvarchar(6),ERROR_SEVERITY()) + '', State '' + CONVERT(nvarchar(6),ERROR_STATE()) + '', Line '' + CONVERT(nvarchar(6),ERROR_LINE()) + NCHAR(13) + NCHAR(10) +' + @CRLF +
N' ERROR_MESSAGE();' + @CRLF +
N' PRINT @Error;' + @CRLF +
N' SET @RC = ERROR_NUMBER();'
ELSE N' THROW;'
END + @CRLF +
N'END CATCH;' + @CRLF +
N'/* --- Completed @Command against database ' + QUOTENAME(DB.database_name,'''') + N'. --- */'
FROM @DBs DB
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)') + @CRLF + @CRLF +
CASE WHEN @Exit_On_Error = 0 THEN N'--Turning XACT_ABORT back on due to @Exit_On_Error parameter' + @CRLF + @CRLF + N'SET XACT_ABORT ON;' ELSE N'' END;
SET @Command_Run = @Command_Run + ISNULL(@CRLF + @CRLF + N'/* --- Post Command Begin. --- */' + @CRLF + @CRLF + N'USE master;' + @CRLF + @CRLF + @Post_Command + @CRLF + @CRLF + N'/* --- Post Command End. --- */', N'');
EXEC sp_executesql @Command_Run, N'@RC int OUTPUT', @RC = @RC;
SET @RC = ISNULL(@RC, 0);
RETURN @RC;
END;
GO
推荐阅读
- javascript - 从 JSON 中为 Javascript 中的不同键获取多个集合
- python - 元组的元组列表中的双重迭代
- c++ - 直接在 unordered_map 的方法中使用散列,而不是用户定义的对象产生散列
- javascript - 在两个 Web Worker 中拥有相同的 WebAssembly 实例
- typescript - 泛型类型中的打字稿值类型
- r - 删除字符串中的模式并将这些值更改为新列
- javascript - jquery过滤器菜单切换
- asp.net-core - 如何将基础架构与 .NetCore WebAPI 完全分离,还是应该?
- c# - Azure Functions Environment.GetEnvironmentVariable 返回 null
- macos - 使用 Xamarin Mac、C# 的跨浏览器、跨平台本机消息传递