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 :D

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 getsYour 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|||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|||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