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;