HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL server Read Only permission automatically for new databases

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
newdatabasesreadsqlpermissionautomaticallyforserveronly

Problem

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.

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#205227, answer score: 7

Revisions (0)

No revisions yet.