Read only access to databases

Question

Hi,

Is there an option on the server level for any sql databases read-only permissions should be provided automatically (for all existing databases and upcoming new databases). I know I can give a db_reader role for a specific databases but is there a role on the overall server/instance level.

Thanks.

Answer 1

Hallo Nibras,

the db_datareader role is a database role but not a server role.
You have to go through each database and add the user / group to the dedicated database role

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Hi,

I already mentioned that I can provide access to the existing databases using the db_reader role but I wanted to have it on the server level so that for any new upcoming databases, it has to provide the read permission as well.

Answer 3

Add a database trigger to the model database which adds new database users to the db_datareader role!
Keep in mind that model will be used for every new database - so the trigger will always be part of the new database.

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('dbo.trg_UserCheck','TR'))
	DROP TRIGGER dbo.trg_UserCheck
	GO

CREATE TRIGGER dbo.trg_UserCheck
ON DATABASE
FOR CREATE_USER
AS
	SET NOCOUNT ON
	
	DECLARE	@principal_name sysname,
			@sql nvarchar(max)

	SET		@principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
	SET		@sql = 'EXEC sp_addrolemember db_datareader, ' + @principal_name + ';'
	EXEC	sp_executeSQL @sql

	SET NOCOUNT OFF
GO

(not tested!!!)

To get more information concerning DDL-Triggers on database level followi these links:

http://msdn.microsoft.com/en-us/library/ms189799.aspx
http://msdn.microsoft.com/en-us/library/bb522542.aspx
http://msdn.microsoft.com/en-us/library/ms190785.aspx

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 4

To whom do you want to grant read-only access?  Everyone with a login or only specific logins/groups?

Only the database owner and sysadmin role members can access a newly created database by default.  If you enable the guest user, then any login can access the database with default public permissions.  Adding the guest user with db_datareader role membership will provide all logins with read-only access.

The script below will enable the guest user in all existing user databases as well as the model system database.  The model database is used as a template for newly created databases so new databases will have the guest user enabled with db_datareader membership going forward.

EXEC sp_MSforeachdb '
IF ''?'' NOT IN(''master'',''tempdb'',''msdb'')
BEGIN
    USE [?];
	PRINT ''Processing '' + DB_NAME();
	CREATE USER guest;
	EXEC sp_addrolemember ''db_datareader'', ''guest'';
END';

Personally, I would explicitly add the only desired users and permissions rather than enabling guest or relying on model.  Users that create new databases might not be aware that everyone with a login has read-only access to the database.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Hi Dan,

Need to provide to a specific windows login the db_reader role, how I can go about it

Answer 6

Need to provide to a specific windows login the db_reader role, how I can go about it

You can use the same script I posted earlier.  Just substitute the Windows account for the guest user:

EXEC sp_MSforeachdb '
IF ''?'' NOT IN(''master'',''tempdb'',''msdb'')
BEGIN
    USE [?];
	PRINT ''Processing '' + DB_NAME();
	CREATE USER [MyDomain\MyAccount];
	EXEC sp_addrolemember ''db_datareader'', ''MyDomain\MyAccount'';
END';

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

Hi Dan,

Thanks, this is working. Awesome.

Answer 8

Hi Dan,

I already tested in my local system and it worked absolutely fine because I created a database within sql and it takes model database template. However we have a tool which does a backup and restore mechanism and does a migration to sql, in that case it is not considering the destination model db, hence the permissions are not inherited. How can I resolve this, any thoughts please.

Thanks.

Answer 9

Hallo Nibras,

in this case you have to implement it by code. A login will not be added as user to a restored database.

Example:

-- Restore the database
RESTORE DATABASE [MyDatabase] FROM DISK = '\\PATH\Backupfile.bak' WITH STATS, REPLACE
GO

-- Change the owner of the db to sa
ALTER AUTHORIZATION ON DATABASE::[MyDataBase] TO sa
GO

-- Add the login to the db_datareader role
USE [MyDatabase]
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'yourLoginAccount')
CREATE USER yourLoginAccount FROM LOGIN yourLoginAccount
GO

EXEC sp_addRoleMember 'db_datareader', 'yourLoginAccount'

HTH :)

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de