Permissions for DBCC commands
-
Anybody know a way to grant execute on DBCC SHOWFILESTATS to a specific user?
Thanks
"Keep Your Stick On the Ice" ..Red Green -
As far as I know permissions for DBCC commands can't be granted to users, only to roles.
Greg
-
Yes and No: DBCC can only be run by logins with the system adminstrator role.
Can sp_spaceused be used instead ?
SQL = Scarcely Qualifies as a Language
-
Thanks everyone. I found a solution. sp_spaceused isn't accurate enough unless you update the usage....which is resource intensive.
I did a little research and found that
select fileproperty(f.name,''SpaceUsed'')*8/1024.00
from sysfiles f
gives me the handy dandy number I need 🙂 so there is no need to go to a DBCC check. Here is the script I was working on...I may post it up later w/ some comments.
BEGIN
SET NOCOUNT ON
--
DECLARE @DBName VARCHAR(128)
DECLARE @SQLString VARCHAR (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
--
CREATE TABLE #TempForFileStats([DatabaseName] VARCHAR(120),
[FileName] VARCHAR(120),
[GroupName] VARCHAR(120),
[Type] VARCHAR (6),
[Size(MB)] DECIMAL(10,2),
[SpaceUsed(MB)] DECIMAL(10,2),
[MaxSize(MB)] DECIMAL(10,2),
[NextAllocation(MB)] DECIMAL(10,2),
[GrowthType] VARCHAR (12),
[FileId] SMALLINT,
[GroupId] SMALLINT,
[PhysicalFile] VARCHAR (260))
--
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'USE ' + rtrim(@dbname) + ' ' +
'SELECT ' + '''' + @DBName + '''' + ' as ''Database'', ' +
' f.name as ''FileName'',' +
' ISNULL(g.groupname, ''LOG'') as ''GroupName'',' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Type'', ' +
' f.size*8/1024.00 as ''Size(MB)'', ' +
' fileproperty(f.name,''SpaceUsed'')*8/1024.00 as ''SpaceUsed (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN NULL ' +
' WHEN 0 THEN f.size*8/1024.00 ' +
' ELSE f.maxsize*8/1024.00 ' +
' END as ''MaxSize(MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' +
' WHEN f.growth =0 THEN NULL ' +
' ELSE f.growth*8/1024.00 ' +
' END as ''NextAllocation(MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' f.filename ' +
' FROM [' + @DBName + '].dbo.sysfiles f, ' +
' [' + @DBName + '].dbo.sysfilegroups g ' +
' WHERE f.groupid *= g.groupid '
INSERT #TempForFileStats
EXECUTE(@SQLString)
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END -- Cursor fetch loop, c_db
DEALLOCATE c_db
--
SELECT RTRIM(@@SERVERNAME) AS [Instance],
RTRIM(DatabaseName) AS [Database],
RTRIM(FileName) AS [FileName],
RTRIM(GroupName) AS [GroupName],
[Size(MB)] AS [SIZE(MB)],
[SpaceUsed(MB)] AS [SpaceUsed(MB)],
[MaxSize(MB)] AS [MaxSize(MB)],
[NextAllocation(MB)] AS [NextAllocation(MB)],
[GrowthType] AS [GrowthType],
[FileId] AS [FileId],
[PhysicalFile] AS [PhysicalFile],
GETDATE() AS [DateChecked],
'@@@@@' AS [Cookie]
FROM #TempForFileStats
ORDER BY [DatabaseName]
------------
DROP TABLE #TempForFileStats
END -- FileSpace script block
"Keep Your Stick On the Ice" ..Red Green
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply