Post reply

Create DB Users for New Databases

  • June 19, 2003 at 10:38 am

    #82775

    I am trying to create database users for many newly created databases and assign the db_owner role. I tried to use a CURSOR to work on a database at a time together with the following command. However, the USE @DBName doesn't seem to work. Please help.

    Use @DBName

    EXEC

    sp_adduser 'Victoria', 'Victoria', 'db_owner'

  • June 19, 2003 at 11:06 am

    #462207

    How about this (ker-lunk!)

    declare @dbname as varchar(100)

    declare @strSQL as varchar(100)

    declare @strSQLCommand as varchar(100)

    set @strSQLCommand='exec sp_adduser ''Victoria'', ''Victoria'', ''db_owner'''

    set @dbname='Whatever'

    -- your db loop here

    set @strsql='USE ' + @dbName + '; ' + @strSQLCommand + ';'

    exec(@strSQL)

  • June 19, 2003 at 12:30 pm

    #462233

    Thank you very much for the input. Any suggestions on how to automate the process of adding hundreds of users? Thanks again.

    WM

  • June 19, 2003 at 12:55 pm

    #462248

    Does my suggestion not work or are you asking another question?

  • June 19, 2003 at 1:10 pm

    #462252

    or to put it another way ...

    1) The code you posted won't work if @dbName is a varchar (if that is what you are doing)

    2) i *think* that if you change db context within a dynamic EXEC statement, that context only remains for the duration of the EXEC -- so you need to concatenate all your sp_adduser statements, separated with semi colons and blam them all in one go. (or do each statement one by one with the 'USE ...' prepended).

    However this solution is klunky and a better way probably exists.

    Perhaps you could post your complete code?

  • June 19, 2003 at 2:21 pm

    #462276

    Your suggestion worked. I am just wondering if there is an easier way to handle more users and logins. Can I put all database names, user names, logins, and database roles in one or more tables, and process them using a cursor?

  • June 20, 2003 at 2:46 am

    #462345

    Yes.

  • June 20, 2003 at 9:53 am

    #462432

    Could you elaborate and privide some sample code to get me started? Thank you.

    WM

  • June 20, 2003 at 5:16 pm

    #462496

    Anybody out there has a sample script?

    Thanks again.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply