**Restore error**

Hi
I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L1
should backup and restore (restoring as new db too) it's databes ,so I set
the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in db1
and 'db_owner' in master too.now it can backup the database successfully but
when it wants to restore the db as a new one at the end of progressing the
blue bar of restoring following error appearred :
"Server user 'L1' is not a valid user in database 'db1' ,restore db is
terminated abnormally."
any help would be thakful.Hi
You don't say if these are on the same server! But this may help
http://support.microsoft.com/defaul...kb;en-us;240872
John
"M R" wrote:

> Hi
> I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L
1
> should backup and restore (restoring as new db too) it's databes ,so I set
> the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in d
b1
> and 'db_owner' in master too.now it can backup the database successfully b
ut
> when it wants to restore the db as a new one at the end of progressing the
> blue bar of restoring following error appearred :
> "Server user 'L1' is not a valid user in database 'db1' ,restore db is
> terminated abnormally."
> any help would be thakful.
>
>|||These are on the same server,
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:714B1D9E-A21F-4850-AFE6-8A85137E05DD@.microsoft.com...
> Hi
> You don't say if these are on the same server! But this may help
> http://support.microsoft.com/defaul...kb;en-us;240872
> John
> "M R" wrote:
>
.L1
set
db1
but
the|||Hi
If you change the owner of the database before backing up do you have
the same problem?
You may want to try dropping the user/login and re-create them.
John

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public(selected)
- db_owner(selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.
hi,
u can set a trigger on restorebackup table.
hope this help
from
doller
|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public (selected)
- db_owner (selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.hi,
u can set a trigger on restorebackup table.
hope this help
from
doller|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public (selected)
- db_owner (selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.hi,
u can set a trigger on restorebackup table.
hope this help
from
doller|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

**find related objects**

Hi
I'm working with SQL 2000, how can I find login name L1 is the owner of
which objects() in which dbs?in other word, when I want to delete a login
name in login list an error appears with the context of "you can n't
delete it because it's owns some objects in some DBs".
any help would be grateful
Try this one
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
G
<R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000, how can I find login name L1 is the owner of
> which objects() in which dbs?in other word, when I want to delete a login
> name in login list an error appears with the context of "you can n't
> delete it because it's owns some objects in some DBs".
> any help would be grateful
|||Thanks for your reply, but let me explain my question in another way,
I want a list which shows me L1 had created which objects for example:
L1:
DB1 View1
DB1 table41
DB2View2
DB2View3
DB2Storeprocedure1
then I can go to those databases and I can change the owner of each object
and finally be able to delete th L1 completey.
I would be grateful if you could help me.
On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:

> Try this one
> CREATE FUNCTION dbo.RoleCheckUser
> (
> @.UserName sysname,
> @.RoleName sysname
> )
> RETURNS BIT
> AS
> BEGIN
> DECLARE @.RetVal BIT
> SET @.RetVal = 0
> SELECT @.RetVal = 1
> WHERE EXISTS
> (
> SELECT *
> FROM sysmembers membs
> JOIN sysusers users on membs.memberuid = users.uid
> JOIN sysusers groups on membs.groupuid = groups.uid
> WHERE
> users.name = @.UserName
> AND groups.name = @.RoleName
> )
> RETURN @.RetVal
> END
> GO
> -- Syntax to use the created function
> SELECT dbo.RoleCheckUser('dbo', 'db_owner')
> G
>
> <R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||You can query sysobjects in a database to find all objects
owned by a user - something like:
SELECT name, xtype
FROM sysobjects
WHERE uid = user_id('YourUser')
-Sue
On Mon, 20 Feb 2006 08:18:40 +0330, R-M <R> wrote:
[vbcol=seagreen]
>Thanks for your reply, but let me explain my question in another way,
>I want a list which shows me L1 had created which objects for example:
>L1:
>--
>DB1 View1
>DB1 table41
>DB2View2
>DB2View3
>DB2Storeprocedure1
>then I can go to those databases and I can change the owner of each object
>and finally be able to delete th L1 completey.
>I would be grateful if you could help me.
>On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:

**find related objects**

Hi
I'm working with SQL 2000, how can I find login name L1 is the owner of
which objects() in which dbs?in other word, when I want to delete a login
name in login list an error appears with the context of "you can n't
delete it because it's owns some objects in some DBs".
any help would be gratefulTry this one
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
G
<R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000, how can I find login name L1 is the owner of
> which objects() in which dbs?in other word, when I want to delete a login
> name in login list an error appears with the context of "you can n't
> delete it because it's owns some objects in some DBs".
> any help would be grateful|||Thanks for your reply, but let me explain my question in another way,
I want a list which shows me L1 had created which objects for example:
L1:
--
DB1 View1
DB1 table41
DB2 View2
DB2 View3
DB2 Storeprocedure1
then I can go to those databases and I can change the owner of each object
and finally be able to delete th L1 completey.
I would be grateful if you could help me.
On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:

> Try this one
> CREATE FUNCTION dbo.RoleCheckUser
> (
> @.UserName sysname,
> @.RoleName sysname
> )
> RETURNS BIT
> AS
> BEGIN
> DECLARE @.RetVal BIT
> SET @.RetVal = 0
> SELECT @.RetVal = 1
> WHERE EXISTS
> (
> SELECT *
> FROM sysmembers membs
> JOIN sysusers users on membs.memberuid = users.uid
> JOIN sysusers groups on membs.groupuid = groups.uid
> WHERE
> users.name = @.UserName
> AND groups.name = @.RoleName
> )
> RETURN @.RetVal
> END
> GO
> -- Syntax to use the created function
> SELECT dbo.RoleCheckUser('dbo', 'db_owner')
> G
>
> <R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||You can query sysobjects in a database to find all objects
owned by a user - something like:
SELECT name, xtype
FROM sysobjects
WHERE uid = user_id('YourUser')
-Sue
On Mon, 20 Feb 2006 08:18:40 +0330, R-M <R> wrote:
[vbcol=seagreen]
>Thanks for your reply, but let me explain my question in another way,
>I want a list which shows me L1 had created which objects for example:
>L1:
>--
>DB1 View1
>DB1 table41
>DB2 View2
>DB2 View3
>DB2 Storeprocedure1
>then I can go to those databases and I can change the owner of each object
>and finally be able to delete th L1 completey.
>I would be grateful if you could help me.
>On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:
>

**find related objects**

Hi
I'm working with SQL 2000, how can I find login name L1 is the owner of
which objects() in which dbs?in other word, when I want to delete a login
name in login list an error appears with the context of "you can n't
delete it because it's owns some objects in some DBs".
any help would be gratefulTry this one
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
G
<R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000, how can I find login name L1 is the owner of
> which objects() in which dbs?in other word, when I want to delete a login
> name in login list an error appears with the context of "you can n't
> delete it because it's owns some objects in some DBs".
> any help would be grateful|||Thanks for your reply, but let me explain my question in another way,
I want a list which shows me L1 had created which objects for example:
L1:
--
DB1 View1
DB1 table41
DB2 View2
DB2 View3
DB2 Storeprocedure1
then I can go to those databases and I can change the owner of each object
and finally be able to delete th L1 completey.
I would be grateful if you could help me.
On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:
> Try this one
> CREATE FUNCTION dbo.RoleCheckUser
> (
> @.UserName sysname,
> @.RoleName sysname
> )
> RETURNS BIT
> AS
> BEGIN
> DECLARE @.RetVal BIT
> SET @.RetVal = 0
> SELECT @.RetVal = 1
> WHERE EXISTS
> (
> SELECT *
> FROM sysmembers membs
> JOIN sysusers users on membs.memberuid = users.uid
> JOIN sysusers groups on membs.groupuid = groups.uid
> WHERE
> users.name = @.UserName
> AND groups.name = @.RoleName
> )
> RETURN @.RetVal
> END
> GO
> -- Syntax to use the created function
> SELECT dbo.RoleCheckUser('dbo', 'db_owner')
> G
>
> <R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
>> Hi
>> I'm working with SQL 2000, how can I find login name L1 is the owner of
>> which objects() in which dbs?in other word, when I want to delete a
>> login
>> name in login list an error appears with the context of "you can n't
>> delete it because it's owns some objects in some DBs".
>> any help would be grateful
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||You can query sysobjects in a database to find all objects
owned by a user - something like:
SELECT name, xtype
FROM sysobjects
WHERE uid = user_id('YourUser')
-Sue
On Mon, 20 Feb 2006 08:18:40 +0330, R-M <R> wrote:
>Thanks for your reply, but let me explain my question in another way,
>I want a list which shows me L1 had created which objects for example:
>L1:
>--
>DB1 View1
>DB1 table41
>DB2 View2
>DB2 View3
>DB2 Storeprocedure1
>then I can go to those databases and I can change the owner of each object
>and finally be able to delete th L1 completey.
>I would be grateful if you could help me.
>On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:
>> Try this one
>> CREATE FUNCTION dbo.RoleCheckUser
>> (
>> @.UserName sysname,
>> @.RoleName sysname
>> )
>> RETURNS BIT
>> AS
>> BEGIN
>> DECLARE @.RetVal BIT
>> SET @.RetVal = 0
>> SELECT @.RetVal = 1
>> WHERE EXISTS
>> (
>> SELECT *
>> FROM sysmembers membs
>> JOIN sysusers users on membs.memberuid = users.uid
>> JOIN sysusers groups on membs.groupuid = groups.uid
>> WHERE
>> users.name = @.UserName
>> AND groups.name = @.RoleName
>> )
>> RETURN @.RetVal
>> END
>> GO
>> -- Syntax to use the created function
>> SELECT dbo.RoleCheckUser('dbo', 'db_owner')
>> G
>>
>> <R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
>> Hi
>> I'm working with SQL 2000, how can I find login name L1 is the owner of
>> which objects() in which dbs?in other word, when I want to delete a
>> login
>> name in login list an error appears with the context of "you can n't
>> delete it because it's owns some objects in some DBs".
>> any help would be grateful
>>

**Create Login **

Hi
I want to create a new login "L1" in SQL 2000 and let him to be as L1's
owner and can backup and restore it too,I selected the "public" and
"db_owner" in Enterprise manager as I defined the login name ,but what
should I choose to let him to do "backup & restore db" too?
Any help wolud be thankful.Nothing. Members of the db_owner database role can backup and restore
databases of which they are the owner. If you want to give a login
permissions to backup and restore a database of which they are not the
owner, you can make them a member of the db_backupoperator database role.
Jacco Schalkwijk
SQL Server MVP
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:usUBP3DNFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hi
> I want to create a new login "L1" in SQL 2000 and let him to be as L1's
> owner and can backup and restore it too,I selected the "public" and
> "db_owner" in Enterprise manager as I defined the login name ,but what
> should I choose to let him to do "backup & restore db" too?
> Any help wolud be thankful.
>
>|||I created L1 with db_owner of DB1 ,but when I changed my enterprise manger
by logining through L1 and wanted to restore DB1's backup as DB2 following
error appearred:
"Create databae permission denied in databade master, restore database is
terminated abnormally."
so,I think it's not enough just to check "public" and "db_owner" for L1 in
DB1,why?
can anybody help me?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OZ9GDHENFHA.2384@.tk2msftngp13.phx.gbl...
> Nothing. Members of the db_owner database role can backup and restore
> databases of which they are the owner. If you want to give a login
> permissions to backup and restore a database of which they are not the
> owner, you can make them a member of the db_backupoperator database role.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "maryam rezvani" <rezvani@.parskhazar.net> wrote in message
> news:usUBP3DNFHA.3336@.TK2MSFTNGP10.phx.gbl...
>|||If a login is a member of the db_owner role, it has the permissions to
backup and restore that database. What you want to do is create a new
database, and restore the original database over that. For that the login
needs to be a member of the dbcreator fixed server role or you have to grant
the permission with GRANT CREATE DATABASE TO L1. The latter gives less
permissions, as the login won't be allowed to drop or alter other databases
than the one he has created. Creating a database automatically as the
creating login to the db_owner role.
Jacco Schalkwijk
SQL Server MVP
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:OF0Ik2ENFHA.2704@.TK2MSFTNGP15.phx.gbl...
>I created L1 with db_owner of DB1 ,but when I changed my enterprise manger
> by logining through L1 and wanted to restore DB1's backup as DB2 following
> error appearred:
> "Create databae permission denied in databade master, restore database is
> terminated abnormally."
> so,I think it's not enough just to check "public" and "db_owner" for L1 in
> DB1,why?
> can anybody help me?
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:OZ9GDHENFHA.2384@.tk2msftngp13.phx.gbl...
>|||Thanks I tried as you said and I've got following error at the end of
progression of a blue bar(showing restore action)
"Server user L1 is not a valid user in database DB2 ,restore database is
terminated abnormally."
as a result the database restored but L1 has no access to DB2!!!!
what should I do?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:#QvAMBFNFHA.2704@.TK2MSFTNGP15.phx.gbl...
> If a login is a member of the db_owner role, it has the permissions to
> backup and restore that database. What you want to do is create a new
> database, and restore the original database over that. For that the login
> needs to be a member of the dbcreator fixed server role or you have to
grant
> the permission with GRANT CREATE DATABASE TO L1. The latter gives less
> permissions, as the login won't be allowed to drop or alter other
databases
> than the one he has created. Creating a database automatically as the
> creating login to the db_owner role.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "maryam rezvani" <rezvani@.parskhazar.net> wrote in message
> news:OF0Ik2ENFHA.2704@.TK2MSFTNGP15.phx.gbl...
manger
following
is
in
role.
what
>|||Can you give the _exact_ steps you take (which permissions you have assigned
to L1, which database you restore to where, etc), and the _exact_ error
message you get? (There is no such thing as a "server user" in Microsoft SQL
Server)
Jacco Schalkwijk
SQL Server MVP
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:e8ags$ONFHA.3560@.TK2MSFTNGP14.phx.gbl...
> Thanks I tried as you said and I've got following error at the end of
> progression of a blue bar(showing restore action)
> "Server user L1 is not a valid user in database DB2 ,restore database is
> terminated abnormally."
> as a result the database restored but L1 has no access to DB2!!!!
> what should I do?
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:#QvAMBFNFHA.2704@.TK2MSFTNGP15.phx.gbl...
> grant
> databases
> manger
> following
> is
> in
> role.
> what
>|||Thanks
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:euWLXiPNFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Can you give the _exact_ steps you take (which permissions you have
assigned
> to L1, which database you restore to where, etc), and the _exact_ error
> message you get? (There is no such thing as a "server user" in Microsoft
SQL
> Server)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "maryam rezvani" <rezvani@.parskhazar.net> wrote in message
> news:e8ags$ONFHA.3560@.TK2MSFTNGP14.phx.gbl...
login
database
L1
<jacco.please.reply@.to.newsgroups.mvps.org.invalid>
restore
the
as
and
>