sql server drop login failed

 

https://stackoverflow.com/questions/37275/sql-query-for-logins

https://www.mssqltips.com/sqlservertip/4299/sql-server-errors-with-drop-login-and-drop-user/

获取所有的用户

SELECT sid
FROM   master..syslogins

 

筛选出db owner不是sa的数据库

SELECT ROW_NUMBER() OVER ( ORDER BY name ) AS RowNumber ,
       name AS DatabaseName ,
       SUSER_SNAME(owner_sid) AS DBOwner ,
       is_read_only ,
       state_desc
FROM   sys.databases
WHERE  SUSER_SNAME(owner_sid) <> 'sa';

 

 

 

通过下面的sql，批量将数据库owner不是sa的设置为sa。 排除了offline和readonly的，并且排除掉master，model，tempdb，distribution四个系统数据库

DECLARE @MyTable TABLE
    (
        RowNumber INT NOT NULL ,
        DatabaseName NVARCHAR(500) NOT NULL ,
        DBOwner NVARCHAR(500) NOT NULL
    );
INSERT INTO @MyTable ( RowNumber ,
                       DatabaseName ,
                       DBOwner )
            SELECT ROW_NUMBER() OVER ( ORDER BY name ) AS RowNumber ,
                   name AS DatabaseName ,
                   SUSER_SNAME(owner_sid) AS DBOwner
            FROM   sys.databases
            WHERE  state_desc <> 'OFFLINE'
                   AND is_read_only = 0
                   AND name NOT IN ( 'master', 'tempdb', 'model' ,
                                     'distribution' );

DECLARE @CurrentRowNumber INT = 1;
DECLARE @MaxRowNumber INT;
SELECT @MaxRowNumber = MAX(RowNumber)
FROM   @MyTable;

DECLARE @SQLString NVARCHAR(500);
DECLARE @TempDatabaseName NVARCHAR(500);
WHILE ( @CurrentRowNumber <= @MaxRowNumber )
    BEGIN
        SELECT @TempDatabaseName = DatabaseName
        FROM   @MyTable
        WHERE  RowNumber = @CurrentRowNumber;
        SET @SQLString = 'Use ' + @TempDatabaseName
                         + '; EXECUTE sp_changedbowner ''sa''';
        PRINT @SQLString;
        EXECUTE sp_executesql @SQLString;
        SET @CurrentRowNumber = @CurrentRowNumber + 1;
    END;