sql server:列出每个数据库的用户

6qftjkof  于 2021-07-29  发布在  Java
关注(0)|答案(2)|浏览(420)

我有几个数据库。我想列出database/security/users文件夹中的所有用户,user1和user2,如下所示:

我提出了这个问题:

SELECT name, type_desc
FROM nameOfMyDatabase.sys.database_principals
WHERE name NOT LIKE 'public' 
  AND name NOT LIKE 'dbo'
  AND name NOT LIKE 'guest'
  AND name NOT LIKE 'INFORMATION_SCHEMA'
  AND name NOT LIKE 'sys'
  AND name NOT LIKE 'db%';

它返回我需要的用户,除了数据库中的默认用户。
现在我需要一个查询,它可以对我拥有的所有数据库执行此操作,而不必知道它们的名称或任何关于它们的信息。
我找到了 sp_MSforeachdb ,但不建议多个网站在线使用。但如果这是我唯一的选择,它是怎么工作的?如何在查询的sql语法中引用当前数据库(假设它像一种for each循环一样工作?)

EXEC sp_MSforeachdb 'USE ? SELECT name, type_desc
FROM ''?''.sys.database_principals
Where name not like "public" 
and name not like "dbo"
and name not like "guest"
and name not like "INFORMATION_SCHEMA"
and name not like "sys
and name not like "db%"'

(在这段代码中,我使用“”?“”作为对当前数据库的引用,但它返回错误)此外,我只希望在我创建的数据库上运行查询,而不希望在生成的系统数据库(master、model、msdb、tempdb)上运行查询
除了sp\u msforeachdb之外,还有别的方法可以让这个工作吗?

smdncfj3

smdncfj31#

请尝试以下操作

EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''msdb'',''tempdb'', ''model'')
BEGIN
USE [?]
SELECT name, type_desc
FROM [?].sys.database_principals
WHERE name NOT LIKE ''public'' 
  AND name NOT LIKE ''dbo''
  AND name NOT LIKE ''guest''
  AND name NOT LIKE ''INFORMATION_SCHEMA''
  AND name NOT LIKE ''sys''
  AND name NOT LIKE ''db%''

END'
``` `sp_MsForEachDb` 所以这就是为什么不推荐它。但是,如果您只需要在单个查询中使用它,而不是在生产代码中使用它,请随意使用它。 `?` 被你的数据库名替换,你在哪里失踪了 `[` 以及 `]` 在数据库名称周围。
您还使用双引号而不是两个单引号。因为在字符串中使用单引号,所以需要使用单引号对其进行转义。
0pizxfdo

0pizxfdo2#

还有很多其他用户创建的版本 sp_msforeachdb 在外面。我将(无耻地)在这里使用我自己的版本,您可以在这里阅读更多内容:spu msforeachdb的无光标版本。为完整起见,我将包括以下ddl:

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE [name] = N'objectlist')
    CREATE TYPE dbo.objectlist AS table ([name] sysname);
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
        THROW 62402, N'@Delimit_Replace cannot have a value of NULL.', 16;

    IF @Quote_Character IS NULL
        THROW 62403, N'@Quoted_Replace cannot have a value of NULL.', 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

如果需要,可以将其添加为系统对象;我在这篇文章中谈到了如何做。
输入后,可以进行查询,并根据需要输入替换字符:

USE master;
GO

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT & AS databasename, name, type_desc' + @CRLF + --& by default injects the database's name in quotes ('') and a N prefix
           N'FROM ?.sys.database_principals' + @CRLF + --? by default injects the database's name in brackets ([])
           N'WHERE name NOT LIKE ''public''' + @CRLF +
           N'  AND name NOT LIKE ''dbo''' + @CRLF +
           N'  AND name NOT LIKE ''guest''' + @CRLF +
           N'  AND name NOT LIKE ''INFORMATION_SCHEMA''' + @CRLF +
           N'  AND name NOT LIKE ''sys''' + @CRLF +
           N'  AND name NOT LIKE ''db%'';';
DECLARE @CommandRun nvarchar(MAX);
EXEC sp_foreachdatabase @Command = @SQL, --The above SQL is the command to run
                        @Skip_system = 1, --Skips system databases
                        @Auto_Use = 1, --Automatically puts a USE at the start of each database
                        @Command_Run = @CommandRun OUTPUT; --For debugging

PRINT @CommandRun;
GO

相关问题