Post reply

restore problem

  • May 20, 2008 at 3:51 am

    #187975

    hi all,

    i have created a login for A for d/b A . and have given db creator server role to it. apart from it i have given all the d/b roles except for denyreadre and denywriter. i am trying to restore the d/b A get the follwing eror while restoring.

    Server: Msg 3110, Level 14, State 1, Line 1

    User does not have permission to RESTORE database 'test'.

    Server: Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Kindly advice.

    thanks for the help.

  • May 20, 2008 at 4:10 am

    #817675

    Hi Sharon

    The first thing to check:

    * Is the login A created at the Server Level under --Security Logins?

    * The user at this level must then be given server roles

    * The server login can then be given rights to the TestDB

    * Delete the user from DB level first if you are not having joy trying to assign rights to existing user

    let us know

    Thanks

    Kevin

  • May 20, 2008 at 4:20 am

    #817682

    kevin,

    i created the login as foloows

    under security - logins - new login - test (name of login)

    added the db creator role

    default db master

    gave all the d/b roles to master except for deny reader and writer

    i log on to the server using test

    exceute restore d/b command

    give me the error

    Server: Msg 3110, Level 14, State 1, Line 1

    User does not have permission to RESTORE database 'test'.

    Server: Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    thanks for your help

  • June 3, 2008 at 1:56 am

    #823448

    Sharon:

    Have you solved it?

    I am having the same problem. I found these posts http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766295&SiteID=1, but they cannot solve my problem.

    Thanks,

    CYH

  • June 3, 2008 at 2:07 am

    #823449

    no. i have found no soultion. incase you do please let me know..

    thanks

  • June 3, 2008 at 2:19 am

    #823455

    Sharon:

    Right click on SQL Server Management Studio and choose 'Run as administrator'. Try to restore again!

    CYH

  • June 3, 2008 at 2:23 am

    #823458

    sorry i did not mention it earlier i am using sql server 2000

  • June 3, 2008 at 2:29 am

    #823461

    Hi - I have tested this in my environment and used the following steps:

    Step 1: logged onto the SQL Server - in EM I expanded security under servername or (local)

    Step 2: under security - click on logins - right-click - select new login

    Step 3: Select SQL Server Auth - type username Test

    Step 4: under Server Roles Tab - tick the Create Databases Role

    Step 5: Click ok and give password and confirm password

    Step 6: Run script similar to the following or do via EM:

    RESTORE DATABASE [Test] FROM DISK = N'D:\MSSQL\Backups\eMarketing30\eMarketing30_db_200711132100.BAK' WITH FILE = 1, MOVE N'eMarketing3' TO N'D:\MSSQL\Data\Test.mdf', MOVE N'eMarketing3_log' TO N'D:\MSSQL\Data\Test_1.ldf', NOUNLOAD, STATS = 10

    GO

    After the restore I got this message:

    10 percent restored.

    20 percent restored.

    30 percent restored.

    40 percent restored.

    50 percent restored.

    60 percent restored.

    70 percent restored.

    80 percent restored.

    90 percent restored.

    100 percent restored.

    Processed 517296 pages for database 'Test', file 'eMarketing3' on file 1.

    Processed 2 pages for database 'Test', file 'eMarketing3_log' on file 1.

    Server: Msg 916, Level 14, State 1, Line 1

    Server user 'Test' is not a valid user in database 'Test'.

    Server: Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    The Database has been restored 100% - but the error message says that the user "Test" does not yet have rights to view or access the database - this can be given under security / logins - under the servername

    let me know if this helps

    Thanks

    Kevin

  • June 3, 2008 at 4:32 am

    #823502

    thanks kevin for your help.

    i tried creating a new user and this thing worked. but if i give rights (dbcreator) to an exisiting user it does not. don't know as to waht is really going wrong.

  • June 3, 2008 at 7:50 am

    #823615

    Are you able to re-create the user?

  • June 3, 2008 at 8:51 am

    #823670

    make sure that the user you created has a access to the said database..

  • June 3, 2008 at 4:49 pm

    #823911

    Try dropping the existing user(& login) and then creating login+user again with same rights and privileges as they had before.

    Manu

  • June 3, 2008 at 11:34 pm

    #823989

    thnaks everybody for the help. if i drop and recreate the user everything works fine.....but i was wondering as to why when i jsut give additional rights to the user it jsut does not take it . some bug ?

  • June 3, 2008 at 11:53 pm

    #823993

    Great to hear that all is working fine.

    Yes - something strange seemed to be happening when changind or editing security rights for that user.

    Kevin

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply