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

I am not sure what you want to achieve. Exactly what permissions do you want to grant the database owner?

dbo already has all permissions it needs inside the database. If you want to grant permissions on server level, you cannot arrange this with the model database. Model is a template for new databases, and server-level permissions are stored in the master database.

Maybe a server-level DDL trigger can be a solution for you.

Answer 3

You mean something like adding this login as a user to the model database and making that user a member of the db_owner role?

Answer 4

yes, Will that work.

its not a user, A windows AD group needs dbo access automatically every time a new database is created.

Answer 5

If you add the Windows AD group as a user to model and make that AD group member of db_owner in model, that AD group will be member of db_owner in each new database. It will not be dbo, but then again I don't think a group can own a database.

Keep in mind that if you do this, members of this AD group can also do whatever they want in model.