restoring database when guest account is missing

Hello,
I need to restore a database from backup, which has no guest account
specified. Sql user which executes the following statement is in Database
Creators server role:
RESTORE DATABASE pubs FROM DISK = 'C:\Temp\pubs_full.BAK'
WITH REPLACE,
MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf',
MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_log.ldf'
The message I receive is:
Processed 208 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
Server: Msg 916, Level 14, State 1, Line 1
Server user 'DK_user' is not a valid user in database 'pubs'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The database is actually restored and I could proceed to the next step in my
script, but @.@.error <> 0 which is not what I want to see. I can not create
'guest' account in the db backup as it is provided by the other department
and the user who is restoring the database can not be granted sysadmin
permissions.
If I create 'guest' account in 'pubs' database, backup it and restore,
everything works fine
Has anybody got ideas how to restore the database without receiving an error
message?
Thanks,
DariusHi
D you have the use 'DK_user' ?
> Server user 'DK_user' is not a valid user in database 'pubs'.
I did just testing
--removed a guest user from the pubs database and run backup command
backup database pubs to disk='c:\pubs2.bak'
go
--went to EM and create a guest account
restore database pubs1 from disk ='c:\pubs2.bak' WITH REPLACE,
MOVE 'pubs' TO 'c:\pubs2.mdf',
MOVE 'pubs_log' TO 'c:\pubs_Log2.LDF'
It worked without errors
"Sabotage" <nospam> wrote in message
news:uTew%23HktGHA.2392@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I need to restore a database from backup, which has no guest account
> specified. Sql user which executes the following statement is in Database
> Creators server role:
> RESTORE DATABASE pubs FROM DISK = 'C:\Temp\pubs_full.BAK'
> WITH REPLACE,
> MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs.mdf',
> MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_log.ldf'
> The message I receive is:
> Processed 208 pages for database 'pubs', file 'pubs' on file 1.
> Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'DK_user' is not a valid user in database 'pubs'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> The database is actually restored and I could proceed to the next step in
> my
> script, but @.@.error <> 0 which is not what I want to see. I can not create
> 'guest' account in the db backup as it is provided by the other department
> and the user who is restoring the database can not be granted sysadmin
> permissions.
> If I create 'guest' account in 'pubs' database, backup it and restore,
> everything works fine
> Has anybody got ideas how to restore the database without receiving an
> error
> message?
> Thanks,
> Darius
>|||Hi Uri,
Yes, I want to use DK_user, which has dbcreator server role, but is not
sysadmin (sorry, I haven't made myself clear enough in the previous post).
It woks fine when you restore with a user which has got sysadmin role. But
in my case DK_user has dbcreator role only. It's quite weird behaviour, as
after restore DK_user becomes the owner of pubs1 database, but is not
allowed to access it:
use pubs1
Server: Msg 916, Level 14, State 1, Line 1
Server user 'DK_user' is not a valid user in database 'pubs1'.
Darius
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OjDX2frtGHA.5044@.TK2MSFTNGP05.phx.gbl...
> Hi
> D you have the use 'DK_user' ?
>
> I did just testing
> --removed a guest user from the pubs database and run backup command
> backup database pubs to disk='c:\pubs2.bak'
> go
> --went to EM and create a guest account
> restore database pubs1 from disk ='c:\pubs2.bak' WITH REPLACE,
> MOVE 'pubs' TO 'c:\pubs2.mdf',
> MOVE 'pubs_log' TO 'c:\pubs_Log2.LDF'
>
> It worked without errors
>
>
> "Sabotage" <nospam> wrote in message
> news:uTew%23HktGHA.2392@.TK2MSFTNGP05.phx.gbl...
>|||Hi
If the database being restoring is aleady exist the member of the dbcreator
is available to run restore command
I did successfuly restored the database by using an user who's memebr of
dbcreator role ONLY
"Sabotage" <nospam> wrote in message
news:OX9mAYttGHA.1512@.TK2MSFTNGP03.phx.gbl...
> Hi Uri,
> Yes, I want to use DK_user, which has dbcreator server role, but is not
> sysadmin (sorry, I haven't made myself clear enough in the previous post).
> It woks fine when you restore with a user which has got sysadmin role. But
> in my case DK_user has dbcreator role only. It's quite weird behaviour, as
> after restore DK_user becomes the owner of pubs1 database, but is not
> allowed to access it:
> use pubs1
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'DK_user' is not a valid user in database 'pubs1'.
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OjDX2frtGHA.5044@.TK2MSFTNGP05.phx.gbl...
>|||My script needs to restore the database and to overwrite it if one exists
already. But I receive an error in both cases (SQL Server 2000 SP4). Have
you tried to restore if the database does not exist?
D.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ef$p8kttGHA.1808@.TK2MSFTNGP06.phx.gbl...
> Hi
> If the database being restoring is aleady exist the member of the
> dbcreator is available to run restore command
> I did successfuly restored the database by using an user who's memebr of
> dbcreator role ONLY
>
>
> "Sabotage" <nospam> wrote in message
> news:OX9mAYttGHA.1512@.TK2MSFTNGP03.phx.gbl...
>|||Wait, wait, if the database does not exist on the server the user needs
CREATE DATABASE permission , which means that your user does not have it in
this case.
Cannot test it on SP4 , right now I'm having only SP3
"Sabotage" <nospam> wrote in message
news:OQ7MFtttGHA.3392@.TK2MSFTNGP04.phx.gbl...
> My script needs to restore the database and to overwrite it if one exists
> already. But I receive an error in both cases (SQL Server 2000 SP4). Have
> you tried to restore if the database does not exist?
> D.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ef$p8kttGHA.1808@.TK2MSFTNGP06.phx.gbl...
>|||db_creator fixed server role is granted to execute several statement
permissions as CREATE DATABASE, ALTER DATABASE, DROP DATABASE, RESTORE
DATABASE and few others, so I think it's not the case...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Opo56xttGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Wait, wait, if the database does not exist on the server the user needs
> CREATE DATABASE permission , which means that your user does not have it
> in this case.
> Cannot test it on SP4 , right now I'm having only SP3
>
>
>
> "Sabotage" <nospam> wrote in message
> news:OQ7MFtttGHA.3392@.TK2MSFTNGP04.phx.gbl...
>|||Yep, I was reading just this explanation and must admit that its a little
bit confused
RESTORE command
Permissions
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database.
"Sabotage" <nospam> wrote in message
news:Owlhx7ttGHA.1536@.TK2MSFTNGP02.phx.gbl...
> db_creator fixed server role is granted to execute several statement
> permissions as CREATE DATABASE, ALTER DATABASE, DROP DATABASE, RESTORE
> DATABASE and few others, so I think it's not the case...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Opo56xttGHA.4968@.TK2MSFTNGP03.phx.gbl...
>|||Sabotage wrote:
> Hello,
> I need to restore a database from backup, which has no guest account
> specified. Sql user which executes the following statement is in Database
> Creators server role:
> RESTORE DATABASE pubs FROM DISK = 'C:\Temp\pubs_full.BAK'
> WITH REPLACE,
> MOVE 'pubs' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf'
,
> MOVE 'pubs_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_log.ldf'
> The message I receive is:
> Processed 208 pages for database 'pubs', file 'pubs' on file 1.
> Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
> Server: Msg 916, Level 14, State 1, Line 1
> Server user 'DK_user' is not a valid user in database 'pubs'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> The database is actually restored and I could proceed to the next step in
my
> script, but @.@.error <> 0 which is not what I want to see. I can not create
> 'guest' account in the db backup as it is provided by the other department
> and the user who is restoring the database can not be granted sysadmin
> permissions.
> If I create 'guest' account in 'pubs' database, backup it and restore,
> everything works fine
> Has anybody got ideas how to restore the database without receiving an err
or
> message?
> Thanks,
> Darius
>
I think the problem is that 'DK_User' is defined as a user in the
"source" database (the database that the backup was created from), but
has a different SID than 'DK_User' on your "restore" server. Do a
Google search for "sql server orphaned users" for information on how to
transfer a login from one server to another while retaining the original
SID.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
no, DK_user is not defined in the "source" database. Only "sa" user is
defined in the backuped database. It's not the case with orphaned users.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23b$fwNwtGHA.4928@.TK2MSFTNGP04.phx.gbl...
> Sabotage wrote:
> I think the problem is that 'DK_User' is defined as a user in the "source"
> database (the database that the backup was created from), but has a
> different SID than 'DK_User' on your "restore" server. Do a Google search
> for "sql server orphaned users" for information on how to transfer a login
> from one server to another while retaining the original SID.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com