Sql-server – Easily grant access to list of users after database is created

permissionssql serversql server 2014

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.

Best Answer

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.

enter image description here

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.

Related Question