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!

Ponypoor:mad:Your permissions were not gone. They are located on the Master db and you probably copied only the production db.|||I am not quite sure what you mean - We are taking the backup of the whole database (which contains many tables...) and then applying it to the new SQL Server... were do I find the 'master' one... ?|||Hi,

Permissions on a object for a user depends on three tables
a. SYSLOGINS/sysxlogins
b. SYSUSERS
c. syspermissions
d. sysprotects
e. sysobjects

syslogins reside on master database and others reside on user db.

all tables are linked with each other on the basis of suid of sys(x)logins.

once you copy user database from one server to another server all the links are messedup. since suid of one server is different from other.

To RESOLVE this use sp_change_users_login to correct the problem. Read more on BOL

Best of luck|||Thank you very much for the information I will try it out...

Ponypoor|||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.