Professional Web Applications Themes

Adding the same user to all databases - Microsoft SQL / MS SQL Server

You could try using the following undoented and unsupported procedure at your own risk: EXEC sp_msForEachDatabase 'EXEC sp_revokedbaccess ''MYUSER''; exec sp_adduser ''MYUSER'', ''MYUSER''; exec sp_addrolemember N''db_owner'', N''MYUSER''' "Interflex" <com> wrote in message news:google.com... ...

Sponsored Links
  1. #1

    Default Re: Adding the same user to all databases

    You could try using the following undoented and unsupported procedure at
    your own risk:

    EXEC sp_msForEachDatabase 'EXEC sp_revokedbaccess ''MYUSER''; exec
    sp_adduser ''MYUSER'', ''MYUSER''; exec sp_addrolemember N''db_owner'',
    N''MYUSER'''





    "Interflex" <com> wrote in message
    news:google.com... 


    Sponsored Links
    Aaron Guest

  2. #2

    Default Re: Adding the same user to all databases

    Aaron,

    Did you mean to say sp_msforeachdb?Even then EXEC sp_msforeachdb 'EXEC
    sp_grantdbaccess "MyUser", "MyUser"' doesnt work as it is looping within the
    database being executed upon.

    I guess we may need to loop within all databases in master..sysdatabases and
    execute the needed commands.

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    at 
    >
    >[/ref]


    Dinesh.T.K Guest

  3. #3

    Default Re: Adding the same user to all databases

    Oh yes, I meant foreachdb, sorry about that. And I meant to add a USE in
    front:

    EXEC sp_msforeachdb 'USE ?; EXEC sp_grantdbaccess ''MyUser'',
    ''MyUser''; --etc etc.'

    Sorry I'm rushing, end of the day here...



    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:OUjF#phx.gbl... 
    the 
    and 
    > at 
    > >
    > >[/ref]
    >
    >[/ref]


    Aaron Guest

  4. #4

    Default Re: Adding the same user to all databases

    Aaron,

    Perfect ! I missed adding the 'USE'. Thanks!

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    > the 
    > and [/ref][/ref]
    procedure [/ref][/ref]
    N''db_owner'', 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  5. #5

    Default Re: Adding the same user to all databases

    > Perfect ! I missed adding the 'USE'. Thanks!

    No no, that was my bad. I often rely too much on memory and post in haste.


    Aaron Guest

Similar Threads

  1. Adding User Login?
    By Infer_R3d in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 4
    Last Post: September 7th, 12:50 AM
  2. Adding User to Multiple Roles?
    By peter.shankland in forum Macromedia Contribute Connection Administrtion
    Replies: 4
    Last Post: October 7th, 02:57 PM
  3. Dynamically Adding User Control with Child User Control
    By Travis Pruitt in forum ASP.NET Building Controls
    Replies: 0
    Last Post: January 13th, 08:49 PM
  4. Adding User Control
    By Slawek in forum ASP.NET Building Controls
    Replies: 4
    Last Post: September 12th, 04:15 PM
  5. Adding user to group.
    By Sharad in forum Sun Solaris
    Replies: 8
    Last Post: August 11th, 02:06 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139