Restore SQL server, Login failure

I restored a DB to another server and then i used the stored procedures

exec sp_addlogin on the master DB and the exec sp_change_users_login on the DB that I have restored. When i try to run a sproc I am getting

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I have used windows authentication to login to connect to the server using sql management studio

Am i missing something. Thankyou for the help

How do you call the stored procedure and what does the stored procedure do?

Thanks
Laurentiu

Restore problem

Since i need to restore my database to another machine, I am now encountering a problem with my db.

Because some login does not exist on the new machine and I need to re-crerate the login in it. However, i found an error message 'User ?? already exist'

Is it possible that i could drop my existing user but some of them are objects/db owner.

many thanks!You got the same problem as me.. see http://dbforums.com/t547764.html|||Originally posted by Nigorr
You got the same problem as me.. see http://dbforums.com/t547764.html

I just find a solution and it fixed my situation and hope it could help you.

I used the sp_change_users_login to associate the login and user.|||thanks, why didn't they(MS) provide a way to do this in enterprise manager?

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlTechnically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlDo the new logins have permission to access the files on
the server ?
J
>--Original Message--
>Hello All,
>I have run into a situation that a 3rd party backup and
restore solution is
>requiring sa account to backup and restore databases in
my SQL 2K/SP3a
>server.
>I have created a login that is a member of
db_backupoperator and db_onwer
>database roles and database_creators server role. Isn't
that enough? does
>it absolutely need sa privilege? Please enlighten me if
you have run into
>the same scenario.
>Thanks,
>sqlgirl
>
>.
>|||Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
--
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirl
Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>
|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

restore master

Hi
I want to restore master db(first) in another server
which has sql server 2000 before restoring user dbs,
to locate login and ...
but following error happended,what's wrong?
"resore database must be used in single user mode
when restoring master db"
and I found there's only one login name called "sa"
as system administrator,
but when I expand the user branch in master database
I found "sa" and "guest" there,
and when I tried to drop guest following error happened too:
"cannot drop the guest user from master or tempdb"
1 - does not restoring master related to guest?
2 - anyway,how can I drop guest?
any help would be greatly thanked.
thanks,
but can I just script my login definition and
relation between them and he users and run it in destination
instead of restoring master database?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||and how can I understand that I
"start SQL service in single user mode"?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||Hi,
Login can be copied from source server to destination server. But Master
database stores the other details like:-
1. Configuration parameters
2. Server Names ,..
So after creating the logins you have to manually sync. that as well.
See the below link to transfer logins from one server to another server with
the same password.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayeffn6hqligo@.msnews.microsoft.com...
> thanks,
> but can I just script my login definition and
> relation between them and he users and run it in destination
> instead of restoring master database?
>
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
|||Hi,
Execute the below command from Query Analyzer:-
select serverproperty('IsSingleUser')
If the value returned is "1" then the server is in single user mode.
If the value returned is "0" then the server is in multi user mode.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayehds6hqligo@.msnews.microsoft.com...
> and how can I understand that I
> "start SQL service in single user mode"?
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>