db_owner problem

I recently registered my hosted database server on my pc. When I ran aspnet_regsql.exe the table created have dbo as schema, but when I create a news table throught stored procedure it has my username as schema.
When I buildt my site on my pc all tables have dbo as schema.
How do I make all tables on my hosted server db have same schema?

Thanks

The very Newbie

The schema of new objects is determined by the default schema of current database user. So just make sure all database users have the same default schema (for example). You can easily do this in Management Studio.|||

Thanks for your reply Iori_Jay.

Where and how in Management Studio do I make sure that default schema is MyUsername(current database user) instead of getting schema "dbo" for some tables and schema "MyUsername" for other tables?
When I ran aspnet_regsql.exe all tables and procedures have schema dbo allthough I use my username to create them.

Thanks again

The Very Newbie
We learn from masters

|||You can check the defaut schema of a database user by checkin the Properties of the user under SQL instance->Databases->your database->Security->Users.|||

Thanks again Iori_Jay,

In the security folder of my database there are 2 users: dbo and myself. I need to transfer schema, but don´t know how to make it work. My hosted server uses sql server 2000.

Thanks

|||

In SQL 2000 SHCEMA is not a seperated object, it equals to the owner of the object. So you need to change to owner of the object using such command:

EXEC sp_changeobjectowner 'myself.tbl1', 'dbo'

|||

Thanks a lotIori_Jay.

It works fine.

sql

db_owner

Hello. What would be the best role assignment for a temporary consultant to
access one of our user created databases on our sql2k server to allow this
person to develop freely but only within this database and not any of our
other databases including the obvious system databases? Would it be ok to
give this consultant a windows domain login and assign it as a db_owner or
would assigning a combination of the other system roles within this db be
better?
Thanks in advance.Yes, db_owner will pretty much give the consultant full control but only
within that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
> Hello. What would be the best role assignment for a temporary consultant
> to access one of our user created databases on our sql2k server to allow
> this person to develop freely but only within this database and not any of
> our other databases including the obvious system databases? Would it be
> ok to give this consultant a windows domain login and assign it as a
> db_owner or would assigning a combination of the other system roles within
> this db be better?
> Thanks in advance.
>|||Thanks for your speedy confirmation Dan. Much appreciated :-)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||I just created a Windows domain user something like 'DomainAUser\JoeTest'
and assigned him to the db_owner role to one of our user created databases.
When I set up an odbc or .udl using this Windows domain user credentials how
come this user is able to see our system databases and a couple other in the
default database drop down box which kind of is concerning.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
> Yes, db_owner will pretty much give the consultant full control but only
> within that database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:e5sNUtgNIHA.5860@.TK2MSFTNGP04.phx.gbl...
>|||All logins can access databases with the guest user enabled. This includes
sample and system databases but permissions in the system databases are
minimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>I just created a Windows domain user something like 'DomainAUser\JoeTest'
>and assigned him to the db_owner role to one of our user created databases.
>When I set up an odbc or .udl using this Windows domain user credentials
>how come this user is able to see our system databases and a couple other
>in the default database drop down box which kind of is concerning.
> Thanks.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:26708F07-A4D5-41EB-BDE9-169A201295A4@.microsoft.com...
>|||Is there a way to disable all of the 'guest' accounts in all of the system
and user databases since I noticed this Microsoft article recommends not to
remove the 'guest' account
http://support.microsoft.com/default.aspx/kb/315523
Thanks Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
> All logins can access databases with the guest user enabled. This
> includes sample and system databases but permissions in the system
> databases are minimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:O0%23a8Y5NIHA.3852@.TK2MSFTNGP06.phx.gbl...
>|||> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
There is only one guest user ("guest") in the system databases, which is
required for proper operation. The guest user inherits only minimal
permissions from the public role so permissions are quite limited in master
and tempdb.
You might consider revoking public execute permissions on the msdb database
sp_add_job and sp_add_dtspackage if you want to prevent non-sysadmins from
creating jobs or saving DTS packages in msdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Is there a way to disable all of the 'guest' accounts in all of the system
> and user databases since I noticed this Microsoft article recommends not
> to remove the 'guest' account
> http://support.microsoft.com/default.aspx/kb/315523
> Thanks Dan.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:740E18FB-57E3-4F74-84B4-8327509943F9@.microsoft.com...
>|||Interesting. Thanks Dan, much appreciated. Take cares.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4266998A-E270-473E-9D98-3A44908D1891@.microsoft.com...
> There is only one guest user ("guest") in the system databases, which is
> required for proper operation. The guest user inherits only minimal
> permissions from the public role so permissions are quite limited in
> master and tempdb.
> You might consider revoking public execute permissions on the msdb
> database sp_add_job and sp_add_dtspackage if you want to prevent
> non-sysadmins from creating jobs or saving DTS packages in msdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:Ozqp05DOIHA.1208@.TK2MSFTNGP05.phx.gbl...
>sql

db_denydatareader Role

Hi!,
I created a user in a database and added him to db_denydatareader role
but still when he logs on using his user name he can browse all the
records. Where as the definition says that he "Cannot select any data
from any user table in the database". Can anyone help me implement this
on my database.
Thanks in advanceIs the user a member of the sysadmin fixed server role, either directly or
via Windows group membership? In that case, the user is 'dbo' in all
databases and permissions are not checked.
Hope this helps.
Dan Guzman
SQL Server MVP
<sajid_yusuf@.yahoo.com> wrote in message
news:1125935568.459784.70100@.g49g2000cwa.googlegroups.com...
> Hi!,
> I created a user in a database and added him to db_denydatareader role
> but still when he logs on using his user name he can browse all the
> records. Where as the definition says that he "Cannot select any data
> from any user table in the database". Can anyone help me implement this
> on my database.
> Thanks in advance
>

db_backupoperator question

I have assigned an user id to a role of db_backupoperator. But after I
created a scheduled job to do backup weekly, it won't run at all. No error
message in the event application. So I go to do the backup manually. I get
an error message when I click the "..." button to specify the backup file
location as following:
Microsoft SQL-DMO (ODBC SQLState: 42000)
"Error 229: Execute permission denied on object 'xp_availablemedia',
database 'master', owner 'dbo'."
Please help. Thanks.Hi,
Its seems your login account does not have the permissions required to
create a backup
device in hard disk. Please consult your system administrator or database
administrator
to obtain the required permissions to write in to the hard disk (Write
permission on the folder you craete the backup file).
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> I have assigned an user id to a role of db_backupoperator. But after I
> created a scheduled job to do backup weekly, it won't run at all. No error
> message in the event application. So I go to do the backup manually. I
get
> an error message when I click the "..." button to specify the backup file
> location as following:
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> "Error 229: Execute permission denied on object 'xp_availablemedia',
> database 'master', owner 'dbo'."
> Please help. Thanks.
>|||Thanks, Hari. I have created the user id previously and didn't assign a
local win 2k user id to it. May be this is the problem. Now I just created
a local win 2k user id with the access right. But I don't want to delete the
sql user id and re-create it again? How can I map the current sql 2k user id
with the local win 2k user? Please let me know. Thanks.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> Hi,
> Its seems your login account does not have the permissions required to
> create a backup
> device in hard disk. Please consult your system administrator or database
> administrator
> to obtain the required permissions to write in to the hard disk (Write
> permission on the folder you craete the backup file).
> Thanks
> Hari
> MCDBA
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > I have assigned an user id to a role of db_backupoperator. But after I
> > created a scheduled job to do backup weekly, it won't run at all. No
error
> > message in the event application. So I go to do the backup manually. I
> get
> > an error message when I click the "..." button to specify the backup
file
> > location as following:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)
> >
> > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > database 'master', owner 'dbo'."
> >
> > Please help. Thanks.
> >
> >
>|||Hi,
The user in which you start the SQL Server and SQL Agent service should have
the permission.
Thanks
Hari
MCDBA
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> Thanks, Hari. I have created the user id previously and didn't assign a
> local win 2k user id to it. May be this is the problem. Now I just
created
> a local win 2k user id with the access right. But I don't want to delete
the
> sql user id and re-create it again? How can I map the current sql 2k user
id
> with the local win 2k user? Please let me know. Thanks.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Its seems your login account does not have the permissions required to
> > create a backup
> > device in hard disk. Please consult your system administrator or
database
> > administrator
> > to obtain the required permissions to write in to the hard disk (Write
> > permission on the folder you craete the backup file).
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > I have assigned an user id to a role of db_backupoperator. But after I
> > > created a scheduled job to do backup weekly, it won't run at all. No
> error
> > > message in the event application. So I go to do the backup manually.
I
> > get
> > > an error message when I click the "..." button to specify the backup
> file
> > > location as following:
> > >
> > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > >
> > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > database 'master', owner 'dbo'."
> > >
> > > Please help. Thanks.
> > >
> > >
> >
> >
>|||Hari,
I found out that both services are started with local system
accounts. Does that mean whoever shut down and re-start the service is the
local system accounts? Or it mean something else? Do I need to re-start both
services with the user id I used to do backup?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23601RbaMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The user in which you start the SQL Server and SQL Agent service should
have
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Hari. I have created the user id previously and didn't assign a
> > local win 2k user id to it. May be this is the problem. Now I just
> created
> > a local win 2k user id with the access right. But I don't want to delete
> the
> > sql user id and re-create it again? How can I map the current sql 2k
user
> id
> > with the local win 2k user? Please let me know. Thanks.
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:uWGed8ZMEHA.808@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Its seems your login account does not have the permissions required to
> > > create a backup
> > > device in hard disk. Please consult your system administrator or
> database
> > > administrator
> > > to obtain the required permissions to write in to the hard disk (Write
> > > permission on the folder you craete the backup file).
> > >
> > > Thanks
> > > Hari
> > > MCDBA
> > >
> > > "Eric Clapton" <no_spam@.bk.com> wrote in message
> > > news:#bQGXdZMEHA.2628@.TK2MSFTNGP12.phx.gbl...
> > > > I have assigned an user id to a role of db_backupoperator. But after
I
> > > > created a scheduled job to do backup weekly, it won't run at all. No
> > error
> > > > message in the event application. So I go to do the backup
manually.
> I
> > > get
> > > > an error message when I click the "..." button to specify the backup
> > file
> > > > location as following:
> > > >
> > > > Microsoft SQL-DMO (ODBC SQLState: 42000)
> > > >
> > > > "Error 229: Execute permission denied on object 'xp_availablemedia',
> > > > database 'master', owner 'dbo'."
> > > >
> > > > Please help. Thanks.
> > > >
> > > >
> > >
> > >
> >
> >
>

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!
Which manual and which DBCC commands does it say should work?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!
|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> database
> --
>
|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!
|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
----
--
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!Which manual and which DBCC commands does it say should work?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> database
> --
>|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:

>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for databas
e
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.sql

db_backupoperator cannot issue dbcc commands

Not sure...but the manual says that a user with the db_backupoperator role
should be able to issue the dbcc commands. So I created a user, added the
role, and he cannot issue any dbcc commands. I even logged out and logged
back in without any success.
This is what I get:
1> dbcc checkdb(yada)
2> go
Msg 7983, Level 14, State 8, Server YADA, Line 1
User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
'yada'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I found this proc to check the role, and dbcc's aren't listed here...but I
am not sure if they would be...
1> sp_dbfixedrolepermission db_backupoperator
2> go
DbFixedRole
Permission
----
-- --
db_backupoperator
BACKUP DATABASE
db_backupoperator
BACKUP LOG
db_backupoperator
CHECKPOINT
I then granted the user dbo...and of course then he can do the dbcc's.
Thanks for any info!Which manual and which DBCC commands does it say should work?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
> Not sure...but the manual says that a user with the db_backupoperator role
> should be able to issue the dbcc commands. So I created a user, added the
> role, and he cannot issue any dbcc commands. I even logged out and logged
> back in without any success.
> This is what I get:
> 1> dbcc checkdb(yada)
> 2> go
> Msg 7983, Level 14, State 8, Server YADA, Line 1
> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
database
> 'yada'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I found this proc to check the role, and dbcc's aren't listed here...but I
> am not sure if they would be...
>
> 1> sp_dbfixedrolepermission db_backupoperator
> 2> go
> DbFixedRole
> Permission
> ----
--
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
> I then granted the user dbo...and of course then he can do the dbcc's.
> Thanks for any info!|||In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
can be run but does not list which ones.
--
Andrew J. Kelly SQL MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ud5VUY1yEHA.3552@.TK2MSFTNGP10.phx.gbl...
> Which manual and which DBCC commands does it say should work?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:9A83EC25-BECC-4E86-B615-858EF58BF661@.microsoft.com...
>> Not sure...but the manual says that a user with the db_backupoperator
>> role
>> should be able to issue the dbcc commands. So I created a user, added
>> the
>> role, and he cannot issue any dbcc commands. I even logged out and
>> logged
>> back in without any success.
>> This is what I get:
>> 1> dbcc checkdb(yada)
>> 2> go
>> Msg 7983, Level 14, State 8, Server YADA, Line 1
>> User 'dbcc_user01' does not have permission to run DBCC CHECKDB for
> database
>> 'yada'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> I found this proc to check the role, and dbcc's aren't listed here...but
>> I
>> am not sure if they would be...
>>
>> 1> sp_dbfixedrolepermission db_backupoperator
>> 2> go
>> DbFixedRole
>> Permission
>> ----
> --
>> -- --
>> db_backupoperator
>> BACKUP DATABASE
>> db_backupoperator
>> BACKUP LOG
>> db_backupoperator
>> CHECKPOINT
>>
>> I then granted the user dbo...and of course then he can do the dbcc's.
>> Thanks for any info!
>|||Which manual says that? It's not correct.
In your example of executing dbcc checkdb, if you look up
the permissions section of this command in books online, it
will indicate that only sysadmins and db_owners can execute
this.
The fixed database role db_backupoperator can issue backup
statements for the current database. Not much more with that
role.
-Sue
On Mon, 15 Nov 2004 09:28:02 -0800, "Chris"
<Chris@.discussions.microsoft.com> wrote:
>Not sure...but the manual says that a user with the db_backupoperator role
>should be able to issue the dbcc commands. So I created a user, added the
>role, and he cannot issue any dbcc commands. I even logged out and logged
>back in without any success.
>This is what I get:
>1> dbcc checkdb(yada)
>2> go
>Msg 7983, Level 14, State 8, Server YADA, Line 1
>User 'dbcc_user01' does not have permission to run DBCC CHECKDB for database
>'yada'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>
>I found this proc to check the role, and dbcc's aren't listed here...but I
>am not sure if they would be...
>
>1> sp_dbfixedrolepermission db_backupoperator
>2> go
> DbFixedRole
> Permission
>----
> -- --
> db_backupoperator
> BACKUP DATABASE
> db_backupoperator
> BACKUP LOG
> db_backupoperator
> CHECKPOINT
>
>I then granted the user dbo...and of course then he can do the dbcc's.
>Thanks for any info!|||I just found the page you are referring to - it's not too
well documented is it.
I think the only dbcc that role can execute is checkcatalog
- as well as checkpoint and backup statements. .
-Sue
On Mon, 15 Nov 2004 16:09:15 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>In BOL 2000 under roles (roles-SQL Server, overview) it states that DBCC's
>can be run but does not list which ones.

DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both|||Thank you both

DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
> have no idea where or how to do this, or even if this is the right thing to
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both

DB Role and DBO

Hello, I've created a database role for the application user. They are usin
g
the dbo schema, so when I try to add dbo to the role, it won't let me. Why
is this? And does this mean we would need to create some other schema
besides dbo?
Thanks,
MitchHi
DBO is just "special" user. My guess you are using SQL Server 2005 ,right?
In SQL Server 2005 user owner and schema are separeted. You will have to
read BOL on the subject. SCHEMA is juct a container for many objects for
security purposes
"Mitch" <Mitch@.discussions.microsoft.com> wrote in message
news:D29C96D0-AEFA-4263-BA19-D5DE631F2CA9@.microsoft.com...
> Hello, I've created a database role for the application user. They are
> using
> the dbo schema, so when I try to add dbo to the role, it won't let me.
> Why
> is this? And does this mean we would need to create some other schema
> besides dbo?
> Thanks,
> Mitch

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciated
Regarding the corruption:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or already have database files
with same size as you had when you took the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com. ..
> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level where
> my restore was succesfull. i want to know what i did wrong & why finally it
> succeed after increasing size. any help will be highly appreciated
|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:

> Regarding the corruption:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or already have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com. ..
>
|||No. But why did you create the database before the restore. The restore operation does a safety
check. If the database which you are to restore into exists, but the file layout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can create the database for
you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:

> No. But why did you create the database before the restore. The restore operation does a safety
> check. If the database which you are to restore into exists, but the file layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process can create the database for
> you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
>

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciatedRegarding the corruption:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or already have database files
with same size as you had when you took the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level where
> my restore was succesfull. i want to know what i did wrong & why finally it
> succeed after increasing size. any help will be highly appreciated|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:
> Regarding the corruption:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or already have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> > getting message SUSPECT.
> >
> > So i created new DB with initial size of 5GB to try to restore 50GB the
> > original DB & it failed.
> >
> > Finally i was able to restore, by increasing size to substantial level where
> > my restore was succesfull. i want to know what i did wrong & why finally it
> > succeed after increasing size. any help will be highly appreciated
>|||No. But why did you create the database before the restore. The restore operation does a safety
check. If the database which you are to restore into exists, but the file layout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can create the database for
you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
the restore command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
>> Regarding the corruption:
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> As for restore:
>> You cannot restore into a smaller database. SQL Server need to create or already have database
>> files
>> with same size as you had when you took the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
>> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
>> > getting message SUSPECT.
>> >
>> > So i created new DB with initial size of 5GB to try to restore 50GB the
>> > original DB & it failed.
>> >
>> > Finally i was able to restore, by increasing size to substantial level where
>> > my restore was succesfull. i want to know what i did wrong & why finally it
>> > succeed after increasing size. any help will be highly appreciated
>>|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:
> No. But why did you create the database before the restore. The restore operation does a safety
> check. If the database which you are to restore into exists, but the file layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process can create the database for
> you when you perform the restore operation, it doesn't have to exist. Or, use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
> > Hi Tibor,
> >
> > Will an autogrow option in 5GB DB work in this scenario?
> >
> > Thanks,
> > Sree
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Regarding the corruption:
> >> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> >>
> >> As for restore:
> >> You cannot restore into a smaller database. SQL Server need to create or already have database
> >> files
> >> with same size as you had when you took the backup.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ureader.com...
> >> > In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> >> > getting message SUSPECT.
> >> >
> >> > So i created new DB with initial size of 5GB to try to restore 50GB the
> >> > original DB & it failed.
> >> >
> >> > Finally i was able to restore, by increasing size to substantial level where
> >> > my restore was succesfull. i want to know what i did wrong & why finally it
> >> > succeed after increasing size. any help will be highly appreciated
> >>
> >>
>

DB Restore FAIL

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
getting message SUSPECT.
So i created new DB with initial size of 5GB to try to restore 50GB the
original DB & it failed.
Finally i was able to restore, by increasing size to substantial level where
my restore was succesfull. i want to know what i did wrong & why finally it
succeed after increasing size. any help will be highly appreciatedRegarding the corruption:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
As for restore:
You cannot restore into a smaller database. SQL Server need to create or alr
eady have database files
with same size as you had when you took the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94be4@.ur
eader.com...

> In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted
> getting message SUSPECT.
> So i created new DB with initial size of 5GB to try to restore 50GB the
> original DB & it failed.
> Finally i was able to restore, by increasing size to substantial level whe
re
> my restore was succesfull. i want to know what i did wrong & why finally i
t
> succeed after increasing size. any help will be highly appreciated|||Hi Tibor,
Will an autogrow option in 5GB DB work in this scenario?
Thanks,
Sree
"Tibor Karaszi" wrote:

> Regarding the corruption:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> As for restore:
> You cannot restore into a smaller database. SQL Server need to create or a
lready have database files
> with same size as you had when you took the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jay" <sqldba@.abc.com> wrote in message news:fa00ab1c82794850b98da6a02af94
be4@.ureader.com...
>|||No. But why did you create the database before the restore. The restore oper
ation does a safety
check. If the database which you are to restore into exists, but the file la
yout doesn't match, you
get an error. This is so you don't do a mistake. But the restore process can
create the database for
you when you perform the restore operation, it doesn't have to exist. Or, us
e the REPLACE option of
the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> Will an autogrow option in 5GB DB work in this scenario?
> Thanks,
> Sree
>
> "Tibor Karaszi" wrote:
>|||Ya thats correct, but still didnt try this option yet thats why.
Thanks, Tibor.
"Tibor Karaszi" wrote:

> No. But why did you create the database before the restore. The restore op
eration does a safety
> check. If the database which you are to restore into exists, but the file
layout doesn't match, you
> get an error. This is so you don't do a mistake. But the restore process c
an create the database for
> you when you perform the restore operation, it doesn't have to exist. Or,
use the REPLACE option of
> the restore command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:2C05DE26-DD1A-429F-A8CF-8997481C4B81@.microsoft.com...
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave this
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> > Hi
> >
> > I have a query in relation to db permissions. I created a local Windows
> > login and then created a SQL Server login using the same account. I gave
> > this
> > login access just to one database and assigned him to the
> > db_denydatawriter
> > role in that databse. This works fine as I just want them to have SELECT
> > access only on that db. The problem is that that login can still do
> > SELECT,
> > UPDATE, DELETE and INSERT on other db's within the server.
> >
> > I thought that if you didn't select Permit against a database in the
> > Database Access screen that the user would not have access to the db?
> >
> > What is the best way to prevent this user from accessing any db's apart
> > from
> > the one which I specified. I could obviously go through each db and
> > specify
> > db_denydatawriter but on servers with lots of db's that is not really
> > practical.
> >
> > Thanks in advance
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave this
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db permissions

Hi
I have a query in relation to db permissions. I created a local Windows
login and then created a SQL Server login using the same account. I gave thi
s
login access just to one database and assigned him to the db_denydatawriter
role in that databse. This works fine as I just want them to have SELECT
access only on that db. The problem is that that login can still do SELECT,
UPDATE, DELETE and INSERT on other db's within the server.
I thought that if you didn't select Permit against a database in the
Database Access screen that the user would not have access to the db?
What is the best way to prevent this user from accessing any db's apart from
the one which I specified. I could obviously go through each db and specify
db_denydatawriter but on servers with lots of db's that is not really
practical.
Thanks in advance"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Hi
> I have a query in relation to db permissions. I created a local Windows
> login and then created a SQL Server login using the same account. I gave
> this
> login access just to one database and assigned him to the
> db_denydatawriter
> role in that databse. This works fine as I just want them to have SELECT
> access only on that db. The problem is that that login can still do
> SELECT,
> UPDATE, DELETE and INSERT on other db's within the server.
> I thought that if you didn't select Permit against a database in the
> Database Access screen that the user would not have access to the db?
> What is the best way to prevent this user from accessing any db's apart
> from
> the one which I specified. I could obviously go through each db and
> specify
> db_denydatawriter but on servers with lots of db's that is not really
> practical.
> Thanks in advance
Check those other DB's for a guest account. If you don't have explicit
access to a database and it has a guest account, then your login will use
that guest account.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick
I'll do that
"Rick Sawtell" wrote:

> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:3DA9DC0E-FEA9-48DD-A7B6-CBC6FAAA0D92@.microsoft.com...
> Check those other DB's for a guest account. If you don't have explicit
> access to a database and it has a guest account, then your login will use
> that guest account.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

db owner

I created a new db under sql 2000. I created an ASP page to access the db, I
can access the database via the asp from my machine but others cannot. They
get a NT logon error.
I noticed that the db I created the owner has my network logon id and domain
name.
example: db owner: domain/logonid
and all the other db system generated have owner od SA.
What should the owner name be?
If its SA how can i change that?
If not, how can I allow others to access the DB via the asp page?
thanksMike
See sp_changeobjectowner in books on line. Generally it is
best for all database object to be owned by dbo.
Regards
John|||use sp_changedbowner
--
BR,
Mark Broadbent mcse+i, mcdba
_________________________
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:OBlBsQfUDHA.212@.TK2MSFTNGP12.phx.gbl...
> I created a new db under sql 2000. I created an ASP page to access the db,
I
> can access the database via the asp from my machine but others cannot.
They
> get a NT logon error.
> I noticed that the db I created the owner has my network logon id and
domain
> name.
> example: db owner: domain/logonid
> and all the other db system generated have owner od SA.
> What should the owner name be?
> If its SA how can i change that?
> If not, how can I allow others to access the DB via the asp page?
>
> thanks
>|||Mike
Oops read the post without reading it properly, Mark is of
course right. Still now you know what to do if you do the
same with an object.
Regards
John|||I changed the db owner, but I still can't access the db via asp from a
remote machine.
Do i have to change IIS settings or something to be able to do this?
Its currenlty set up to allow anonymous access, but i'm not allowing IIS to
control the password, should I?
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0ff201c351f9$3d40a860$a001280a@.phx.gbl...
> Mike
> Oops read the post without reading it properly, Mark is of
> course right. Still now you know what to do if you do the
> same with an object.
> Regards
> John|||Hi Mike, does the account that you are using to connect to the remote data
source (specified in your asp pages) have access permissions to the
database.
If you are using integrated security when forming the connection I would
expect that the account used would be the iis service one (in this case
would need access), I would be very suprised if security of each user was
delegated to the sql server (a-la kerberos style).
Also look at your DSN, cos if you have configured it to connect to the data
source using sql server authentication then I would expect that this user is
the one which would need the db rights.
Its been a while since I've done this but you should get there in the end
thru trial and error. The first thing you need to get working is getting the
asp pages to run (and connect) successfully from the web server itself. Then
try getting a remote client to work (connecting and running the asp).
First
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:%23nhcm2fUDHA.2316@.TK2MSFTNGP09.phx.gbl...
> I changed the db owner, but I still can't access the db via asp from a
> remote machine.
> Do i have to change IIS settings or something to be able to do this?
> Its currenlty set up to allow anonymous access, but i'm not allowing IIS
to
> control the password, should I?
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0ff201c351f9$3d40a860$a001280a@.phx.gbl...
> > Mike
> >
> > Oops read the post without reading it properly, Mark is of
> > course right. Still now you know what to do if you do the
> > same with an object.
> >
> > Regards
> >
> > John
>

DB name changes daily

Hi,
I am trying to write SQL automation that runs SQL scripts against the daily drop of the database. Each day, a new DB is created using the format (Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name might be TEST12345 and tomorrow wo
uld be Test12346. I have a script that creates the dbname such as Test12345 and when I run it, I get the expected result, but when I try this statement
Use @.dbname
I get an error.
Is there a way to introduce a variable with a Use statement so that I can automatically open the latest db?
Thanks
Hi,
'USE' statement will change the DB context for the current connection and
then go back to where it was before (Default datbase context).
Doing this in an exec will change the context until the exec is completed
and then go back to default database context.
This is small example to show how to do it. This example uses the Northwind
database.
This database has a stored procedure 'emp_list' . From the master database
and execute the following:-
set quoted_identifier off
declare @.dbname varchar(15), @.procname varchar(25)
declare @.startdate varchar(10) , @.enddate varchar(10)
set @.dbname = 'northwind'
set @.procname = '[emp_list]'
exec ('USE ' + @.dbname + ' execute ' + @.procname )
the last statement translates to:
exec ( USE northwind execute [emp_list])
But after the execution again the context will go to the default database.
Note:
The best option rather than using the above is:- (SP_DEFAULTDB)
1. Have a common login
2. Every time after creating the new database use the sp_defaultdb procedure
to change the default db of the login
3. After this when ever that user logins the database context will be the
new database and there is nolt required to give USE XXXXX
Thanks
Hari
MCDBA
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:F9E1B6D0-5378-45AD-8A2C-6DF194B86B0A@.microsoft.com...
> Hi,
> I am trying to write SQL automation that runs SQL scripts against the
daily drop of the database. Each day, a new DB is created using the format
(Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name
might be TEST12345 and tomorrow would be Test12346. I have a script that
creates the dbname such as Test12345 and when I run it, I get the expected
result, but when I try this statement
> Use @.dbname
> I get an error.
> Is there a way to introduce a variable with a Use statement so that I can
automatically open the latest db?
> Thanks

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyan
Hi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touched
> this db. We have about 5 DBA with full previlage. Suddlenly when i checked
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find when
> owner ship is changed.?
> thanks
> kalyan

db name changed

about 1 week back, I created a DB dbo as owner. After this i never touched
this db. We have about 5 DBA with full previlage. Suddlenly when i checked
today owner name is changed to my login. I am 100 % sure i never touched.
Is there any way to find, when name is changed? i checked log file i did n't
get any information.
this is very urgen issue, could any one of you guys help me how to find when
owner ship is changed.?
thanks
kalyanHi
That information is not logged. The only way you could do the change is to
use sp_changedbowner.
You can not create a DB with dbo as owner. An owner of a DB can only be
someone who is is master.dbo.syslogins. dbo is a role inside a DB.
When you created the DB, your login become owner for it, and unless you did
it when logged in as sa, or ran sp_changedbowner, it remains in your name.
Regards
Mike
"Kalyan" wrote:

> about 1 week back, I created a DB dbo as owner. After this i never touche
d
> this db. We have about 5 DBA with full previlage. Suddlenly when i checke
d
> today owner name is changed to my login. I am 100 % sure i never touched.
> Is there any way to find, when name is changed? i checked log file i did n
't
> get any information.
> this is very urgen issue, could any one of you guys help me how to find wh
en
> owner ship is changed.?
> thanks
> kalyan