none
db_owner für alle UserDB's RRS feed

  • Frage

  • Hallo,

    wie kann ich folgendes Skript so umbauen, dass es automatisch auf alle UserDB's ausgeführt wird?

    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]
    GO
     

    Danke & Grüße

    Julian

    Dienstag, 25. April 2017 11:26

Antworten

  • 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

    • Als Antwort markiert Julian79 Mittwoch, 26. April 2017 06:17
    Mittwoch, 26. April 2017 06:15