DBA Contract without Local Admin priveleges

O what a place to be

I started this Contract as an (Interim) for a new DBA role, for an application support Company last month & all was going well.

The User Application is run via Citrix against multiple Hosted Sybase ASA Databases.

I introduced SQL 2005 with Reporting Services as a mixed Data Mart Remote Query via ODBC Linked Servers setup.

Because they had never had a DBA before the Data I was able to pull from over thirty seperate databases into one and present via Reporting Services has blown them away.

And then one day the Senior Support Analyst told me he had put the main most important Sybase Database on a completely seperate domain he had created(with no Trust between the two) , because he was unable to secure the existing domain against unauhorized remote internet intrusion & Viruses.

(I never liked the idea that hosted customers were domain\users on the Corporate network)

To add insult to injury he then told me to install & maintain another SQL Box on the new domain, OK so far.

I logged into the supoposed new box via citrix & then remote desktop, and to my disbelief he had the desktop locked down - no access to control panel or anything - he asked me why i needed access - I told him - he asked me why I need to have reboot priveleges - I told him.

So now he's installed 2005 himself in the vain hope I can work without Local Admin privelages or need to unlock the Desktop - he certainly won't give me Domain Admin.

I Just cannot believe I'm unable to persuade him to Unlock the Desktop & have even threatened to walk out unless he lets me do my Job.

He probably does'nt like me but there can be absolutely no question about my abilities or accessing data that i should'nt.

He's basically read a Deny by Default article and expects me to start of as a user with a locked desktop and then request & justify escalating my security from there.

Is this possible ?

Good Grief :eek: Any ideas what I should do ?

Thanks

GW& have even threatened to walk out unless he lets me do my Job.

I say do that! :p

Or tell him that if the server needs a reboot then he has to do it - point out to him that if the server goes down (day or night) he will have to remote connect to work and sort it out - rather than having one of his capable "drones" doing it. Tell him to expect to be on-call 24/7/365 (that is assuming that he wants his customers server to be online 99.9% of the time)

If he's so uppity about security then maybe you should suggest signing some disclaimer saying that you're not going to prat about with the server or any sensitive data in contains?

You can't do your job without the access, period.

Alternatively, simply tell him to "[expletive] off" when he asks you anything about that server :D|||I would just pester him with emails (cc'd to appropriate folks) that you need various tasks done on the server. Be good and vague about the actual details. For example: "Please be sure that daily backups are taken of these databases." If he is worth dealing with, he will figure out SQL Agent quickly enough. If not, well, just sit back and laugh as he does the backups manually. Escalate as necessary (Make sure the backups are not on the same physical disk as the database).

Once he has all that squared away, if you are not satisfied, hit him up with profiler trace requests. This should be accompanied by a stream of SQL scripts that are "tweaks to existing code".

Polish the whole thing off with requests to load data from various sources (implementation details left to him, of course).

And of course, most importantly, sprinkle liberally with thanks, and politeness.|||Ahh, the old scaremongering tactics - hadn't even crossed my mind!|||Who is your manager? They should be fighting this battle with/for you. If you where hired to do a job and are unable to it will reflect badly on the whole chain.

Alternatively I think goergev's first approach is best. Getting into a p_ssing contest with a Senior employee when you are new is not a wise path.

Changing culture is never an easy task. Good luck.|||MMMmmmmm tehe - U Monkeys !!

Such a shame though this is going to slow my dev progress to a crawl & I take pride in my work.

I'm happy as a contractor and would'nt take a permie role anyway - hope the new DBA likes his new life.

I just wonder how he's (Senior Support Analyst) gonna secure his brave new world when he's not capable of purging the existing one.

Dunno loads about Citrix & Network Security best practices but Is it common to let Customers on to the Corporate Domain as Users, Is Citrix really that secure ?.

I figure he's set himself up as Domain Admins and does'nt want to share his power with the rest of IT.

GW|||I figure he's set himself up as Domain Admins and does'nt want to share his power with the rest of IT.
bingo! :)

If he's willing to take all the power, then he's gotta be willing to take all the responsibility that comes with it too.|||Sorry, I mis-understood your message of "interim for a New DBA role". I saw that as "contract for hire".

I am a contractor too so I understand the "no permies" feeling. But I will ask you the question. What were you hired to do? Write reports or prepare the way for a new DBA or both. Since you are a contractor you can be even bolder in dealing with culture. Tell them there is a helpful way and a non-helpful way to do things. Right now he, and by extension the company, is in a non-helpful mode.

As a contractor I would much rather come into a company where the previous contractors where helpful themselves because it makes my experience better.|||Sorry Bartron Maybe I was'nt quite Clear.

The company had never had a DBA before - they hired one but had to wait 3 Months for him to start - Thus I got the contract for 3 months to fill in.

(they have a 5 strong IT Application Support Dept with strong links to the Application Developers in the holding company)

The new DBA has since turned down the job & the Co. are now actively seeking someone else.

My Brief was simple "Consider us a Greenfield site and start from scratch doing what you think is Best - we need reports on all these seperate Sybase databases".

So I recommended and implemented a SQL 2005 Data Mart with reporting Services.

GW|||Sounds like a great approach to their original intent.

Good luck dealing with the new agenda. :-) Seems like "doing what you think is best" should give you some leverage. Of course they can always ignore it. Their peril.|||... expects me to start of as a user with a locked desktop and then request & justify escalating my security from there.

Seems pretty standard at face-value. I'm don't need domain or local admin on any of our production sql server boxes to do my job. It would help and it sure would be nice, but I don't need it. On the same token, our network engineers understand and take on responsibility for the server itself. This includes restarting it on my request, staying current with patches and feeding me requested wmi indicators.

The problem arises when someone is locking you down just because they can. If they still give you grief after you have clearly justified your requirements, take it to your contract admin and draw a picture for them of how this person is directly hindering your ability to perform.|||Thanks to everyone for your support.

Looks like I'm just gonna have to leave em with a less developed and less stable product.

Thanks Teddy for clarifying it is possible to be a DBA without Local Administrator security.
(Do they allow you to access the control panel or event logs ?)

Just seems ridiculous to me considering I'm the only DBA here & I recommended, designed & wrote the Bl**dy thing.

It's not even an OLTP it's a sodding homemade Data Mart.

The only thing on this new network for the next few months is one of the 30 Sybase DB's - I have Domain Admins to the current network.

:eek: A Contractors Life is not an easy one.

GW|||Thanks Teddy for clarifying it is possible to be a DBA without Local Administrator security.
(Do they allow you to access the control panel or event logs ?)


No, yes.

I'm in a highly responsive environment though so this works fine. If I OMGJUSTNEED to perform administrative tasks, I can tap an engineer and either guide them or have them over my shoulder as I do whatever it is I need to do. I get whatever general filesystem access I request and I can requisition whatever additional logging I need including exposing log files, setting up wmi logging or asking for a new package to be designed for one of our third-party performance monitoring applications.

If engineering wasn't as responsive as they are, I would be able to justify administrative rights on our servers. If you're in one of those "job security through obscurity" environments where one person is guarding the keys to the proverbial kingdom with their life, then you might want to go ahead and push for those admin rights.

DB2 Vs SQL Server?

Hi,

I come from a strong SQL Server background.

I am moving into a new role where the company use DB2.

Is there much difference in terms of syntax between DB2 and SQL Server etc?

What is DB2 like to work with (environment, reliability etc)?

Any feedback is much appreciated.

Thanks.I am thinking you might get more productive input over at:

www.dbforums.com|||Try this urls for DB2 info. Hope this helps.

http://pages.ca.inter.net/~ccdb2/link_db2.htm

Kind regards,
Gift Peddie

db_owner to all tables

is there a command that can change a login role to db_owner in all the
tables, or do i have to use
{
USE table_name
EXEC sp_adduser 'login name'
EXEC sp_addrolemember 'db_owner', 'login name'
}
for each of the tables ?

thanksHi

Rather than adding the login to the db_owner role, why not changed the
object ownership using sp_changeobjectowner?

e.g. to get a list of commands

DECLARE @.username sysname
SET @.username = 'ABC'
select 'EXEC sp_changeobjectowner ''' + u.name + '.' + o.name + ''',
''dbo''' from sysobjects o
JOIN sysusers u on o.uid = u.uid
where u.name = @.username
and o.type = 'U'

You could change this to a cursor and run each statement with EXEC. You will
need to watch out of dependencies and also make sure the correct owner
prefix is used wherever it is referenced.

If you want to change the owner or the database use sp_changedbowner.

The USE statement is for databases not tables.

John

<liorhal@.gmail.com> wrote in message
news:1116165893.813043.270140@.g44g2000cwa.googlegr oups.com...
> is there a command that can change a login role to db_owner in all the
> tables, or do i have to use
> {
> USE table_name
> EXEC sp_adduser 'login name'
> EXEC sp_addrolemember 'db_owner', 'login name'
> }
> for each of the tables ?
> thanks

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_dtsadmin role

Our DBA has given me access to MSDB on the SSIS service on one of our servers as db_dtsadmin. When I try to connect to the server using Integration Services in the connect drop down menu, I get the following generic error msg: connect to SSIS service on server 'xxxx' failed: access is denied.

I'm told this role should be sufficient to give me access. Do I need other server access roles to use in conjunction with db_dtsadmin or are we missing something really easy here.

Thanks.

DBRICHARD wrote:

Our DBA has given me access to MSDB on the SSIS service on one of our servers as db_dtsadmin. When I try to connect to the server using Integration Services in the connect drop down menu, I get the following generic error msg: connect to SSIS service on server 'xxxx' failed: access is denied.

I'm told this role should be sufficient to give me access. Do I need other server access roles to use in conjunction with db_dtsadmin or are we missing something really easy here.

Thanks.

This link might help: http://msdn2.microsoft.com/en-us/library/aa337083.aspx|||Thanks! This looks to be the answer. We'll try it.|||

DBRICHARD wrote:

Thanks! This looks to be the answer. We'll try it.

If that works for you, be sure to mark this thread as answered. Thanks!|||Thanks. We're still trying to work on this. We followed the instructions on the link (connecting to a remote integration services server) but I still can't connect and still receive the same generic 'access denied' error message. There must be some other permissions needed besides what is outlined on the link. Back to the drawing board.|||You could also ask this question over on the SQL Server security forum... Might generate a few more responses.

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_datareader role**

Hi
I defined a new login"login1" in SQL Server 2000 and
tried to make an access(public+db_datareader)
to one of databases called "db1" successfully.
then in query analyzer I logined by "login1" and
saw the name of more than one databases in left window
,there were ("master","tempdb","distribution",
"db1","northwind","pubs","msdb"),and when I tried to open them ,for
example I selected "tempdb" and
then clicked the right key of mouse on a view
called "dbo.sysconstraints" and it opened successfully,why? I want my user
just read the information of one database called "db1"!!!!
second question: why didn't appear other databases
in left window of query analyzer?
3th question ,what's the best selection to access
"login1" just reading the information(just select statement) stored in
"db1"?(for example:
is it correct to be member of public role and then
check the check boxes of select column in permission
section of all tables in "db1" one by one!?)
any help would be greatly appreciated.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/A login can access a database only if the login is a user in the database or
the guest user is enabled. Since any login can access a database containing
the guest user, the other databases listed must have the guest user enabled
and this is why 'Login1' can access those databases even without a database
userid. Note that the guest user is required in the master and tempdb
system databases but can be removed from other databases if you don't want
all logins to have access to those databases.

> I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why
The public role has SELECT permissions on system tables and views. This is
needed in order to retrieve meta-data needed by database access APIs.

> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
Adding the user to only the db_datareader role will provide SELECT
permissions on tables and views. If you need more granular permissions, you
can create your own database role and grant the desired permissions to the
role. This allows you to control user database permissions via role
membership without granting direct permissions to individual users.
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4uk7scihqligo@.msnews.microsoft.com...
> Hi
> I defined a new login"login1" in SQL Server 2000 and
> tried to make an access(public+db_datareader)
> to one of databases called "db1" successfully.
> then in query analyzer I logined by "login1" and
> saw the name of more than one databases in left window
> ,there were ("master","tempdb","distribution",
> "db1","northwind","pubs","msdb"),and when I tried to open them ,for
> example I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why? I want my user
> just read the information of one database called "db1"!!!!
> second question: why didn't appear other databases
> in left window of query analyzer?
> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
> any help would be greatly appreciated.
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||How can I recognize a guest user in a database?
On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> A login can access a database only if the login is a user in the
> database or
> the guest user is enabled. Since any login can access a database
> containing
> the guest user, the other databases listed must have the guest user
> enabled
> and this is why 'Login1' can access those databases even without a
> database
> userid. Note that the guest user is required in the master and tempdb
> system databases but can be removed from other databases if you don't
> want
> all logins to have access to those databases.
>
> The public role has SELECT permissions on system tables and views. This
> is
> needed in order to retrieve meta-data needed by database access APIs.
>
> Adding the user to only the db_datareader role will provide SELECT
> permissions on tables and views. If you need more granular permissions,
> you
> can create your own database role and grant the desired permissions to
> the
> role. This allows you to control user database permissions via role
> membership without granting direct permissions to individual users.
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> How can I recognize a guest user in a database?
To see if the guest user is enabled in a particular database:
USE MyDatabase
sp_helpuser 'guest'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4vwbsg8hqligo@.msnews.microsoft.com...
> How can I recognize a guest user in a database?
> On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
>
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

db_datareader for new tables

I have added someone to the db_datareader role and that is fine. However, if I add a new table to the database, they do not have 'Select' permission for the new table. Is this by design? Is there a way around this to give users read permission on all tables current and future? Thanks for any answers to this question.I think the role db_datareader is a db-wide role, by default it applies to all tables/views in the database, including the newly created ones. You do not need to set the 'select'-permission.|||A bit of a longshot, but run

sp_helprotect [table name]

and see if someone has denied access to the table for some reason.|||According to Microsoft (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3xns.asp) a member of db_datareader can read every table, past, present, and future.

-PatP|||Explicity denying access to a table will trump db_datareader, but I don't think anything else does.|||in an unrelated note
how about creating views for all those users.
hmmmmmm?

views have many advantages over direct table access.

they add a security layer between the user and the table
they mask database complexity
they can increase read performance
and they can give you a layer between the root object and the user so object name changes can occur without recompillation of the application.


just a thought.|||I know what you mean, Curt. The db_datareader role makes it awful hard to add things like a salary table to your database, too ;-).

But then, when was the last time someone actually thought about security, anyway. I mean without the DBA storming over to his cube?|||i wont give a database to a developer until i explain the importance of views and stored procedures.

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...
>> 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.