Answered by:
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.
Friday, March 16, 2012 6:44 AM
Answers
-
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/
- Proposed as answer by Prakash Nandwana Friday, March 16, 2012 12:50 PM
- Marked as answer by Iric Wen Monday, March 19, 2012 8:43 AM
Friday, March 16, 2012 12:07 PM
All replies
-
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 roleUwe Ricken
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
http://www-db-berater.deFriday, March 16, 2012 9:24 AM -
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.
Friday, March 16, 2012 10:48 AM -
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.aspxUwe Ricken
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
http://www-db-berater.deFriday, March 16, 2012 11:31 AM -
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/
Friday, March 16, 2012 11:43 AM -
Hi Dan,
Need to provide to a specific windows login the db_reader role, how I can go about it
Friday, March 16, 2012 11:57 AM -
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/
- Proposed as answer by Prakash Nandwana Friday, March 16, 2012 12:50 PM
- Marked as answer by Iric Wen Monday, March 19, 2012 8:43 AM
Friday, March 16, 2012 12:07 PM -
Hi Dan,
Thanks, this is working. Awesome.
Friday, March 16, 2012 12:21 PM -
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.
- Proposed as answer by Uwe RickenMVP Wednesday, March 21, 2012 12:39 PM
- Unproposed as answer by Uwe RickenMVP Wednesday, March 21, 2012 12:39 PM
Wednesday, March 21, 2012 12:26 PM -
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.deWednesday, March 21, 2012 12:43 PM