locked
Unable to add db_datareader to databases with numbers in their names. RRS feed

  • Question

  • I have three databases servers with about 490 databases on them, we have an internal tool that requires db_datareader to each database to work properly.  Using this command:

    sp_msforeachdb @command1= "?..sp_addrolemember 'db_datareader', 'DOMAIN\User'"

    I'm able to add db_datareader to the particular user, then I go back and delete the user from the system databases, simple stuff.  However, I have several databases that either start with a number, or are only numbers for the database name, and I'm unable to add db_datareader to that user via script or via the UI even.

    Am I missing something simple?

    Tuesday, November 26, 2013 6:37 PM

Answers

All replies

  • several databases that either start with a number, or are only numbers for the database name

    Hello Ron,

    Valid object identifier must start with a letter, underscore or at sign; numbers are not allowed, see http://technet.microsoft.com/en-us/library/aa223962(v=sql.80).aspx

    If you use a number as first sign you must quote the name in brackets [ ]


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:16 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:55 AM
    Tuesday, November 26, 2013 6:48 PM
  • Hi Ron,

    The below example shows how a Login names testLogin is provided with db_datareader access on a database named 1234. 

    USE [1234]
    GO
    CREATE USER [TestLogin] FOR LOGIN [TestLogin]
    GO
    USE [1234]
    GO
    EXEC sp_addrolemember N'db_datareader', N'TestLogin'
    GO


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 26, 2013 6:57 PM
  • The proc sp_msforeachdb has many problems including this one.  As an undocumented command you should not be using it.

    I would suggest creating your own version of sp_msforeachdb and add [] around the database names.

    Please see:

    http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

     

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:16 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:55 AM
    Tuesday, November 26, 2013 7:58 PM