Unanswered: Copying over permissions on all tables..... ??
We have a situation here were we always loose out permissions and then have to reapply them (and this is a LARGE company! with many group permissions..... Ughhh). I had thought at first that the permissions would be copied with the database when I copied it from one Server the other... not so I found out! So I applied ALL the permissions! Well we took a copy of Server #1 and replaced it on the other Server and all our permissions were gone.
Is there anyway that we can make sure that all the permissions come across. We are moving a database on NT 4 to another one on Win2000 they have different names... is it possible to make sure all the rights come across????
Why do we loose the current permissions (on the Win2000 Server) when we do a complete restore (move over) of the NT 4 systems database to the Win2000 systems database?? Its irritating having to redo all those permissions!
Try selecting the "generate sql script..." option from enterprise manager and on the "options" tab select "Script database users and roles", "Script SQL Server logins" and "Script object-level permissions".
Some combination of these should at least generate a script that you can apply after restoring the backup.
I'm always leery of restoring a master database from one server to another. No specific reason why but it just seems like it could cause problems.