Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unanswered: permission question

    Hi,

    I granted a domain user login:
    --read only to our production db.
    --db_owner to msdb.

    I want this login to be able to create jobs & dts packages.
    he's able to create dts packages, but when he tries to create a new job, in the db dropdown menu (steps tab) he can't see the production db. He only sees msdb, master & temp... he needs to see the production db, so that he can create a job.

    please help!
    Meera

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    You have done the right thing allowing access to MSDB. He should be able to create DTS packages anyway ( anyone can ) .

    With his login, what is his default database? Also, has his login ( not a group etc ) been actually granted access to the production database?

    I say this as the Public group in a database can be emptied in the database, thereby not everyone on the server is granted access automatically to the database.

    Cheers

    SG

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    Thanks for replying!

    This guy belongs to a domain user group called dbCustomReports. So, when he registers his servers in Enterprise manager using the runas command, he'll be registered as Domain/dbCustomReports.

    His default db is the production db.

    The login in SQL server is actually Domain/dbCustomReports. That login has db_owner permissions on msdb & production.

    Which login should I grant permissions to? I didn't follow that part of your question.... (he doesn't have any individual sql server login)

    Any help is appreciated.
    Thanks,
    Meera

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    you should grant the domain/dbCustomReports access to you production database. Setting a default database does not grant access to that database. You have granted permissions to the group to which the user belongs. Be aware that anyone else belonging to that group has the same privileges in the database.
    Johan

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    That account has been granted db_owner priviliges to the production db & then I set the default db
    Meera

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    can the user see the production database in the enterprise manager or also only msdb, master and temp?
    Johan

  7. #7
    Join Date
    Sep 2003
    Posts
    16
    in EM, the user can see the production db and all the other db (of course when he tries to see the tables, it won't let him.)

    Thanks,
    Meera

  8. #8
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    How did you set up the permissions for this user? Are there any other privileges or database roles granted?
    Johan

  9. #9
    Join Date
    Sep 2003
    Posts
    16
    database called CR --- db_owner
    msdb -- db_owner
    database called Prod -- read only

    Meera

  10. #10
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    I think you should only give read only to the first one too

    Paulo

    Originally posted by meeraarvind
    database called CR --- db_owner
    msdb -- db_owner
    database called Prod -- read only

    Meera

Posting Permissions

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