sqlserver

Changing the Owner of Tables in SQL Server

You may configure SQL Server such that users who are not dbo may create tables, but you set yourself up for a world of pain.

Soon, you can no longer do simple table selects (because dbo is inferred) and instead must resort to prefacing every join with [user].[tablename].[fieldname].

The solution is a SQL Server system stored procedure sp_changeobjectowner:

EXEC sp_changeobjectowner 'user.table', 'dbo'

Note that this operation will clear all privileges.

Credit goes to my old co-worker Bruce, who shared this solution many six years ago in an email titled, "Lucky You". I had several hundred tables and views to re-title and privilege at the time.

Database Documentation Made Easy

Data Dictionary Creator is a simple application which helps you document SQL Server databases. It stores all the information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.  The tool is open source and available as an executable and source code on codeplex.  The first code release was November 2006 with over 40 releases and patches up until today.  Authored in C#.Net.

Database Dictionary Creator

I could go through a step-by-step, but there is already a great walkthrough online.

The html grouped option was great for me - it even creates the anchor tags to jump from the table list to the table details. 

The only step remaining in my mind was to link the database diagram to the table details by exporting a diagram from sql server and adding an image map.  Since those steps are custom, I'll document them here:

Exporting a SQL Server Relationship Diagram

  1. Right-click in the diagram pane
  2. Choose "Copy Diagram to Clipboard"
  3. Paste in an image management destination program and save as image

Creating an Imagemap

  1. Open an online imagemap creator such as http://www.maschek.hu/imagemap/imgmap
  2. Upload an image or provide a url to an image and click "accept"
  3. Draw a box around each table in the diagram.  Add hypertext references (href) and alternative text (alt) attributes for each hyperlink.  Href's should link to the anchors in the format "#dbo.{tablename}", alt tags are displayed on hover, "View {tablename}" is probably appropriate.
  4. When all tables have been outlined and linked, click code and copy the text

Putting it all together

  1. Open the html produced by the Database Documention Creator in export grouped mode
  2. Just inside the body tag (after <body>), add a reference to the image you created (hint: it must be accessible to the html you are viewing - it is easiest to place it in the same directory).  An html image is referenced like the following <img src="imagename.format" alt="Alternative Text to Display" />
  3. After your image paste the code you copied.  This is the imagemap it must be using the same copy of the image that your used to create the imagemap.  If the image has been resized or modified the coordinates will no longer match.
  4. Link the imagemap to the image by copying the map id from the map code to the image tag like the following: <img usemap="#map id value" src="imagename.format" alt="Alternative Text to Display" />.  Notice I have added a pound symbol before the id attribute.
  5. Save your html and view it in a browser.  The product should resemble http://fishandgame.idaho.gov/ifwis/core/home/model.

How this could be better

The source code for both applications I have used are available online.  The Database Dictionary Creator is available for free and the Imagemap Creation Tool for a modest donation.  The whole process might be slightly smoother if we write some code to wrap together the imagemap creation and publishing to the web.  I'm thinking we use the existing Database Dictionary Creator to export the grouped html, then have an upload page which accepts the created html and an image.  The user then adds an imagemap and the script cleans up the html, puts it all together into one page and publishes the product.

SQL Server User-Defined Data Types Not Visible to MS Access on Linked Table Refresh

Had one of those, "am I on crazy pills?!" problems the past few weeks. In an MS Access database I had a whole slew of SQL Server Linked Tables that included User-Defined Data Types(UDT). The UDTs were mostly auditing fields that referenced defaults to pull in the system.user and current datetime.

Everything worked great.

That was until I added a field and asked a user (with very elevated privileges) to refresh the linked tables. They did, but the audit fields referencing the user-defined types disappeared.

At first, I thought it was some silliness in their file-based DSN, but even using my connection (with Windows-based security... so it pointed to their account) the audit fields remained hidden.

To make the whole story more perverse, if I used the same connection logged in as me (I am a dbo) the tables refreshed with the audit fields and they could also see them.

After much googling I finally stumbled upon this post and this msdn article which indicate that user-defined types have permissions including: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION and REVOKE.

To set permissions to allow anyone to view a user-defined datatype, use the following
 

grant execute on TYPE::[dbo].[user_data_type] to public;

Or the appropriate user or role.

References:

The Case IsNull Now

In SQL Server views I've been using CASE statements to look for nulls for years. I've just nullified this practice.

For the following Employees Table:
EmployeeIDLastNameFirstNameNickname
39CarterJamesJimmy
40ReaganRonald 
41BushGeorge 
42ClintonWilliamBill

Would be used to create the View:
EmployeeIDLastNameHandle
39CarterJimmy
40ReaganRonald
41BushGeorge
42ClintonBill

This is the CASE:
SELECT EmployeeID
, LastName
, CASE Nickname
WHEN Null THEN FirstName
ELSE Nickname
END As Handle
FROM Employees

ISNULLified by:
SELECT EmployeeID
, LastName
, ISNULL(Nickname, FirstName) AS Handle
FROM Employees

 

Restoring Orphaned SQL Server 2008 Users

When moving databases across servers and reinstalling SQL Server almost everything transitions perfectly except SQL Server Users. Unless the user already exists in the Master Security table, the database user is left orphaned inside the database without the ability to perform any useful function. Like you right now, but we're gonna change that.

Here is an example, note how there is a User Name but no Login Name.

User Name but no Login Name

Step 1: Recreate the User in Master
First, if they don't already exist, is to recreate the user in the master database by browsing to the root level Security folder in SQL Server Management Studio, choose Logins and right-click create user.

You tried to check the database under the "User Mapping" page didn't you?

Create failed for User 'UserName'.

That was a good idea hotshot, but it ain't gonna work for you. Just create the user and Ok your way out of there. Time for Step 2.

Step 2: Remap User to Database
We need to run a system stored procedure I found on MSDN, it is pretty straightforward, unfortunately it must be performed for each database.
NOTE: There's got to be a better way, and supposedly this method is deprecated, but this is the only thing I could get to work in my instance, so if you got something better please share.

USE DATABASENAME;
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'UserPassword';
GO


Where:
DATABASENAME = The name of your database
UserName = SQL Server Login to repair
UserPassword = SQL Server Login's password to repair

Full documentation on MSDN for sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378.aspx