February 9, 2005 at 4:18 am
I've been wondering if it's possible to set up a role, which can have restricted specific DBCC access to one database.
Given that a particular user would require a given DBCC access, such as, say 'DBCC show_statistics', but only on one database on the server, and with specifically that DBCC call, is there any method available to grant only that command on that database?
So far, granting DB Owner role works, but not a lot else, and I'm not a firm fan of granting excessive rights when, with the right granularity of control, the correct permissions can be given. And DB Owner is a bit much, just for the granting of one call.
Any thoughts on this greatly appreciated.
Cheers,
Rich
February 9, 2005 at 5:43 am
Not that I have tried this before (seriously) just thinking out of the box...
Have you created a stored-procedure that does the DBCC that you want have that owned by the dbo and grant EXEC to the specific user(s), group(s)?
How bout trying something like
CREATE PROCEDURE usp_Statistics
@table VARCHAR(2000),
@index VARCHAR(2000)
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'DBCC SHOW_STATISTICS(' + @table + @index + ')'
EXEC @SQL
GO
and then EXEC usp_Statistics 'tablename', 'indexname'
Good Hunting!
AJ Ahrens
webmaster@kritter.net
February 9, 2005 at 7:02 am
That's a possiblity for a solution (may test it for purely local and new work), however, the solution I'm looking for is one that allows the dbcc to run bare due to existing applications already in the field that need it..
Plus, getting to know the security system at that granularity would make me a little happier when designing frameworks for new databases, rather than having to write wrappers.
If, however, wrappers are the only way, I guess I'm going to have to nudge a few devs to do some rewrites.
Thanks for the feedback though! Much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply