January 18, 2013 at 12:14 pm
I have created a view where I join a table from different database on same database server. In that another database, tables are dropped and created every night from other oracle databse. :crying: (Don't ask me why? thats the stupid decesion by so called DBA). I am giving select permission to my application user to only one table on another database so user can fetch the data from my view when needed. But problem is when the table is dropped in night, app user loose the rights and can't access the view. I can make the app user as member of db_datareader and make it work, but it will give app user select rights to 100s of tables instead of just one. Can anyone please suggest any solution?
January 18, 2013 at 12:18 pm
mak101 (1/18/2013)
I have created a view where I join a table from different database on same database server. In that another database, tables are dropped and created every night from other oracle databse. :crying: (Don't ask me why? thats the stupid decesion by so called DBA). I am giving select permission to my application user to only one table on another database so user can fetch the data from my view when needed. But problem is when the table is dropped in night, app user loose the rights and can't access the view. I can make the app user as member of db_datareader and make it work, but it will give app user select rights to 100s of tables instead of just one. Can anyone please suggest any solution?
Wow that is a crappy "architecture" huh? Maybe you can create a job that will grant select on the table(s) to a given user? Then just schedule that job to run after the table has been recreated.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 18, 2013 at 12:48 pm
Yeah, I guess that is better option.
Thanks.
January 18, 2013 at 12:52 pm
You could also put the tables in their own schema and grant the user select on the schema.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply