3

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.

6

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.

  • 1
    To be more exact: all new databases are created as copies of the model database ;) – TomTom Apr 28 '18 at 10:45

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.