Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marshaki
Starting Member
5 Posts |
 Posted - 2008-11-12 : 13:45:12
|
| We are using SQL Server 2000. Whenever, a DTS runs which has to drop a table and create it again, all the information regarding the right to access that table are cleared, so we have to create them again, going to Login section of SQL Server Enterprise Manager. I was woundering if there is a system table that holds this information, so we can save a copy of it, and reinstate it later? I would appreciate if anybody can share their expereince with me. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-11-12 : 19:33:12
|
| Dropping an object and recreating it frequently is just a bad idea. Couldn't you truncate the table which would keep your permissions and just reload it?Mike"oh, that monkey is going to pay" |
 |
|
|
marshaki
Starting Member
5 Posts |
Posted - 2008-11-13 : 12:19:35
|
| Thanks for the reply, mfemenel. We have so many DTS packages. In almost all of them we have a drop, followed by a crreate, then followed by a DTS Transfer. The reason for this arrangement is that our data comes from different areas of our organizations and they keep changing the layouts of the tables.Your comments gave me an idea, as the last resort; I can replace the drop and create objects with SQL object that delets all the records. However, we need to keep two sets of DTS packes; one for daily updates with the above format, the other for the occasional change in the layouts, with the old format (ie, drop-Create-Transfer).Thanks. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-11-14 : 14:51:56
|
| You can add following code in your DTS job after you drop and recreate table.grant select on 'yourtable' to 'abc'grant update on 'yourtable' to 'def'grant delete on 'yourtable' to 'ghi'.................So every time DTS job runs,it grants the appropriate rights to appropriate users. |
|
|
|
marshaki
Starting Member
5 Posts |
Posted - 2008-11-18 : 13:00:07
|
| Great femenel. I will try that. The code is what I have been looking for. I will try it. Sorry that I had not checked the messages for couple of days. Thanks. |
|
|
|
marshaki
Starting Member
5 Posts |
Posted - 2008-11-18 : 13:00:50
|
| Great femenel. I will try that. The code is what I have been looking for. I will try it. Sorry that I had not checked the messages for couple of days. Thanks. |
|
|
|
marshaki
Starting Member
5 Posts |
Posted - 2008-11-18 : 13:02:07
|
| Oobs. That message was from ssunny. Thanks ssunny for the tips. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-11-18 : 15:27:23
|
quote:
Originally posted by marshaki
Oobs. That message was from ssunny. Thanks ssunny for the tips.
Cheers. |
|
|
|
|
|
|
|
|
Priblem with Giving Permission to Access Tables