Security changes tend to break existing functionality. True, such changes are often required, but poor users and developers simply want their programs to continue to work.
Case in point: In SQL Server (2005 and later), if you can SELECT from a view, or invoke a function or stored procedures, you do not automatically have the privileges to read the T-SQL definition of these objects.
MSDN states:
In earlier versions of SQL Server, metadata for all objects in a database is visible to members of the public role. This means that any user […] can view metadata for every object in the server, even those objects on which a user has no rights.
In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission.
The consequence is that while you can query sys.objects for the view, you do not automatically get the OBJECT_DEFINITION() of the view.
What are the basic rules?
- If you are db_owner, you see everything.
- If you are db_datareader, you can select sys.objects (tables, views, table-valued functions), but OBJECT_DEFINITION returns NULL.
- To gain access to object definitions (sometimes referred to as catalog views, or metadata) otherwise, you need to have the View Definitions grant assigned:
GRANT VIEW DEFINITION TO [username]
What are the consequences?
Create users with different database roles assigned, and run these statements when connected as these users:
SELECT type, OBJECT_DEFINITION(object_id)
FROM sys.objects
WHERE type IN ('V', 'P', 'FN')
Depending on the user privileges will either receive no data at all, or the object names and a NULL definition, or objects and their T-SQL definition.
As dbscript implements a metadata import for SQL Server databases, it needs the definition of a connection string for each database.
While there is a Test Connection button to check the validity of the connection parameters, it does not yet check whether the Metadata is actually accessible. This functionality will be implemented in an upcoming version of dbscript.
devioblog 