Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

User auf jede neue Datenbank mit db_owner Role

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von sanguiniker, 6 April 2016.

  1. sanguiniker

    sanguiniker Neuer Benutzer

    Hallo,

    ich suche nach einer Lösung in der ein bestimmter User (SQL oder AD User) so berechtigt wird, dass er automatisch beim Anlegen einer neuen DB in dieser gleich db_owner Rechte hat.
    Dieser User legt diese Datenbanken jedoch nicht selber an.

    Ich habe es versucht durch Erstellen einer Serverrole jedoch kam ich hier nicht zum gewünschten Ergebnis.
    Vielen Dank im voraus für eure Antworten.
     
  2. Tommi

    Tommi Datenbank-Guru

    Hi,

    hier wirst du mit einem DDL-Trigger arbeiten müssen.
    Informationen dazu findest du auf dieser Seite:

    DDL-Trigger

    Hier ist auch ein Beispiel für genau deinen Fall angegeben (Event CREATE_DATABASE)

    Viele Grüße,
    Tommi
     
  3. sanguiniker

    sanguiniker Neuer Benutzer

    Hallo Tommi,


    vielen Dank für deine Antwort.

    Jedoch habe ich bisher noch nie etwas mit Triggers gemacht und bin ein bisschen ratlos wie ich hier jetzt:

    ALTERROLE [db_owner] ADD MEMBER [Meber] einbauen kann.

    Im Script verstehe ich auch noch nicht was ich mit der Passage:

    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    anfangen soll.

    Gruß
    Micha
     
  4. Tommi

    Tommi Datenbank-Guru

    Hallo Micha,

    ist eigentlich gar nicht so kompliziert und knifflig.

    Ein Trigger reagiert auf bestimmte Aktionen auf einem Objekt. Dieses Objekt kann eine Tabelle, eine Datenbank oder sogar eine SQL-Server-Instanz sein.
    Wenn die im Trigger zu überwachende Aktion (das Event) durch einen Befehl ausgelöst wird (in deinem Fall das Erstellen einer neuen Datenbank, das ein Event "CREATE_DATABASE" auslöst)
    können innerhalb der Trigger-Programmierung zusätzliche Befehle abgesetzt werden.

    Bei Abfangen eines Events auf SQL-Server-Instanz-Ebene spricht man von einem DDL-Trigger (bei Tabellen z.B. wird dies DML-Trigger genannt).

    Ich nehme mal das Beispiel der MSDN-Seite auseinander:

    Schritt 1:
    Code:
    IF EXISTS (SELECT * FROM sys.server_triggers
        WHERE name = 'ddl_trig_database')
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
    Hier wird lediglich geprüft, ob der Trigger bereits angelegt wurde. Ist das der Fall, so wird er aus der SQL-Serverinstanz gelöscht.

    Schritt 2:
    hier wird der Trigger angelegt. mit der Klausel
    Code:
    ON ALL SERVER 
    wird angegeben, dass es sich um einen Trigger für die gesamte Instanz handelt.

    Das Schlüsselwort "FOR" gibt an, dass die im Trigger-Code angegebenen Anweisungen ausgeführt werden, bevor die Datenbank angelegt wird!
    Für deine Anweisungen müsste hier also das Schlüsselwort "AFTER" stehen.

    Das im Beispiel angegebene SELECT ist die Anweisung, die mittels dem Trigger ausgeführt werden soll.
    Im Beispiel wird die Funktion EVENTDATA() verwendet, die ein XML-Fragment zurückgibt.
    Über die Anweisung .value wird Text des angegebenen XML-Knotens ausgegeben.

    Diese Anweisung musst du mit deinen Befehlen überschreiben. Das SELECT benötigst du nicht.

    Schritt 3:

    Mit der Anweisung
    Code:
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
    wird der grade erst angelegte Trigger auch direkt wieder gelöscht. Diesen Code-Abschnitt brauchst du gar nicht.

    Viele Grüße,
    Tommi
     
  5. sanguiniker

    sanguiniker Neuer Benutzer

    Hallo Tommy,

    vielen Dank für deine Mühe jedoch bin ich nicht erfahren in T-SQL.
    Ich habe das einmal so versucht zu lösen aber ich bekomme es einfach nicht hin.

    CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    AFTER CREATE_DATABASE
    AS
    :setvar SQLCMDUSER [Test5]
    :setvar INT_DATABASE Test12 -- Wie über nehme ich den neu create Database Name?
    USE $(INT_DATABASE)
    GO
    CREATE USER $(SQLCMDUSER) FOR LOGIN $(SQLCMDUSER)
    GO
    USE $(INT_DATABASE)
    GO
    ALTER ROLE [db_owner] ADD MEMBER $(SQLCMDUSER)
    GO

    Vielleicht kannst du mir ja noch den entscheidenden Tipp geben....

    Gruß Micha
     
  6. Tommi

    Tommi Datenbank-Guru

    Hallo Micha,

    die Anlage des Triggers müsste so aussehen:

    Code:
    CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS
        DECLARE @SQL as varchar(max), @DB varchar(255)
    
        SET @DB=EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
    
        SET @SQL='
        USE ['+@DB+'] ;
    
        CREATE USER [Domain\User] FOR LOGIN [Domain\User] WITH DEFAULT_SCHEMA=[dbo] '
    
        EXEC (@SQL)
    GO
    Damit der Trigger-Code mit diesem funktioniert muss jedoch der in der neuen Datenbang anzulegende User bereits unter Sicherheit > Anmeldungen in der Instanz angelegt sein (Klausel
    Code:
    FOR LOGIN [Domain\User]
    Ich habe hier auch einen Domain-User vorausgesetzt. Das kann an natürlich auch mit einem SQL-Server-User machen.
    Informationen zum Skript findest du auf diesen Seiten:

    EVENTDATA (Transact-SQL)
    Verwenden der EVENTDATA-Funktion
    CREATE USER (Transact-SQL)

    Viele Grüße,
    Tommi
     
  7. sanguiniker

    sanguiniker Neuer Benutzer

    Hallo Tommi,

    vielen Dank, damit kann ich erstmal arbeiten ;-)
    Jetzt muss ich nur noch rausfinden, wie ich mehrere User aus einer Tabele damit berechtigen kann.
    Gruß
    Micha
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden