November 5, 2009 at 12:11 pm
Hi Everyone
In my SQL 2005 production environment I have a director that is wanting to be able to view the properties of any of his team's databases. His read access of course is not enough and I don't want him to be able to change any of the properties of the databases.
Is there a way to lock down in DBO so he only has the ability to see but not change anything in the database? He only wants to see the properties of every database.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
User 'adptestone' does not have permission to run DBCC showfilestats for database 'APEX'. (Microsoft SQL Server, Error: 7983)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=7983&LinkId=20476
November 5, 2009 at 12:17 pm
Why would someone at a Director level need to see database properties for? What are they hoping to get from it?
Personally, I'd say no. You can maybe create a process that can dump the sp_configure results down into a table, or give them a wrapper to run sp_configure, but I would not open up the security to allow them to view the properties directly. Director or not, they don't need sysadmin permissions in the DB.
November 5, 2009 at 3:03 pm
Agree with John on this one. You could setup an automated job to dump the info to a table for him and then grant him read permissions to that table.
As far as management having excessive permissions, what would be his justification? In situations like this it is good to have good policy and change management in place. If a manager/director wants access to something - they will need to provide adequate reasoning and get sign-off from their boss.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 5, 2009 at 4:36 pm
Thanks, I totally agree with all of you on this.
My current solution to see if this will appease him is to create a proc. I will see if that will fly. He can see the data but cannot do anything to change anything.
CREATE PROCEDURE DatabaseInformation
AS
SELECT name, create_date, compatibility_level,user_access_desc,state_desc, recovery_model_desc,page_verify_option_desc FROM sys.databases
WHERE name = 'abc'
OR name LIKE 'abc%'
ORDER BY name
EXEC sys.sp_helpDB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply