Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Read only access to all databases on a Server

    Hi,
    How can I provide a user read only access to all the databases on the server. I have 15 databases on the server. I know I can give db_datareader access in each database individually but that is time consuming and I have 10 servers for this to be done.

    thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    this may work (for each server):

    sp_msforeachdb @command1= "?..sp_addrolemember 'db_datareader', 'username'"
    Last edited by PMASchmed; 01-18-08 at 15:52.

  3. #3
    Join Date
    Oct 2003
    Posts
    93
    ya that worked after a small tweak. thanks a ton.

    sp_msforeachdb @command1= "?..sp_addrolemember N 'db_datareader', N 'username'"

    I saw the modified syntax on some other site.

    Do you know what is N used ?

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    The N prefix
    denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages, for example Chinese. Unicode is designed so that extended character sets can still "fit" into database columns.
    http://databases.aspfaq.com/general/...-n-prefix.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •