Reading View Definitions in SQL Server

November 6, 2012

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.


Limitations of the T-SQL PRINT Command

October 21, 2012

One of the main functions of dbscript is to allow the user to select a set of tables and columns. This selection is used to generate T-SQL code (either a set of statements or the definition of a stored procedure) which can then be executed on a live database to generate INSERT/UPDATE statements reproducing the values of the selected tables and columns. An earlier post illustrated this functionality.

While this works in principle (at least for MS SQL Server), there are restrictions in the T-SQL command that make live harder than necessary if you want to script long text or binary values (NVARCHAR(MAX), VARBINARY(MAX):

During research I found a couple of work-arounds for the 4000/8000 byte limit:

  • stored procedure LongPrint on ask.sqlservercentral
  • stored procedure LongPrint on Adam Anderson’s blog

Both procedures try to find a line break in the string to be output, split the string at the line breaks and prints the substrings accordingly. However, from looking at the code, the procedures only work correctly if there is a line break found within 4000 characters, which is a problem if

  • you have a list of column values without line breaks (in case of dbscript: INSERT and UPDATE values)
  • you want to output binary values whose hexadecimal representation is longer than 4000 characters

With all these restrictions in place, I considered some other solutions:

The sp_executesql solution is necessary because scripts should be generated without any requirements in the target databases. The idea is to encapsulate a version of LongPrint in a TSQL variable, and invoke this code using sp_executesql and passing the string to be output.

Preparing this blog I came to the solution that the current implementation is good enough for most cases. In cases where output length exceeds certain limits, or for databases that enforce similar limits or do not support outputting text at all, a generic C# implementation in the dbscript backend may be the best solution.

To be continued… ;)


dbscript 1.04 released

October 21, 2012

The code for the latest version of dbscript has been sitting around for a while, and I did not find the time to release it until a recent bug report forced encouraged me to finally publish it. What’s more, I promised to provide an update!

So what’s new in dbscript 1.04?

Architectural Cleanup

Up to dbscript 1.03, every new supported database engine and every new object type stored their values in either base columns or object type-specific columns. The object type-specific columns are now mapped onto the same columns in the Object table (using some meta data tables, and an internal code generator which also creates the NHibernate classes).

New functionality

Dependency Diagrams (based on T-SQL analysis of views, stored procedures and functions)

namespace parameter for C# and VB.Net value scripts

Internal Cleanup

Recognize sql server 2008 and 2008R2

Multi-comparison is performed in C# rather than TSQL, reducing the chance of a TSQL timeout, and allows adding/removing project versions after a calculation has been performed.

XmlSchemaCollections are treated as types instead of objects

UI Cleanup

A couple of UI inconsistences have been fixed

Compare Object HTML-escapes correctly

Administrators can delete projects

MediaWiki login

dbscript user Mark made pointed out that dbscript does not work with current versions of MediaWiki anymore. A bit of research found that the login form adds a new hidden parameter wpLoginToken which needs to be submitted in the login POST.

dbscript 1.04 has been modified to provide this parameter, and hopefully this fix solves the issue for everybody having problems generating MediaWiki documentation of their databases.

As always, the latest version of dbscript is available for download here.


Database Changes Done Right

March 12, 2012

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!


Database Dependency Diagrams

September 1, 2011

Preparing the release of the next version of dbscript, I can proudly present you one of its new functions: to generate a Dependency Diagram of all objects contained in a database:


dependency diagram of AdventureWorks 2008 (png)

dependency diagram of AdventureWorks 2008 (dia screenshot)As usual, the diagrams can be generated in .png and .dia format.

Dependency analysis and dependency diagram generation are currently only implemented for MS SQL Server databases.


dbscript 1.03 released

September 15, 2010

dbscript, the integrated database versioning, documentation and code generation web application, has finally been migrated to .Net 3.5 (SP 1).

This makes it easier to install, as the separate installation of ASP.Net Ajax libraries is not necessary anymore.

The draw-back is that it is no longer possible to run the web application on Windows 2000 (Server), if that is a concern to anybody.

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
view
HTML (single file) view view view view
view
MediaWiki view view view view
view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view
view

dbscript is available for download here.


Integrated Database Versioning and Documentation with dbscript 1.02

July 11, 2010

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
view
HTML (single file) view view view view
view
MediaWiki view view view view
view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view
view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

  • Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
  • Update notifications are implemented via JSONP
  • Some fixes

dbscript is available for download here.


Follow

Get every new post delivered to your Inbox.

Join 69 other followers