How to automatically grant permissions to application created databases in SQL Server 2008 R2?

Question

So I have an application that automatically creates a new database after the database it is currently in reaches a fixed number of rows. I need to be able to automatically assign a user within the installation the db_datareader role for the automatically created database. I'm kind of at a loss here, any help is greatly appreciated.

• Vladimir Oselsky: If you creating databases grammatically why not add GRANT ACCESS to those scripts?

Answer 1

Add the user to the DB_READER role. Like this:

use YourNewDb
go
exec sp_addrolemember db_datareader, UserName (or) [domain\netusername] (or) suser_sname()
go

Copied from https://stackoverflow.com/questions/456346/t-sql-to-grant-db-datareader-and-db-datawriter

• user3220117: This is what I would like to do, but I really have no way of setting up an automated way to know when the new DB is created, or it's name without looking. I already have a script set up that does this, but I'm looking for a way to do it automatically if at all possible. Thanks for the code though!

Answer 2

You can do this by granting the permission in the Model database. This database is used to create any new ones.

USE model
GO
CREATE USER UserName FROM LOGIN LoginName
GO
EXEC sp_addrolemember 'db_datareader','UserName'
GO

That way, as the application creates new databases (which are copied from Model), they will already have the User and the role membership.

However be warned. This will be the case for ANY new database created with the CREATE DATABASE command on this instance. So if you create a database for a different application it will already have this user and the user will have db_datareader. Databases created with ATTACH DATABASE or RESTORE DATABASE will not use the model database, and will not copy the security from the Model database.