Server Role for User Database Access

Question

Server Role for User Database Access

dpaskiet

Valued Member

Points: 62
More actions
March 20, 2025 at 1:34 pm

#4563818

I am looking to simplify a development server access issue. Databases are constantly being added to this server/cluster which requires me to add the developer AD group to the new database as a DB_OWNER to allow them to do the needful. This is a PITA! I have in the past used a job created to loop through all user db's and add the group to the db_owner role accordingly. Its messy and not as secure as I would like. I would like to create a server role that will give db_owner rights or full control of those user databases while also not allowing access to system databases or any server admin function.
Any ideas on how to best accomplish this? I have researched and read many articles on others trying to do similar things, but none seem to actually have a working solution.
This is for SQL server 2019.

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

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

ScottPletcher SSC Guru Points: 101180 More actions · Answer 1

Add that user to the "model" db -- with whatever permissions you want -- and they will automatically be added to every new db.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004556 More actions · Answer 2

ScottPletcher wrote:

Add that user to the "model" db -- with whatever permissions you want -- and they will automatically be added to every new db.

Ditto that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrew Grasshopper Points: 20 More actions · Answer 3

A clean solution would be to create a custom server role that automatically grants the necessary permissions to newly created user databases while keeping system databases secure. Instead of running a job or manually adding the group, you can leverage the model database approach, as suggested. Any user or role added there with specific permissions will automatically be copied to all newly created databases.

Using ALTER SERVER ROLE combined with a script to loop through user databases and exclude system DBs might also work. It depends on how much automation vs. manual control you need.

Server Role for User Database Access

Cookies on SQLServerCentral