Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: Permissions question.. (hey, didja miss me?)

    Hi all,

    Wow...since I've been gone, the search feature is apparently working again! Kewl!

    However, after doing a search and not finding anything pertaining to my current issue, here I am again

    I access a table on a linked server/database apart from my application's DB.

    Every day, a process on the other server/DB drop and recreate the table....which, of course, zaps my user permissions. I then have to go into the Enterprise Mgr and re-enable my SELECT access to the table.

    My question is this: Is there a way to auto-magically set my user ID on the linked server to give me access to the table without having to go in explicitly on a daily basis and re-enabling my UID Access rights.

    This is the only table my user ID has been given access to on the remote server, so I am not able to simply set up my user ID on the remote system to "allow everything"

    Am I missing something obvious? Do I have to create a SP on the remote server to do a daily GRANT SELECT ON Remote_Table TO Paul_Dammit after the table is rebuilt each day???

    Suggestions?



    It gets
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your only option at the database level would be to give a user "carte-blanche" access to all of the tables within that database by making them a memeber of the db_datareader group.

    For one specific table, I'd create a job that ran every 6 minutes or so, with one SQL step that tests to see if the table is there then grants permission if it is. I'm a "fewer moving pieces" kind of guy.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    That's what I was afraid of, Pat...thanks for the reply! For the time being, I'll do that...I have a control table that I can read for a flag indicating when the drop/create has occurred, so I can simply does the grant when it happens.

    I was just hoping to not have to add a job to someone else's DB...oh well...the price they pay for being so selective about the riff-raff they let in, I guess

    Thanks again!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess from my perspective, if they see adding a one step job as too much of a pain, then they need to make you a member of db_datareader. That way they can do it once. Neither task takes over 40 seconds for a competent DBA, and once scheduled the only thing they need to do is watch for errors in the server log, which they'd better be doing anyway!

    If they see this as a problem, they REALLY need to reevaluate their attitude. There might be collatoral duties that this job implies, but those are enforced by the organization and should be significantly less of a problem than granting the permissions manually.

    -PatP

Posting Permissions

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