Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    11

    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!

    Ponypoor

  2. #2
    Join Date
    Apr 2002
    Posts
    16
    Your permissions were not gone. They are located on the Master db and you probably copied only the production db.

  3. #3
    Join Date
    Feb 2002
    Posts
    11
    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.... ?

  4. #4
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    permissions

    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
    Deepak Khattar

  5. #5
    Join Date
    Feb 2002
    Posts
    11
    Thank you very much for the information I will try it out...



    Ponypoor

  6. #6
    Join Date
    May 2002
    Posts
    4

    RE copying over permissions on all tables...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •