获取[创建登录]脚本，现有的登录之前删除

Question

我正在做一个登录清理活动在我的SQL Server。不需要的登录将从服务器上删除。但是，在删除登录之前，我想保留当前登录的备份(通过运行"Create Login.."脚本为特定登录恢复)。

当I right click on Login > Script Login as > Create to ..

它给:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'ö©3ÚZ90T¥[³(1ëI¥6îõÛLáÚ', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

这里，缺少用户映射。这是一个手动过程。

如何获得所有登录和特定登录的实际脚本(带有用户映射)?(我试图在互联网上搜索，获得登录列表和相应的用户映射-而不是脚本)。

Answer 1

根据这篇MSDN文章，您可以使用如下查询获取用户映射:

    SELECT 
        susers.name AS ServerLogin,
        users.name  AS DBUser,
        DB_NAME()   AS [Database],              
        roles.name  AS DatabaseRoleMembership
    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]

如果您需要在多个数据库上运行此操作，我会使用游标依次遍历每个数据库(为每个数据库构建动态SQL语句)，并在返回完整集合之前将每个数据库的结果添加到临时表中。

您已经知道如何构造CREATE LOGIN命令来创建服务器级登录，因此您只需要能够创建脚本来创建数据库级用户并将登录映射到这些用户。

根据这个问题，你可以在一个特定的数据库上创建用户:

    USE <dbname>;
    CREATE USER <username> FROM LOGIN <loginname>;

，并使用

使用户成为特定组的成员:

   USE <dbname>
   EXEC sp_addrolemember '<dbrole>', '<username>';

使用上面的代码，您可以创建一个脚本来生成必要的语句，以便在以后重新创建登录及其映射用户。

不管怎样，我是这样做的:

    -- Generate statements to re-create server level logins
    SELECT  'IF(SUSER_ID(' + QUOTENAME(susers.name,'''') + ') IS NULL)' + 
    'BEGIN ' +
        'CREATE LOGIN ' + QUOTENAME(susers.name) + CASE WHEN susers.type_desc = 'SQL_LOGIN'
                                                        THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),logins.password_hash,1)+' HASHED'
                                                        ELSE ' FROM WINDOWS'
                                                   END + ';' + 
    'END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS LoginCreationStatements
    FROM      sys.server_principals AS susers
    LEFT JOIN sys.sql_logins        AS logins ON susers.principal_id = susers.principal_id
    WHERE susers.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
      AND susers.name NOT LIKE '##%##' -- Ignore Microsoft shipped logins
      AND susers.name <> 'SA'
    -- Generate statements to re-create database level users and their role memberships
    DECLARE @dbName    NVARCHAR(128)
    DECLARE @dbID      INT
    DECLARE @statement NVARCHAR(MAX)
    CREATE TABLE #userRoles
    (
        ServerLogin            NVARCHAR(128),
        DBUser                 NVARCHAR(128),
        [Database]             NVARCHAR(128),
        DatabaseId             INT,
        DatabaseRoleMembership NVARCHAR(128)
    )
    DECLARE db_cursor CURSOR FOR
        SELECT database_id, name
        FROM sys.databases
    OPEN db_cursor
    FETCH NEXT FROM db_cursor
    INTO @dbID, @dbName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @statement = N'INSERT INTO #userRoles ' +
                          'SELECT ' +
                              'susers.name AS ServerLogin,' +
                              'users.name  AS DBUser,' +
                              QUOTENAME(@dbName,'''') + ' AS [Database],' +
                              QUOTENAME(CONVERT(VARCHAR,@dbID),'''') + ' AS DatabaseId,' +
                              'roles.name   AS DatabaseRoleMembership ' +
                          'FROM ' + QUOTENAME(@dbName) + '.sys.database_principals users ' +
                          'INNER JOIN ' + QUOTENAME(@dbName) + '.sys.database_role_members link ON link.member_principal_id = users.principal_id ' +
                          'INNER JOIN ' + QUOTENAME(@dbName) + '. sys.database_principals roles ON roles.principal_id = link.role_principal_id ' +
                          'INNER JOIN sys.server_principals susers ON susers.[sid] = users.[sid] ' +
                          'WHERE susers.type_desc IN (''SQL_LOGIN'',''WINDOWS_GROUP'',''WINDOWS_LOGIN'') ' +
                            'AND susers.name NOT LIKE ''##%##'' ' +
                            'AND susers.name <> ''SA'''
        EXEC sp_executesql @statement
        FETCH NEXT FROM db_cursor
        INTO @dbID, @dbName
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
    -- Return the statements for re-creating database level users
    SELECT DISTINCT 
            'IF NOT EXISTS (SELECT 1 FROM [' + [Database] + '].sys.database_principals WHERE name = ' + QUOTENAME(DBUser,'''') + ') ' +
            'BEGIN ' +
            'CREATE USER ' + QUOTENAME(DBUser) + ' FOR LOGIN ' + QUOTENAME(ServerLogin) + ' ' +
            'END;' AS UserCreationStatements
    FROM #userRoles
    -- Return the statements for re-creating role mappigns for database level users
    SELECT  'USE ' + QUOTENAME([Database]) + '; ' +
            'EXEC sp_addrolemember ' + QUOTENAME(DatabaseRoleMembership) + ', ' + QUOTENAME(DBUser) + ';' AS UserRoleMappingStatements
    FROM #userRoles
    DROP TABLE #userRoles

这给出了3个结果集——第一个包含重新创建服务器级登录的语句，第二个包含重新创建数据库级用户的语句，最后一个是将登录重新映射到用户的语句。

可能有一些事情我错过了(像我们可能应该检查目标数据库仍然存在之前运行CREATE USER语句，也许你想忽略某些数据库，如主数据库和MSDB)，但我希望这是有帮助的!:)

Answer 2

这是我从多年来偷来的脚本片段中构建的。我想给每个人适当的荣誉，但我不知道所有的来源。它可以使用一些简化，但它似乎对我有效。

CREATE TABLE #ToDo
(
 RID TINYINT
,MyLogin varchar(128)
,MyScript varchar(max)
)
CREATE TABLE #MyTemp
(
 RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,DBname nvarchar(max)
,Username nvarchar(max)
,AliasName nvarchar(max)
)
INSERT INTO #MyTemp
(LoginName,DBname,Username,AliasName)
EXEC master..sp_msloginmappings 
CREATE TABLE #Logins
(
 RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,DBname nvarchar(max)
,Username nvarchar(max)
,AliasName nvarchar(max)
)
INSERT INTO #Logins
(LoginName,DBname,Username,AliasName)
SELECT LoginName,DBname,Username,AliasName
    FROM #MyTemp 
    WHERE dbname IS NOT NULL
    AND SUBSTRING(LoginName,1,1) != '#'
    AND SUBSTRING(LoginName,1,3) != 'NT '
CREATE TABLE #Alters
(
 RID INT IDENTITY(1,1) Primary Key
,LoginName nvarchar(max)
,ALTERED nvarchar(max)
)
DECLARE @CurRow INT = 1
DECLARE @MaxRow INT
DECLARE @LoginName varchar(128)
DECLARE @DBName varchar(256)
DECLARE @MySQL varchar(max)
SELECT @MaxRow = MAX(RID)
    FROM #Logins
WHILE @CurRow <= @MaxRow
    BEGIN
        SELECT @LoginName = LoginName
            ,@DBName = DBName
            FROM #Logins
            WHERE RID = @CurRow
        ;WITH MyCTE
        AS
        (
        SELECT DP1.name AS DatabaseRoleName
            , isnull (DP2.name, 'No members') AS DatabaseUserName   
            FROM sys.database_role_members AS DRM  
                RIGHT OUTER JOIN sys.database_principals AS DP1  
                    ON DRM.role_principal_id = DP1.principal_id  
                LEFT OUTER JOIN sys.database_principals AS DP2  
                    ON DRM.member_principal_id = DP2.principal_id  
            WHERE DP1.type = 'R'
            AND DP2.name Like @LoginName
        )
        INSERT INTO #Alters
        (LoginName,ALTERED)
        SELECT @LoginName, 'USE '+@DBName+';'+char(10)+char(13)+'GO;'+char(10)+char(13)+ 'EXEC sp_addrolemember '''+DatabaseRoleName+''', '''+@LoginName+''';'
            FROM MyCTE
        SET @CurRow = @CurRow + 1
    END
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 0, MyLogin = REPLACE(REPLACE(QUOTENAME(SP.name), '[',''),']','')
    ,'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)
        +CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)
            + ' HASHED, CHECK_EXPIRATION = ' 
        +CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' 
        +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE ' FROM WINDOWS WITH' END 
        +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS MyScript
    FROM sys.server_principals AS SP
        LEFT JOIN sys.sql_logins AS SL
            ON SP.principal_id = SL.principal_id
    WHERE SP.type IN ('S','G','U')
    AND SP.name NOT LIKE '##%##'
    AND SP.name NOT LIKE 'NT AUTHORITY%'
    AND SP.name NOT LIKE 'NT SERVICE%'
    AND SP.name <> ('sa');

INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 2
    , MyLogin = SL.name 
    ,'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''' AS MyScript
    FROM master.sys.server_role_members SRM
        JOIN master.sys.server_principals SR
            ON SR.principal_id = SRM.role_principal_id
        JOIN master.sys.server_principals SL
            ON SL.principal_id = SRM.member_principal_id
    WHERE SL.type IN ('S','G','U')
    AND SL.name NOT LIKE '##%##'
    AND SL.name NOT LIKE 'NT AUTHORITY%'
    AND SL.name NOT LIKE 'NT SERVICE%'
    AND SL.name <> ('sa');

INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 3
    ,MyLogin = SP.name 
    ,CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END
        + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' 
    + CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' ELSE ' WITH GRANT OPTION' END collate database_default AS MyScript
    FROM sys.server_permissions AS SrvPerm 
        JOIN sys.server_principals AS SP
            ON SrvPerm.grantee_principal_id = SP.principal_id 
    WHERE   SP.type IN ( 'S', 'U', 'G' ) 
            AND SP.name NOT LIKE '##%##'
            AND SP.name NOT LIKE 'NT AUTHORITY%'
            AND SP.name NOT LIKE 'NT SERVICE%'
            AND SP.name <> ('sa');
INSERT INTO #ToDo
(RID, MyLogin, MyScript)
SELECT RID = 4, MyLogin = LoginName, MyScript = ALTERED
    FROM #Alters
    ORDER BY LoginName, ALTERED
--SELECT * FROM #ToDo
SELECT RID, MyLogin, MyScript
    FROM #ToDo
    ORDER BY MyLogin, RID, MyScript
-- cleanup
DROP TABLE #MyTemp
DROP TABLE #Logins
DROP TABLE #Alters
DROP TABLE #ToDo