SQL Server Performance

role permission of tempdb due to server restart

Discussion in 'SQL Server 2008 General DBA Questions' started by tatvasoftseo, Nov 13, 2009.

  1. tatvasoftseo New Member

    in our application that need to createsome tables in tempdb. The user
    (call it APP) is not sa. Everytime thatthe server need to be rebooted, the
    db_owner role on tempdb for user APPdisappear. So...
    1. Is there any setting to ensure thatthe the db_owner role on tempdb for
    user APP will remain after serverreboot?
    2. If not,
    Is there any solustion to run a scriptat server startup?
    And does anybody have a script thatwill create the db_owner role on tempdb
    for user APP(or some pointer on how)?
    Chirag Vyas
    Project Manager
    Software Outsourcing India
    Web Development Company India
    tatvasoftseo, Nov 13, 2009
    #2

  2. Luis Martin Moderator

    Welcome to the forums!!
    What kind of tables?. I mean: temporary tables?
    Luis Martin, Nov 15, 2009
    #3

  3. satya Moderator

    Welcome to the forums.
    This behaviour is by design as and when SQL restarted TEMPDB is restructured from system settings.
    First things first I would recommend not to make this APP user as DB_OWNER for TEMPDB, rather you can grant access for particular actons WRITE or SCHEMA OWNER etc.
    Then coming to script see:
    USE master;
    GO
    EXEC sp_configure 'show advanced option', '1';
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sp_configure 'scan for startup procs', '1';
    RECONFIGURE WITH OVERRIDE
    GO
    CREATE PROC spAdduser
    AS
    exec tempdb..sp_adduser 'APP', 'APP', 'db_owner'
    GO
    -- set it to run at sql server start-up
    exec sp_procoption N'spAdduser', 'startup', 'on'
    satya, Nov 16, 2009
    #4

Share This Page