Problem gelöst:
-- 1. Set Owner to Julian1
-- Welche DB's haben SA NICHT als Owner:
---- SELECT name AS DBName, suser_sname(owner_sid) AS DBOwner
---- FROM sys.databases
---- WHERE suser_sname(owner_sid) <> 'julian1'
-- SA zum Owner aller UserDB's machen:
declare commands cursor for
SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [SA];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'SA'
declare @cmd varchar(max)
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
exec(@cmd)
fetch next from commands into @cmd
end
close commands
deallocate commands
-- 2. Set DB_Owner Permission to 'Domain\SecurityGroup'
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ '
IF NOT EXISTS (SELECT name
FROM [sys].[database_principals]
WHERE name = N''Domain\SecurityGroup'')
Begin
CREATE USER [Domain\SecurityGroup]
FOR LOGIN [Domain\SecurityGroup] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [Domain\SecurityGroup]'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
Quelle der Original-Skripte:
https://dataginger.com/2015/05/05/sql-server-change-ownership-for-all-user-databases-to-sa-account/
https://www.mssqltips.com/sqlservertip/3541/grant-user-access-to-all-sql-server-databases/
Grüße
Julian
