sql server windows ad role permisions

Question

I would like to grant a specific windows role automatic permissions to be the "dbo" every time a new database is created, can this be accomplished using the model database, thanks

Answer 1

Yet an alternative is to use a server-level DDL trigger that adds the role to the database when it is created.

But Tibor makes a good point about databases that are restored on the server. DDL triggers don't fire on RESTORE as recall.

Answer 2

And just to clarify: Yes, what Erland say is what I suggested. We are in agreement.

Answer 3

thank you.

are there any recommended ways other than using the model database wherein an AD group can be the member of the db_owner whenever a database is created without explicitly adding the AD role to the db_owner role.

Answer 4

Sure, you can have a scheduled job executing, say, every hour. This can do anything you want, for instance go through all databases and verify that the login for this AD group exists as a user in each database an is a member of db_owner role. But you will of course have a lag between the database is created and when the job is executed. However, this is in one aspect more robust than the model database. A database can be "created" by restore or attach. The model database won't help you in those cases.