Post reply

TempDB User

  • June 26, 2004 at 3:49 am

    #83250

    Hi all,

    I added some users to tempdb as dbo, but whenever sqlserver restarts I should grant these db access again. Is there any way to automate this operation?

    Thanks in advance

    SaNaZ

     

  • June 26, 2004 at 10:33 pm

    #512086

    TempDB is re-created when you start SQL, which is why you lose the users and privileges.

    You can add them to model with the required privilege, therefore the creation of tempdb (which is based on model) will achieve the desired result.  This is a rather dangerous option though, because they'll become dbo in any other new dbs you may create, and given their unrestricted model privileges, it's also possible they may start making their own custom changes to model too.

    An option with reduced exposure is a "startup" stored procedure in master that executes the required sp_grantdbaccess and sp_addrolemember commands.  Eg.

    create proc MyStartup

    as

    exec tempdb..sp_grantdbaccess N'bob', N'bob'

    exec tempdb..sp_addrolemember N'db_owner', N'bob'

    go

    exec sp_procoption N'MyStartup', N'startup', N'true'

     

     


    Cheers,
    - Mark

  • June 27, 2004 at 12:44 am

    #512087

    Hi Mccork,

    Tahnks for your reply .

    when I ran this statement :

    exec sp_addrolemember N'db_owner', N'test'

    I got the  folwing error message:

    Server: Msg 15410, Level 11, State 1, Procedure
     sp_addrolemember, Line 66

    User or role 'test' does not exist in this database.

     
    Now I want to know how should I solve this error.
     
    Thanks again
    SaNaZ

  • June 27, 2004 at 1:07 am

    #512088

    I made a mistake  and now everything is ok. The sp works great.

    Thanks a lot

    SaNaZ

     

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

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