patternsqlMinor
SQL server Read Only permission automatically for new databases
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
Once you run this sqllogin1 user will be created in all new database and will be a member of
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]
GOOnce 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]
GOContext
StackExchange Database Administrators Q#205227, answer score: 7
Revisions (0)
No revisions yet.