TempDB User

• sani

  SSCrazy

  Points: 2477

  More actions

  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

   

• mccork

  SSC-Insane

  Points: 22288

  More actions

  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

• sani

  SSCrazy

  Points: 2477

  More actions

  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

• sani

  SSCrazy

  Points: 2477

  More actions

  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