User auf jede neue Datenbank mit db_owner Role

sanguiniker

Neuer Benutzer
Beiträge
4
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.
 
Werbung:
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
 
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
 
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
 
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
 
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
 
Werbung:
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
 
Zurück
Oben