dbowner on tempdb - restart of sql service resets this

[divinyl]

Hi all

An application we host requires a user to have db owner rights on the tempdb. After each sql service restart the tempdb gets recreated and these settings are lost. So i need to identify a way of reassigning the user the rights on tempdb every time sql service starts up.

Any ideas on how best to do this?

Thanks,
Div

 

[mutley1]

This probably isn't ideal, but it will work given certain circumstances.

First question: Is your SQL Server Agent set to start on a restart of SQL Server. If it is, then you could create a job to assign rights to the tempdb, and in the scheduling select "run on SQL server agent startup".

Like I say, not ideal, but something you could look at.

Cheers,

M

 

[divinyl]

Thanks Mutley - i guess that would have worked as well, but i've found something else that works well - others may find this solution of use:

Basically stored procedures can be configured to automatically run when the sql service restarts. The stored procedure has to belong in the master database, and owned by dbo. The other limitation is that the stored proc cannot have any input or output parameters.

So what you need to do is this:

First, you have to enable SQL Server to scan the system everytime the sql server service starts up - this is an option in sql server properties and is false by default. You then create your stored procedure in the master database - so for my purposes, this was what i needed:

create procedure assignTempDbRights
AS
EXEC ('use tempdb; EXEC sp_adduser ''login'', ''user'', ''db_owner''')

You then need to run the sp_procoption system stored proc which will make the stored procedure above automatically execute on startup:

sp_procoption @ProcName = 'assignTempDbRights',
@OptionName = 'startup',
@OptionValue = 'true'

And voila - the stored procedure runs everytime the sql service starts up and the user is assigned permissions on temp db. Works a treat!

Cheers
Div

 

[mutley1]

Cheers Div,

We have a blackbox trace on startup set here - don't know why I didn't remember that and advise...sorry! Glad you got it sorted.

BR,

M.