Permissions to run DBCC SHOWFILESTATS

All,
We have a custom app (that unfortunately can't be modified any time soon)
that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
information. However, we need to allow non-admins/dbowners to use this
function. As I am not an SQL Server Admin, I haven't the faintest idea
how to allow this. I've tried GRANTing the right with no luck. Can anyone
help this poor schmoe out and tell me exactly how to grant execute rights
for DBCC SHOWFILESTATS on a given database (let's say Northwind for
example)?
Many thanks in advance.
--
--Sasquatch
"And I thought 'Reverend Billy ...', you know, which is good 'cause when I
think 'Reverend Debra ...', that's another set of problems altogether."
-- Rev. Billy C. WirtzTry Assigning System administrator role to that user (who
has to perform the dbcc showfilestats command).
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>|||Hi Sasquatch
There are many commands for which permission cannot be granted; many DBCC
commands, including this one, are in that category.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Sasquatch
Please do not post the same question simultaneously in multiple newsgroups.
This has been answered in another group already.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Mangai
This is NOT recommended if you care at all about security and integrity of
your database. Putting someone in the sysadmin role gives them far too much
privilege, and that role should be restricted to those who really need full
privilege.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:06ac01c3af25$5e739630$a501280a@.phx.gbl...
> Try Assigning System administrator role to that user (who
> has to perform the dbcc showfilestats command).
> >--Original Message--
> >All,
> >
> >We have a custom app (that unfortunately can't be
> modified any time soon)
> >that uses the undocumented command "DBCC SHOWFILESTATS"
> to get data file
> >information. However, we need to allow non-
> admins/dbowners to use this
> >function. As I am not an SQL Server Admin, I haven't the
> faintest idea
> >how to allow this. I've tried GRANTing the right with no
> luck. Can anyone
> >help this poor schmoe out and tell me exactly how to
> grant execute rights
> >for DBCC SHOWFILESTATS on a given database (let's say
> Northwind for
> >example)?
> >
> >Many thanks in advance.
> >
> >--
> >--Sasquatch
> >
> >"And I thought 'Reverend Billy ...', you know, which is
> good 'cause when I
> > think 'Reverend Debra ...', that's another set of
> problems altogether."
> > -- Rev. Billy C. Wirtz
> >.
> >|||You can use
select
fileid,
groupid,
size/8 as TotalExtents,
fileproperty(name,'SpaceUsed')/8 as UsedExtents,
name,
filename
from sysfiles
where groupid <> 0
to give you the same output as DBCC SHOWFILESTATS. You
only need public access to run this. You can comment out
the where clause to give you figures for the log files as
well.
As you can't change the app, this probably doesn't help.
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>