locked
Read only access to databases RRS feed

  • 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 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

    Friday, 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.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

    Friday, 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.de

    Wednesday, March 21, 2012 12:43 PM