SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    TempDB and Guest

    Hello everyone

    I just wondering whether it is safe to set the guest logins password for access to tempdb, as I really do not want to use SA to access it and all other logins are dropped when the sql server service is restarted as tempdb gets recreated.

    Any other solutions I may not be aware of?
    .NET Code Monkey

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the guest logins password for access to tempdb
    Sorry, I don't understand, what do you want to do? Set the guest login's default database to tempdb?

    Please also explain what you mean by "guest login".

  3. #3
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    When ever tempdb is recreated by MS SQL it only ever has two logins, SA and Guest.

    Now I am not comfortable using SA, so was wondering whether me changing the password for guest would break any other aspects of SQL Server, and would a password change just be undone when SQL Server recreates tempdb?

    Is there any way to have SQL Server recreate a custom user when it recreates tempdb?
    .NET Code Monkey

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    well, the reason why I ask is because your system is nonstandard, someone has set it up that way. MS SQL does not, by default, have a login named "Guest".

    Look in the database named "model", in the Users section, see if there is an account called "guest". These accounts get copied to the tempdb each time it is created. If you need a new account, make one in the "model" database, and it gets automatically copied to the tempdb.

    HTH

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What also could be happening is the following:

    Someone created a databas user (not the same as a login account!). You can list all the users in the security section of enterprise manager. Lets say someone created a user named "limitedrights".

    Then someone went to the model database, and added a new login. The login is for the user named "limitedrights", and the login account was named "guest".

    To setup the same scheme you would:

    a) Create a new database user (in the security section). Assign the password here.
    b) Create a new login for your user in the model database. Assign the permissions here.

    HTH

  6. #6
    SitePoint Wizard big_al's Avatar
    Join Date
    May 2000
    Location
    Victoria, Australia
    Posts
    1,661
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks asteriks!
    .NET Code Monkey


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •