2

I'm currently dealing with a database where multiple databases will be added in the future. Right now, I'm working on creating some users with specific permissions and I'm struggling with how to ensure that they can also cover the databases that aren't yet added. Complicating things, most of these permissions are needed only databases ending in a specific suffix.

For example, a user needs to be created that can add assemblies to all the current and future databases that end in ABC.

Is there a way to make this easier to manage, or will it involve always ensuring after a new set of DBs are added that the user has access to the ones ending in ABC?

3

One possible option would be to:

  1. Create a Database Role in each DB (i.e. this should be in your template / source DB) that contains the desired set of permissions.

  2. Create an Instance / Server -level DDL Trigger that fires on the CREATE_DATABASE event and does the following:

    1. Checks the name of the Database
    2. If new DB name ends with ABC then:
      1. Adds the Login to the new Database as a User
      2. Add the new User to the Database Role that has the desired permissions

This will likely require some Dynamic SQL, but it should work. And honestly, I'm not sure what I think of using a DDL Trigger in this manner, but it certainly does automate it ;-).

2

I would probably go with a variation on Solomon's approach just to avoid the ickyness of a dynamic DDL trigger (actually it isn't that similar now that I think about it..):

  1. Create a "DBA" database that includes a stored procedure to create databases. The sproc would:
    • Validates the DB name ends in 'ABC'
    • Do any other weird validating or default setting configs you may want these databases to have
    • Automatically create users in the new database (hardcoded or from a table)
    • Apply desired permissions to the users.
  2. Give the users access to run the sproc, but not create their own databases manually.

This lends itself to be a little more hands-on, which may be good or bad depending on your situation and what type of environment this is applying to. When it comes to wacky stuff like this, though, I find creating self-service stored procs is cleaner and easier to maintain than baking in server-level stuff and keeps all of it in one place.

You could also add these roles to MSDB and just not use them if the database doesn't end in ABC -but again I sort of like having it all contained in the DBA database for maintenance.

  • True, depending on the context and specifics, a stored procedure could encapsulate all of the logic to handle this. +1 – Solomon Rutzky Nov 13 '17 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.