Post reply

Need Script to change default DB and add DB-User

  • October 4, 2006 at 1:27 am

    #60874

    Hi pros,

    i have to change for all logins at a sql-server instance the default Database and i want to add for each login a database user with the database role public (i do it til now in Enterprise Manager \ SQL -Server Logins \Tab Database Access first grid Databases and grid Database Roles).

    I use SQL-Server 2000.

    Thanks in advance

  • October 4, 2006 at 7:47 am

    #664095

    To change default database,

    sp_defaultdb [ @loginame = ] 'login' , [ @defdb = ] 'database'

    To add a db user,

    sp_adduser [ @loginame = ] 'login' [ , [ @name_in_db = ] 'user' ] [ , [ @grpname = ] 'group' ]

  • October 4, 2006 at 11:49 am

    #664145

    I recently had to do this and used this method:

    1. In Enterprise Manager, right-click on a database object e.g. a table or view and select  'Generate SQL Scripts'

    2. On the Options tab, select 'Script database users and database roles' and 'Script SQL Server logins(Windows and SQL Server logins)'

    3. Click OK.  A script will be generated that contains the statements that Chaohua Wang suggested.

    4. Edit the script file that was generated to remove the statements you don't need and change the default database value and database role value.

    5. Execute the script in Query Analyzer.

    Greg

    Greg

  • October 5, 2006 at 1:35 am

    #664234

    Hi,

    thank you very much for you help!

    Jürgen

Viewing 4 posts - 1 through 3 (of 3 total)

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