Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Grant Existing User Access to a new DB

    I run this script in the morning to restore a db.
    Code:
    DROP DATABASE Data1
    RESTORE DATABASE Data1
    FROM DISK = 'D:\Data1_Backup\Data1_backup.bak'
    WITH RECOVERY,
    MOVE 'Data1' TO 'D:\MSSQL\Data1.MDF',
    MOVE 'Data1_log' TO 'D:\MSSQL\Data1_log.LDF'
    GO
    This is fine if I'm logged in using Windows Auth, but if I want to use a SQL Server Login, it drops the access.

    Is there an TSQL Statement to grant access to a new db?

    The User already exists and has access to other DB's on the server.

    Ideally I want something like (Ignore Syntax as it's incorrect)

    ALTER USER AnalysisAdmin
    WITH DATABASE DATA1
    grant access = db_owner & datawriter
    Schema = dbo

    Cheers

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note that a User and a Login are not the same thing. Logins are what are used to authenticate to SQL Server, Users are specific to databases. You associate logins with users. Typically, users and logins have the same names which can confuse matters a bit.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2006
    Posts
    54
    Under the Main Security / Logins, there is 'AnalysisAdmin'

    Analysis Admin has access to several database on the server.

    Under User Mapping, I wish to map the newly added database (as db_owner & writer) with the default schema dbo

    Cheers

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out sp_change_users_login in BoL
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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