locked
Give SQL user read access to all databases RRS feed

  • Question

  • Hi, this is two questions really.

    1. I have created a SQL user and i need to give it datareader access to all databases that are currently on the server is there a easy way to do this ?

    2. I need to give my SQL user read access to all future databases that get created on the server.  I have added to "model" DB is this all i need to do for future ones, I guess i will know tomorrow when a new DB is created but thought i would ask in case i am wrong.

    The SQL server has TMG/ISA logs, so a new DB is created each day.

    • Changed type KJian_ Monday, June 13, 2011 7:21 AM
    Friday, June 10, 2011 9:26 AM

Answers

  • DECLARE @name_holder VARCHAR(100)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','%snap%') 
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    EXEC sp_addrolemember 'db_datareader', 'user_name'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor
    


    2) Yes.

     

    ** This is under the assumption that you already have the user in all dbs. Also If you have snapshots you have to exclude them.

    • Marked as answer by KJian_ Monday, June 20, 2011 7:38 AM
    Friday, June 10, 2011 1:30 PM
  • Hi duncan320,

    In addition to using a CURSOR, we can also use dynamic SQL or a undocumented stored procedure to grant a user access to all databases, please see:

    http://stackoverflow.com/questions/3087140/sql-server-how-to-grant-read-access-to-all-databases-to-a-login 

    http://www.sqlservercentral.com/Forums/Topic1022679-391-1.aspx

    For new databases, you can add the user in the model database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by KJian_ Monday, June 20, 2011 7:38 AM
    Monday, June 13, 2011 7:20 AM

All replies

  • DECLARE @name_holder VARCHAR(100)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','%snap%') 
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    EXEC sp_addrolemember 'db_datareader', 'user_name'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor
    


    2) Yes.

     

    ** This is under the assumption that you already have the user in all dbs. Also If you have snapshots you have to exclude them.

    • Marked as answer by KJian_ Monday, June 20, 2011 7:38 AM
    Friday, June 10, 2011 1:30 PM
  • Hi duncan320,

    In addition to using a CURSOR, we can also use dynamic SQL or a undocumented stored procedure to grant a user access to all databases, please see:

    http://stackoverflow.com/questions/3087140/sql-server-how-to-grant-read-access-to-all-databases-to-a-login 

    http://www.sqlservercentral.com/Forums/Topic1022679-391-1.aspx

    For new databases, you can add the user in the model database.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by KJian_ Monday, June 20, 2011 7:38 AM
    Monday, June 13, 2011 7:20 AM