Easily grant access to list of users after database is created

Question

I have a couple of databases that are occasionally deleted and recreated. This happens when the schema changes.

I have a group of developers that need access to those databases. In order to grant these developers access to those databases, I can either:

• Use a shared login, and grant that login access to each database using SSMS. This is bad security, but fairly quick. I don't want to do this except that it saves me a lot of time.

• Use individual logins, and grant each individual login access to each database using SSMS. This is good security, but costs me time every time I have to recreate the databases. I have to select each user's login, and edit the user's User Mapping.

How can I easily grant a group of users access to a database that I've dropped and recreated?

Versions

• Microsoft SQL Server Management Studio 12.0.2569.0

• SqlServer 12.0.4100.1

Scope

• There are about six developers.
• There are two SqlServer servers.
• Each server has two databases to which developers are granted access.
• Databases are deleted/recreated once or twice a month.

Answer 1

Create an Active Directory group and add all of the necessary users to it. Grant the group access to the database, which can be done easily with CREATE USER. If you want to add the group to a role, such as db_owner/db_datareader/db_datawriter, then use ALTER ROLE.

You could also do the steps that you've been doing in the UI, but then select the option to script it out so that you don't have to lookup the syntax for CREATE USER or ALTER ROLE. I often use the UI to help me with scripts I'm building.

Once the script works as desired, save it and use it for a recreated database. You should just need to switch which database to run the script in.

Alternatively, add the group to the model database and whichever roles the group should be in so that when you create a new database the group automatically has access. See this page for more information on model. But only use model if the group needs access to all newly created databases.

Answer 2

I would choose an option similar to Tara's, but still very generic since you haven't said how this restore is happening - if you add that detail we may be able to provide you the most painless way to automated this. This is going off the fact that you said "a few" and "occasionally". I'll also assume when you re-create them they have the same name.

1. Create the desired database role with permissions and users in a current iteration for each distinct database.
2. Script out the roles along with its users and permissions, saving each one in a file that is accessible by SQL Server.
3. Create a SQL Agent job for each database that calls its script, with an "Operating System (CmdExec)" type step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -i "permission_setup_DB_A.sql" -b

4. Either run that SQL Agent job manually after the restore of Database A, or chain it up to the automated restore process by having it call the agent job directly.

There are more elegant ways, but they're hard to get into without knowing more about your process.

In my current situation, we have a really complicated process when doing this type of thing, so I have a separate database which has all of the complicated permissions and data massaging included as stored procedures which I then call from SQL Agent job steps as part of the restore process. This is probably overkill for your use case, but again, hard to say for sure.