role permission of tempdb due to server restart

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

role permission of tempdb due to server restart

Author

Topic

ctatva
Starting Member

1 Post

Posted - 2009-11-14 : 00:32:26

in our application that need to create some tables in tempdb. The user
(call it APP) is not sa. Everytime that the server need to be rebooted, the db_owner role on tempdb for user APP disappear. So...

1. Is there any setting to ensure that the the db_owner role on tempdb for user APP will remain after server reboot?

2. If not,
Is there any solustion to run a script at server startup?
And does anybody have a script that will create the db_owner role on tempdb for user APP(or some pointer on how)?

Chirag Vyas
Project Manager
Software Outsourcing India
www.tatvasoft.com
www.sparsh-technologies.com

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-14 : 09:33:17

You can create a SQL Agent job that starts when the SQL Agent service starts to execute your script:


use tempdb;
GO
create user APP
for login APP
with default_schema = dbo
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'APP'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources