Databases, logins and jobs on standby server

I have restored my production server master database to the standby server.
I then ran a series of scripts I found at KB246133:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
I found that my standby server showed itself as a remote server. I created
linked servers between standby and production; I executed sp_dropserver
'(production server name)' and then sp_addserver (standby server name).
SP_dropserver resulted in a message that said that logins existed for this
server. When I look in the sysservers table on the standby server I have
two entries: one for the production server and one for the standby server.
I've also created a backup device on both the production and standby server
which are the same. The backup file from production is copied to standby so
that the restore can be done on the standby server.
My scripts to copy the backup file and restore the database work fine from
QA on the standby server. However, when I try to execute them as part of a
job, I get failures. The step which copies the database over says:
Could not relay results of procedure 'CopyDatabase_DukeAccount' from remote
server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error
7312). The step failed.
I am confused why SQL thinks that NCNSV1010 is the remote server since it is
actually the local server that this job is executing on. Additionally, the
backup file IS actually copied over successfully.
The step which restores the database to the standby server, which is the
local server, from the backup device says:
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
Cannot open backup device
'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or device
off-line. See the SQL Server error log for more details. [SQLSTATE 42000]
(Error 3201). The step failed.
However, when I open the backup device to view contents, the just copied
backup file is visible in it.
Both servers are running W2K SP4, SQL2K SP2 (with security patch) Standard
Edition.
Does anyone have any idea what is wrong here? Do I need to drop the logins
for the (production server name) that exist on the standby server? If I do
that, will I then have to run the process again to transfer the logins? I
can't seem to drop the production server from the sysservers table if they
have logins. I could manually drop it from the table, but I don't know what
repurcussions that may have.
I need to figure this out because I must automate this process to run daily,
then the T-Logs many times a day, and I'll be doing this for quite a few
databases on these machines.
Thanks in advance.
Deborah> I found that my standby server showed itself as a remote server. I
created
> linked servers between standby and production; I executed sp_dropserver
> '(production server name)' and then sp_addserver (standby server name).
Seems you forgot to specify the ,local option to sp_addserver.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
in message news:eb2bk2FsDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I have restored my production server master database to the standby
server.
> I then ran a series of scripts I found at KB246133:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
> I found that my standby server showed itself as a remote server. I
created
> linked servers between standby and production; I executed sp_dropserver
> '(production server name)' and then sp_addserver (standby server name).
> SP_dropserver resulted in a message that said that logins existed for this
> server. When I look in the sysservers table on the standby server I have
> two entries: one for the production server and one for the standby
server.
> I've also created a backup device on both the production and standby
server
> which are the same. The backup file from production is copied to standby
so
> that the restore can be done on the standby server.
> My scripts to copy the backup file and restore the database work fine from
> QA on the standby server. However, when I try to execute them as part of
a
> job, I get failures. The step which copies the database over says:
> Could not relay results of procedure 'CopyDatabase_DukeAccount' from
remote
> server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000]
(Error
> 7312). The step failed.
> I am confused why SQL thinks that NCNSV1010 is the remote server since it
is
> actually the local server that this job is executing on. Additionally,
the
> backup file IS actually copied over successfully.
> The step which restores the database to the standby server, which is the
> local server, from the backup device says:
> RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
> Cannot open backup device
> 'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or device
> off-line. See the SQL Server error log for more details. [SQLSTATE 42000]
> (Error 3201). The step failed.
> However, when I open the backup device to view contents, the just copied
> backup file is visible in it.
> Both servers are running W2K SP4, SQL2K SP2 (with security patch) Standard
> Edition.
> Does anyone have any idea what is wrong here? Do I need to drop the
logins
> for the (production server name) that exist on the standby server? If I
do
> that, will I then have to run the process again to transfer the logins? I
> can't seem to drop the production server from the sysservers table if they
> have logins. I could manually drop it from the table, but I don't know
what
> repurcussions that may have.
> I need to figure this out because I must automate this process to run
daily,
> then the T-Logs many times a day, and I'll be doing this for quite a few
> databases on these machines.
> Thanks in advance.
> Deborah
>
>|||Tibor,
You are absolutely right, I hadn't specified local.
Thank you!
Deborah
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:Om179$YsDHA.1996@.TK2MSFTNGP09.phx.gbl...
> > I found that my standby server showed itself as a remote server. I
> created
> > linked servers between standby and production; I executed sp_dropserver
> > '(production server name)' and then sp_addserver (standby server name).
> Seems you forgot to specify the ,local option to sp_addserver.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> in message news:eb2bk2FsDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > I have restored my production server master database to the standby
> server.
> > I then ran a series of scripts I found at KB246133:
> > http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246133#4
> >
> > I found that my standby server showed itself as a remote server. I
> created
> > linked servers between standby and production; I executed sp_dropserver
> > '(production server name)' and then sp_addserver (standby server name).
> > SP_dropserver resulted in a message that said that logins existed for
this
> > server. When I look in the sysservers table on the standby server I
have
> > two entries: one for the production server and one for the standby
> server.
> >
> > I've also created a backup device on both the production and standby
> server
> > which are the same. The backup file from production is copied to
standby
> so
> > that the restore can be done on the standby server.
> >
> > My scripts to copy the backup file and restore the database work fine
from
> > QA on the standby server. However, when I try to execute them as part
of
> a
> > job, I get failures. The step which copies the database over says:
> >
> > Could not relay results of procedure 'CopyDatabase_DukeAccount' from
> remote
> > server 'NCNSV1010'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000]
> (Error
> > 7312). The step failed.
> >
> > I am confused why SQL thinks that NCNSV1010 is the remote server since
it
> is
> > actually the local server that this job is executing on. Additionally,
> the
> > backup file IS actually copied over successfully.
> >
> > The step which restores the database to the standby server, which is the
> > local server, from the backup device says:
> >
> > RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error
3013)
> > Cannot open backup device
> > 'd:\mssql\backup\dukeaccount\DukeAccount_Full.bak'. Device error or
device
> > off-line. See the SQL Server error log for more details. [SQLSTATE
42000]
> > (Error 3201). The step failed.
> >
> > However, when I open the backup device to view contents, the just copied
> > backup file is visible in it.
> >
> > Both servers are running W2K SP4, SQL2K SP2 (with security patch)
Standard
> > Edition.
> >
> > Does anyone have any idea what is wrong here? Do I need to drop the
> logins
> > for the (production server name) that exist on the standby server? If I
> do
> > that, will I then have to run the process again to transfer the logins?
I
> > can't seem to drop the production server from the sysservers table if
they
> > have logins. I could manually drop it from the table, but I don't know
> what
> > repurcussions that may have.
> >
> > I need to figure this out because I must automate this process to run
> daily,
> > then the T-Logs many times a day, and I'll be doing this for quite a few
> > databases on these machines.
> >
> > Thanks in advance.
> > Deborah
> >
> >
> >
>

Database Users and Logins

Hi

I can't figure out what the purpose of having seperate users is as I can't actually login to the database using one.

Here is my scenario.

I have a single login called LoginA and I have a database which I want to carve up using schema's. At the database level I need to create a user, associate a login with this user and can set a default schema and specifiy what schemas this USER can access. The login created can access multiple schemas.

So..

I created a database login called loginA.

I created a user for the database called UserA set it's login name to LoginA and

I then created 3 schemas called SchemaA, SchemaB and SchemaC and set their schema owner name to UserA.

I went back to UserA and set their default Schema to SchemaA

How can I login using the new user created as it has no password associated with it. If I login using LoginA then I have no default Schema set becuase the schema is associated with a USER not a LOGIN.

I can understand why you can only have one login account assicated with one user account for each database but I can;t understand why you can specify a user name if you can't use it to login.

Has anyone got any ideas?

Thanks

Logins are used to connect to the server - they provide an identity at the server level. Users provide an identity at the database level. A login will be mapped to a user when he connects to the database.

To have the default schema take effect after login, you should change context to the database where you create UserA (let's say its name is DbA). To automatically do this at login time, you need to additionally change the default database of LoginA to be DbA instead of the master database. A schema is a database entity, so the default schema of a login will be specific to each database to which he will connect.

Thanks
Laurentiu