Control metada visibility using few 'simple' statements
You may be aware that a user in a SQL Server 2005 database can only see metadata that the user either owns or on which the user has been granted some permission. A default policy enhanced as compared to how a DBA can control the security access to the database-table level. A better way to prevent unwanted users with minimal privileges from viewing other important data for all objects in that instance.
GRANT VIEW DEFINITION TO public;
This allows a override of view visibility at the database level, better than the one above.
GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;
The last and best one (on security perspective) to allow the visibility on object level (table or view), so if the object is a table, all the columns, indexes, statistics, and constraints of the table will be visible.
Cross posting from one of the Technet article, that you will have to run this statement for each user. You can automate the process by creating a DDL trigger similar to the following:
If the data is confidential then you can take help of allow access with certificate-signed stored procedures that allow to access server-level system tables with minimum information. Refer to the Books Online for SQL 2005 for more information on Certificate-signed access.
To let users with minimal privileges see all metadata, run one of the following statements:
GRANT VIEW ANY DEFINITION TO public;
This overrides all view visibility limitation and allows READONLY access on that SQL instance.
GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;
This is far better in restricting the visibility to a schema based level.
Rather going or allowing the metadata visibility to PUBLIC it is better to enable a specific user or group with minimal READ privileges on the database objects.
CREATE TRIGGER grant_view_definition_on_principal ON DATABASE
FOR CREATE_USER, CREATE_ROLE
AS
DECLARE @event_type sysname, @principal_name sysname, @sql nvarchar(max);
SELECT @event_type = eventdata().value('(/EVENT_INSTANCE/EventType) [1]','sysname');
SELECT @principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
IF (@event_type = 'CREATE_USER')
SELECT @sql = 'GRANT VIEW DEFINITION ON USER :: ' + @principal_name + ' TO PUBLIC ' ;
ELSE
SELECT @sql = 'GRANT VIEW DEFINITION ON ROLE :: ' + @principal_name + ' TO PUBLIC ' ;
EXEC (@sql) ;
GO
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.