losing permisions in tempdb

Hi,
I have noticed that everytime sql server restarts the permission on tempdb g
o away. Is there a way to fix this?
Also i thought this sort of information was stored in the master database?Tempdb is recreated whenever SQL Server is restarted. Guest
user exists in tempdb by default which is how users access
tempdb for temp tables and such. What permissions are
causing problems? Tempdb is recreated using the model
database as a template and maybe you have something wrong
with the model database. Hard to say as I don't know what
you are trying to accomplish or what problems the recreation
of tempdb is causing you.
-Sue
On Thu, 1 Apr 2004 08:11:16 -0800, "Jason"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have noticed that everytime sql server restarts the permission on tempdb
go away. Is there a way to fix this?
>Also i thought this sort of information was stored in the master database?|||Generally you shouldn't be granting permissions to users in tempdb. What is
the requirement for this? (As Sue said, tempdb gets rebuilt every time the
SQL Instance or server is restarted - this is a desirable thing).|||Hi,
It is a vendor supplied solution. They use tempdb to store session state.
They didn't use a guest account to access tempdb though, there is an applica
tion account that reads and writes to tempdb with session state data.
When the server is bounced, SQL Server restarts and the application account
loses all privledges to the temp db.
Also, I thought all the privledge info was saved in the master db, it seems
based on this that at least some info is also stored in the target database
as well...can you explain, or point me to a book online chapter that explai
ns this.
thanks|||Try setting up whatever users, permissions the application
needs in model database. Tempdb is recreated using model as
a template.
The help topic Users in SQL Server books online explains the
difference between users and logins and some of what is
stored where. User accounts are specific to a database and
the user account is associated with permissions and object
ownership in a given database. Master will store information
on logins as well as user information specific to the master
database (not all of the databases).
-Sue
On Mon, 5 Apr 2004 06:16:06 -0700, jason_fin
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>It is a vendor supplied solution. They use tempdb to store session state.
They didn't use a guest account to access tempdb though, there is an applic
ation account that reads and writes to tempdb with session state data.
>When the server is bounced, SQL Server restarts and the application account
loses all privledges to the temp db.
>Also, I thought all the privledge info was saved in the master db, it seems
based on this that at least some info is also stored in the target database
as well...can you explain, or point me to a book online chapter that expla
ins this.
>thanks
>

Looking For Technique To Default Execute Permission on any new SPr

Hi,
I'm looking for a way, for when any procedure is created or updated in a
user database, that a particular DB role be granted execute permissions on
the proc.
This role is a: datareader,datawriter, and ddladmin, but can not be
db_owner. I know that I can grant execute en-masse using a variety of T-SQL
techniques, but I would have to do this periodically. Also, adding a GRANT
on the tail-end of the CREATE PROCEDURE would not work for me.
I was beginning to create a trigger on sysobjects for CREATE,UPDATE when the
object type is 'P' (for procedure) to then issue the GRANT, but soon realize
d
that triggers on system tables are not permitted!
Does anyone have any suggestions?
Thanks very much,
SteveTIf you are on SQL Server 2005, you could grant execute
permissions on the schema to the role -
GRANT EXECUTE ON SCHEMA::YourSchema TO YourRole
Otherwise, not much you can do if you can't add a grant
statement to the create procedure script.
-Sue
On Thu, 2 Nov 2006 13:49:02 -0800, SteveT
<SteveT@.discussions.microsoft.com> wrote:

>Hi,
>I'm looking for a way, for when any procedure is created or updated in a
>user database, that a particular DB role be granted execute permissions on
>the proc.
>This role is a: datareader,datawriter, and ddladmin, but can not be
>db_owner. I know that I can grant execute en-masse using a variety of T-SQ
L
>techniques, but I would have to do this periodically. Also, adding a GRANT
>on the tail-end of the CREATE PROCEDURE would not work for me.
>I was beginning to create a trigger on sysobjects for CREATE,UPDATE when th
e
>object type is 'P' (for procedure) to then issue the GRANT, but soon realiz
ed
>that triggers on system tables are not permitted!
>Does anyone have any suggestions?