[ftmjr]

What privileges are needed for a login to run DBCC commands (i.e. dbcc checkalloc, dbcc checkdb, dbcc checkcatalog, etc.)

Curretnly, we have CRON jobs which runs both dbcc commands and update stats daily. The problem is, the dba before me used the default "sa" account to run these jobs / commands. I'd like to stop this practice and change this to a login account with less privilages. I was hoping to just create a regular login and add it to all the databases as a user. So with this in mind...

Are there any special privilages or roles needed to run the below commands (i.e. sybase_ts_role)?

- dbcc checkalloc
- dbcc checkdb
- dbcc checkcatalog

In addition, what privilages does a login need to run daily CRONs (is dbo needed)...

- update stats
- reorg

Thank you.

[trvishi]

You still need sa_role for dbcc checks.

If you do not want to use sa, you can probably create an account for housekeeping and grant it sa role.

You just need to be an object owner at the minimum for update stats/reorg I think. But since the scripts are probably owned by dbas and not developers, again, I would just recommend using an account specific for housekeeping.

Saves a lot of headache.

The only thing you have to be careful is not give out sa password/role to developers. That can lead to more headaches

You can also check the following link which has a workaround.

http://www.sypron.nl/grant_sa.html

[ftmjr]

Please forgive me, but I think only DBO is needed for most DBCC commands. I ran the following scenarios...

SC01 Test - Created a login only then ran DBCC command.
SC01 Results - Message was displayed and said that command cannot be ran unless an account is either DBO of that database or SA.

SC02 Test - Created a login, then added the login as a dbo alias to each database (including system databases).
SC02 Results - DBCC commands ran successfully with no error.

Important Note: It may be necessary to use the "sybase_ts_role" role for specific DBCC commands.

What are your thoughts? Thank you.

[trvishi]

Yeah my bad. Some dbcc commands can be run by dbo I guess. But I guess you need to read the manauls to figure out which ones can or cannot be run with people with sa role. Its very normal for a dba not to remember them

I guess it depends on who owns the responsibilities.

Do developers have their own maintenance scripts or do the dbas have centralized admin scripts?

In some environments,

a) the DBA team takes care of running ALL maintenance scripts (dbcc, index reorg, upd stats).
b) the Developer team takes care of reorg/upd stats because its directly involved with the performance and let the DBAs take care of DBCC
c) Theres no different teams. Its a Developer /DBA environment

My guess was from your postings, in your environment, you do have different teams and if you are responsible for the maintenance scripts, I would use a housekeeper login with sa_role as it saves some headaches.

But the objective is not to give sa role to developers if its different teams. Its not a security hole if you use sa role or sa equiv login to run your maintenance scripts as long as the developers dont have access to those.

[ftmjr]

I agree with you. It's a matter of how granular a person may want to go for security purposes. The more granular, the more work it takes to set up the dbcc / update stats / reorg jobs.

Yes, I agree with you... only the DBA team should have access the SA privilaged accounts. This is the purpose of our project / initiative. To remove SA privilaged accounts from teams who are not a part of the DBA Team. In this process, we are working to disable the default "sa" account and setup new logins with SA privilages.

Thanks for your help...