question

MARIEAlexandre-2733 avatar image
0 Votes"
MARIEAlexandre-2733 asked MARIEAlexandre-2733 answered

How to grant DBCC INFO and DBCC DBTABLE commands to an user which has no server administrator role ?

Hi all the community,

I'm proud to post my first question here (and pretty sure won't be my last one...), after lot of researches everywhere, I did not find a final answer...

Is it possible in SQL server to grant DBCC commands to a user which not belongs to server administrator role ?

Thank you very much, have a nice day and take care of you and others.

Best regards,

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Sort of, but you need to know what you are doing. Beware that both these DBCC commands are undocumented.

There is a general technique you can employ when want a non-admin user perform an action that requires permissions, you are not keen on granting that user directly. You put the command(s) in a stored procedure. Then you create a certificate in master, and sign the procedure with the certificate. Then you create a login from that certificate, and you grant that login the permissions needed. (Which in this case, I suspect is membership in sysadmin.)

You have to be very careful when you write the procedure, so that it restricts what the user can to. I don't know these DBCC commands (as I said, they are undocumented), but if they require dynamic SQL to put in arguments, you need to be very careful, so that you don't have an SQL injection hole. So my gut feeling is that you should not try this at all.

But if you want to pursue this, you can learn the details of this technique from my article Packaging Permissions in Stored Procedures.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MARIEAlexandre-2733 avatar image
0 Votes"
MARIEAlexandre-2733 answered

Thank you very much !

That's was one of my scenario in my mind, code it in SP, but your article is fully clear and very helpfull from my point of view, but not sure it was possible...

You hit the bull's-eye !

I know the DBCC command are "undocumented" and by the way in case of issue on production probably not supported by editor support team.

For sure, I will instruct developpers to highly secure their code...as old developper I will write the test plan to prevent from any SQL injection possibilities...and others security issues...

Anywhere you are on the planet (or perhaps others planets, who knows...) , I wish you the best !

Take care.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.