DDL Trigger to create database user

Question

DDL Trigger to create database user

DbRob

SSC Veteran

Points: 286
More actions
February 10, 2010 at 1:49 pm

#215553

Hi,
I am trying to determine if it is possible to create a FOR CREATE_DATABASE DDL trigger for statements that will create a user on the database that was just created. I believe I can get the database name from EVENTDATA(), but can I do this without executing a USE database statement? For example, BOL says:
To create a database user using Transact-SQL
1.In Query Editor, connect to the database in which to create the new database user by executing the following Transact-SQL command: USE <database name> GO
2.Create the user by executing the following Transact-SQL command: CREATE USER <new user name> FOR LOGIN <login name> ; GO
...but this will fail because you can't use a USE statement in a trigger or stored proc. Anyone have thoughts on this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Although at first blush I'm not a fan of what you are doing and would want to hear a whole bunch about why.. You could probably build a dynamic SQL statement and execute it with:

EXEC ( @variable_name )

Within the sproc or trigger. I haven't tried that for a long time so mileage may vary.

CEWII

DavidZahner Ten Centuries Points: 1169 More actions · Answer 2

Is the user name going to be determined by the database name or is there anything else that is preventing you from simply creating the user in model so that any new database has the users?

DbRob SSC Veteran Points: 286 More actions · Answer 3

DavidZahner (2/11/2010)
Is the user name going to be determined by the database name or is there anything else that is preventing you from simply creating the user in model so that any new database has the users?

David- it works! So simple I comlpletely missed this.

Elliot- here is the background. I agree that this would be bad practice under normal circumstances, but the scenario is as follows:

This will be a dedicated SQL Server for a law firm application that handles cases and case data. Each case created within the application creates a seperate database. The user will be the same for each database, based on a domain login. Because of the nature of the business, this won't necessarily be and orderly, structured process. Could happen 3-5 times a week, possibly in the middle of the night as we are international. Everyone on the team is authorized to have access via the application, so one AD group will suffice. Restricated access, on an infrequent basis, can be handled via normal request channels. I proposed this solution as we are a very small team for this kind of support, and after discussion, received management approval to pursue this option. Never encountered a scenario like this before.

Thanks!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I thought of another potential solution.

You could add that user to the model database and it would be there automatically at creation without using a DDL trigger.

CEWII