主页 / dba / 问题 / 234881
Accepted
user1930901
Asked: 2019-04-17 00:59:57 +0800 CST

具有各自数据库名称的孤立用户[重复]

  • 772

我需要获取每个数据库中孤立用户的列表，以及它们所在的数据库名称。我做了下面的操作，给出了每个数据库中的孤立用户，但我不知道如何在下一个包含另一列给每个用户找到显示在哪个数据库中。我尝试使用 DB_NAME() 但无济于事。

use master;
GO

CREATE PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000) 
SELECT @command = 'use ? 
if DB_ID("?")>4
EXEC sp_change_users_login ''Report'' '
EXEC sp_MSforeachdb @command
END

CREATE TABLE tempTable (
    user nvarchar(1000),
    User_sid sql_variant 
)

INSERT INTO tempTable
EXEC master..orphaned_u

SELECT * FROM  dbo.tempTable

我已经看过Find orphaned users但最好我使用上面的代码并找到一种方法在具有已内置存储过程 sp_change_users_login ''Report'' 的列中显示数据库名称

sql-server users

1 个回答

  1. Best Answer

    如果您必须使用解决问题的非常精细的方法sp_change_users_login

    通过使用游标并插入存储过程中的临时表

    use master;
GO

ALTER PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000) ;
DECLARE @dbname nvarchar(500);

CREATE TABLE #temptable (
    ID INT IDENTITY(1,1),
    [Dbname] nvarchar(500),
    [user] nvarchar(1000),
    [User_sid] sql_variant 
);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR select name from sys.databases where database_id > 4 and state_desc = 'ONLINE';
OPEN C
FETCH NEXT FROM C INTO @dbname;
WHILE @@FETCH_STATUS =0
    BEGIN
    SET @command = '
    use '+QUOTENAME(@dbname)+'

    EXEC sp_change_users_login ''Report'' '
    INSERT INTO #temptable([user],[User_sid])
    EXEC(@Command);
    IF @@ROWCOUNT >0
    BEGIN
    ;WITH CTE  AS
    (
        SELECT TOP(1) * FROM #temptable
        ORDER BY ID DESC
        )
        UPDATE CTE
        SET [Dbname] = @dbname;
    END
    FETCH NEXT FROM C INTO @dbname;
    END
SELECT * FROM #temptable;
END

    如果您不需要，我建议不要将程序/数据放在主数据库中。

    运行过程

    EXEC orphaned_u

    返回我的两个孤立用户

    ID  Dbname       user   User_sid
1   my_test      test   0x4B0387CBECE23440B207E17B9F01160F
2   my_test_old  test2  0xF6F3F720927731449B6A9409F1248743
    • 1

相关问题