Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    9

    Unanswered: User permissions after load database

    I've notice that when I load a database dump, users seem to loose their premissions and access rights, the main reason is that suid and uid between master database and the loaded database gets mixed up.

    I hope I've explained my problem clearly.
    so does anyone face such situation? how to overcome this problem, I'm in a development stage for a software and dumping/loading happens like 4 to 5 times a day.

    appreciate the responses

    Regards

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Boiling Ice
    I've notice that when I load a database dump, users seem to loose their premissions and access rights, the main reason is that suid and uid between master database and the loaded database gets mixed up.

    I hope I've explained my problem clearly.
    so does anyone face such situation? how to overcome this problem, I'm in a development stage for a software and dumping/loading happens like 4 to 5 times a day.

    appreciate the responses

    Regards
    You could make a script that drops all users/login/rights and recreates them. A one time job to create the script, but it only takes a click on a button to execute it.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Aug 2005
    Posts
    9
    Thank you for your reply. This is what I've done so far but this script has to be maintained all the time, otherwise permissions won't be up-to-date.

    is it always the solution or is there any other ways?
    I would like to read what other people are doing when they restore their database!!

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I make sure that syslogins are in sync on source and target

    bcp master..syslogins out /tmp/syslogins.txt -Usa -P<pw> -S<source> -c

    edit /tmp/syslogins.txt and remove sa and probe (first 2 lines)

    isql -S <target>
    sp_configure 'update',1
    delete from syslogins where suid>2 -- NB. Leave a login for below bcp
    exit

    bcp master..syslogins in /tmp/syslogins.txt -Usa -P<pw> -S<target> -c

    isql -S <target>
    sp_configure 'update',0
    _______________________

    Or if login names and database user names are the same you can try

    update sysusers
    set suid=l.suid
    from sysusers u,master..syslogins l
    where u.name=l.name
    and u.suid>0

    and check/fix sysalternates for aliases

Posting Permissions

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