January 6, 2011 at 12:40 pm
Does anyone here know if there is a way to drop and recreate a table without losing the user permissions attached to it?
BTW, the DB uses mixed-mode access because there are no domain controllers.
January 6, 2011 at 12:45 pm
Don't give database users direct permissions to table. Give database role(s) access to the table, and add or remove database users from those roles as needed. When you drop and create the table again, grant the database roles the same access to the tables as they had before.
January 6, 2011 at 1:18 pm
We have only server roles, not user roles, because of not being able to use domain controllers. But even so, wouldn't the same issue still be there? I would have to go to the table and grant the user roles permissions. The idea being to refresh the data in the table (which happens daily on a large scale) but not have to continually renew the grants - user role or individual.
January 6, 2011 at 1:58 pm
We have only server roles, not user roles, because of not being able to use domain controllers...
Domain controllers have nothing to do with the use of database roles. Database users, not server logins, are assigned to database roles.
...I would have to go to the table and grant the user roles permissions. The idea being to refresh the data in the table (which happens daily on a large scale) but not have to continually renew the grants - user role or individual...
If you can avoid it, don't drop the tables. Use truncate to clear out the old data, and you won't have to re-add permissions.
If you are going to drop and create tables daily, you will need to add the permissions again, and that is why I suggest using database roles to simplify it.
It sounds like you don't actually know what a database role is, so I would suggest that you read about them in SQL Server Books Online.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply