SQL server Read Only permission automatically for new databases

Question

When I setup my database I create a sql user that my application runs as. The sql user gets read only access to all the databases on the server. The problem is the user won’t have read only access to any new created databases in the future. There is another app that creates these new databases for users and I don’t have a way to add my user script into the templates database created. Is there a way to automatically update the sql user for read only for the future ones? SQL server 2014.

Answer 1

The above answer only works for newly created databases, but not restored databases.

Instead, give them these permissions:

GRANT CONNECT ANY DATABASE TO myloginname;
GRANT SELECT ALL USER SECURABLES TO myloginname;

That will give them permissions to all databases that are added - even if their login isn't in the database. That second role was added in SQL Server 2014.

Answer 2

Reference: model Database

The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.

Add the user to your model database db_datareader role. All future database will mimic this privilege.

USE [model]
GO
CREATE USER [sqllogin1] FOR LOGIN [sqllogin1]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [sqllogin1]
GO

Once you run this sqllogin1 user will be created in all new database and will be a member of db_datareader role.