Db2, Nolock

Hi guys,

I am a new user for DB2.

In DB2 V8, i have a table with some no of columns.

table test
col1
col2
col3
col4

Now i want to issue a query like this.

select col1 from test with(NOLOCK) where col1 = <some value>

when i am executing this query on that time i am getting a error message like this...

SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811

Anyone can help me?

i tried to find out.

I did the same thing with SQL server 2000.
I am not facing any problem there.

Thanks & Regards,
MuthuYou should post it on DB2 forum and this is related to SQL Server.

DB2 Linked Server

I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?What OLE DB Provider are you using? I have had no luck with the standard ones provided by IBM; I have heard of others who have used 3rd party drivers with great success.

Regards,

hmscott

Originally posted by vmlal
I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?|||I'm using the OLE DB Provider as follows:
srvproduct: DB2OLEDB
providername: MSDASQL

I have installed the right drivers for DB2 etc.

What other 3rd party tools would be helpful? any ideas?

thx|||Sorry it took me so long to get back to you. I had to dig through my e-mails to find the link. Go to www.hitsw.com. On their home page, look in the middle third for a link to "SQL Middleware".

I have not used their products, but a friend of mine has. He demonstrated the capability to link a DB2 server using the OLE DB provider from Hit Software.

I hope this helps.

Regards,

hmscott

Originally posted by vmlal
I'm using the OLE DB Provider as follows:
srvproduct: DB2OLEDB
providername: MSDASQL

I have installed the right drivers for DB2 etc.

What other 3rd party tools would be helpful? any ideas?

thx|||Originally posted by vmlal
I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?

Use an openquery instead.|||Originally posted by snail
Use an openquery instead.

Doesnt work with openquery either.

PS: The DB2 is on AIX boxsql

db_owner role and table owner issues

Hi,

I have given a user db_owner role in a database. When he creates a table using Enterprise manager the table owner is dbo. When he creates a table using Query Analyzer the table owner is the user. eg

Enterprise Manager = dbo.Table1

Query Analyer = username.Table1

This causes a problem when the user is writing web applications. Is this an error in the way i have set up permissions ? How can i make them behave the same way?

Thanks for your help.First, don't create tables in EM

Second, it's how they are connecting...

Third, depending on how you want the application to work, qualify the owner...

Fourth, get control...

have them supply you with the DDL, and you create the tables for them...

My guess is that EM is connected with sa, and QA is connecting with their id...

just a guess...|||Thanks for your quick response. I should have included the following information.

I work in a University computing department where students must learn to create tables etc. I could not create their tables for them as it is part of their assessment (and there are 1700 students!)

Students can only log on to the server using windows authentication so will log on using the same windows account to both EM and QA.

Students are taught to use both EM and QA which is why they are finding problems.

Thanks again for all help.|||Gotta test it...which way do you want the tables qualified for their apps...

dbo?

I'll look into it...|||yes dbo thank you.

db_owner role

I am getting this error message when disabling a job. The user is not a SA.

TITLE: Microsoft.SqlServer.Smo

Alter failed for Job 'XYZ'.

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

EXECUTE permission denied on object 'sp_help_operator', database 'msdb', owner 'dbo'. (Microsoft SQL Server, Error: 229)

The user can diasble the job if i give db_owner permission on msdb.

Is there a way i can do this without making the user db_owner?

Thanks for any help

There is no specific db_role for job administation, but you can create one. Grant the appropiate permission to the role that you need and assign the user to that role. You sure can also only grant the specific user the rights for that, but the next time you want another user to do the job you will have to repeat your work for that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks.

Could you describe the "Grant the appropriate permission to the role that you need" in as little more detail as to what has to be done?

Thanks for the help

|||

I don′t know the complete list of permissions you will need for your work to be done, so you would have to go the iterative way to get your work done, here are the steps to complete:

-Create a db Role in the Database msdb (as you want to administer the alerts and things related to the SQL Agent)
-Assign users to this role that should do the administrative work.
-Assign the appropiate permissions to that db role (where the users are currently in) Put in the execute right for that procedure you get an error for.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

db_owner member execute problems

I've made a user a member of the db_owner role of a particular database.
However, this account does not have permission to execute a stored procedure
and I can't figure out why. The error message is shown below...
Server: Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 19
User does not have permission to perform this action.
Any help appreciated
Regards
PaulOnly members of the sysadmin or serveradmin server roles can
execute sp_addmessage.
-Sue
On Fri, 15 Jul 2005 10:25:30 +0100, "Paul Hatcher"
<paul.hatcher@.online.nospam> wrote:

>I've made a user a member of the db_owner role of a particular database.
>However, this account does not have permission to execute a stored procedur
e
>and I can't figure out why. The error message is shown below...
>Server: Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 19
>User does not have permission to perform this action.
>Any help appreciated
>Regards
>Paul
>|||Sue
Thanks for that - I was misreading the message as a lack of ability to run
the sp - I forgot that I was creating a custom message inside it!
Regards
Paul
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:voafd15qnose10e9dvdlrobdt9ohmh4s8p@.
4ax.com...
> Only members of the sysadmin or serveradmin server roles can
> execute sp_addmessage.
> -Sue
> On Fri, 15 Jul 2005 10:25:30 +0100, "Paul Hatcher"
> <paul.hatcher@.online.nospam> wrote:
>
>

db_owner and administrator role

if I associated an user to role : db_owner, he has automatically
administrator role or I have to define another thing ? I yes what I have to do
thanks
ftct
d
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct
|||Db_owner "automagically" give you the aapropiate permission as an
aadministrator in the database:
From BOL:
db_owner Performs the activities of all database roles, as well as
other maintenance and configuration activities in the database. The
permissions of this role span all of the other fixed database roles.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct
|||Hi,
If you allocate db_owner previlage to an user, that particular user will be
an administrator in that database. He can perform all the activities inside
that database
like Backup Restore, Create table, Truncate table, setting previlages ...
But he can not do any activities in configuration parameters or any changes
to other database.
But if you set the SYSADMIN Server role automatically user can perform any
task in all databases as well as he can change the server config parameters.
Thanks
Hari
SQL Server MVP
"ft" <ft@.discussions.microsoft.com> wrote in message
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
to do
> thanks
> ftct

db_owner and administrator role

if I associated an user to role : db_owner, he has automatically
administrator role or I have to define another thing ? I yes what I have to
do
thanks
ftctd
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct|||Db_owner "automagically" give you the aapropiate permission as an
aadministrator in the database:
From BOL:
db_owner Performs the activities of all database roles, as well as
other maintenance and configuration activities in the database. The
permissions of this role span all of the other fixed database roles.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct|||Hi,
If you allocate db_owner previlage to an user, that particular user will be
an administrator in that database. He can perform all the activities inside
that database
like Backup Restore, Create table, Truncate table, setting previlages ...
But he can not do any activities in configuration parameters or any changes
to other database.
But if you set the SYSADMIN Server role automatically user can perform any
task in all databases as well as he can change the server config parameters.
Thanks
Hari
SQL Server MVP
"ft" <ft@.discussions.microsoft.com> wrote in message
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
to do
> thanks
> ftct

db_owner and administrator role

if I associated an user to role : db_owner, he has automatically
administrator role or I have to define another thing ? I yes what I have to do
thanks
ftctd
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct|||Db_owner "automagically" give you the aapropiate permission as an
aadministrator in the database:
From BOL:
db_owner Performs the activities of all database roles, as well as
other maintenance and configuration activities in the database. The
permissions of this role span all of the other fixed database roles.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ft" <ft@.discussions.microsoft.com> schrieb im Newsbeitrag
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
> to do
> thanks
> ftct|||Hi,
If you allocate db_owner previlage to an user, that particular user will be
an administrator in that database. He can perform all the activities inside
that database
like Backup Restore, Create table, Truncate table, setting previlages ...
But he can not do any activities in configuration parameters or any changes
to other database.
But if you set the SYSADMIN Server role automatically user can perform any
task in all databases as well as he can change the server config parameters.
Thanks
Hari
SQL Server MVP
"ft" <ft@.discussions.microsoft.com> wrote in message
news:03C6FD05-1A7B-40AA-A45F-C5B9AE5A57B5@.microsoft.com...
> if I associated an user to role : db_owner, he has automatically
> administrator role or I have to define another thing ? I yes what I have
to do
> thanks
> ftct

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_ddladmin role without 'drop' capability

Hello -
I'm looking for a way to assign a user db_ddladmin
permissions on a particular database, but without the DROP
functionality. I want them to be able to do everything
that the role entails, but not to be able to drop tables
etc. Any help would be greatly appreciated...thanks!
MatthewHi,
If you provide db_ddladmin role you cant restrict the user from drop
command. Because the DENY or REVOKE
command can not be granted.
So alternative is provide the roles 'db_datareader', 'db_datawriter' the
user and provide explicit grant to
create table,create proc,create function,create view.
Sample:-
sp_addrolemember 'db_datareader','user'
go
sp_addrolemember 'db_datawriter','user'
go
grant create table,create proc,create function,create view to <user>
Thanks
Hari
MCDBA
"Matthew" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c46dd3$52520310$a301280a@.phx.gbl...
> Hello -
> I'm looking for a way to assign a user db_ddladmin
> permissions on a particular database, but without the DROP
> functionality. I want them to be able to do everything
> that the role entails, but not to be able to drop tables
> etc. Any help would be greatly appreciated...thanks!
> Matthew|||Thanks Hari, much appreciated!

>--Original Message--
>Hi,
>If you provide db_ddladmin role you cant restrict the
user from drop
>command. Because the DENY or REVOKE
>command can not be granted.
>So alternative is provide the
roles 'db_datareader', 'db_datawriter' the
>user and provide explicit grant to
>create table,create proc,create function,create view.
>Sample:-
>sp_addrolemember 'db_datareader','user'
>go
>sp_addrolemember 'db_datawriter','user'
>go
>grant create table,create proc,create function,create
view to <user>
>--
>Thanks
>Hari
>MCDBA
>"Matthew" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c46dd3$52520310$a301280a@.phx.gbl...
DROP[vbcol=seagreen]
>
>.
>

DB_DDLAdmin Role in SQL

Hello:

I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains?

Thank you,

-H

Hi WebD,

A user with db_ddladmin role just means that the user is authorized to run any DDL (data defination language) command in the database. So, based on my understanding, I don't think it will cause server security troubles in the distance futer. As to database security configurations, I think the best practices are to avoid assigning server roles to users but instead, assigning database levels to users to make sure users are only authorized to some particular databases and not the whole databases installed in your instance.

You can refer to this article for more detailed information and better explanation:http://www.sql-server-performance.com/articles/dba/sql_security_p1.aspx

Hope my suggestion helps

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

sql

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...[vbcol=seagreen]
> 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...
error[vbcol=seagreen]
> get
file
>
|||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[vbcol=seagreen]
> 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...
database[vbcol=seagreen]
> error
I
> file
>
|||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[vbcol=seagreen]
> the permission.
> Thanks
> Hari
> MCDBA
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uTQM2WaMEHA.2824@.TK2MSFTNGP10.phx.gbl...
> created
> the
user[vbcol=seagreen]
> id
> database
I[vbcol=seagreen]
manually.
> I
>

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 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...
error[vbcol=seagreen]
> get
file[vbcol=seagreen]
>|||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...
database[vbcol=seagreen]
> error
I[vbcol=seagreen]
> file
>|||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...
> created
> the
user[vbcol=seagreen]
> id
> database
I[vbcol=seagreen]
manually.[vbcol=seagreen]
> I
>

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 trigger to avoid duplication of row ? how please

I'm Maintaining an existing website. Many times user clicked the button
twice, which results in duplication of row. now i cant do any modifications.
but i can write an trigger to check any row with same data,time stamp etc.
if so, then delete existing and insert new row with same data. but i do know
about trigger ? how can i achieve this validation using trigger.
Thnkz ,
Mahesh kumar.RCan't you use a UNIQUE constraint? For maintaining uniqueness that
would usually be the method preferred over a trigger.
David Portas
SQL Server MVP
--|||Why not define a primary key or unique constraint on the table?
Or, prevent the user from clicking the button twice, by disabling the
button, once it is pressed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
news:%23CDz0EjJFHA.2772@.TK2MSFTNGP14.phx.gbl...
I'm Maintaining an existing website. Many times user clicked the button
twice, which results in duplication of row. now i cant do any modifications.
but i can write an trigger to check any row with same data,time stamp etc.
if so, then delete existing and insert new row with same data. but i do know
about trigger ? how can i achieve this validation using trigger.
Thnkz ,
Mahesh kumar.R

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