Report Permissions

A reader of this blog asked me this morning: "If you wanted to design a query to make a report for management that would show which AD Groups or users have access to which reports, how would you go about it."

This is what I quickly whipped up:

SELECT
         Catalog.Name ReportName
         ,Users.UserName
         ,Roles.RoleName
   FROM [dbo].[Catalog]
         JOIN
         dbo.PolicyUserRole
         ON   [Catalog].PolicyID = PolicyUserRole.PolicyID
         JOIN
         dbo.Users
         ON   PolicyUserRole.UserID = Users.UserID
         JOIN
         dbo.Roles
         ON   PolicyUserRole.RoleID = Roles.RoleID

Can anyone see any problems with that?

Microsoft Certified Master - Database Structures

The MCM program is aimed at the uber-uber-uber SQL gurus, and from what I'm reading on various blogs it's no walk in the park for them.

The awesome thing that's popped up out of the newly revised program is that there are a bunch of free training material videos available.

While I'm not personally anywhere near thinking about attempting the MCM certification, I'm interested in hearing about what lies under the covers of SQL server from people who know it inside out. The absolute nitty gritty: like learning about DNA and mitochondria as opposed to the more systemic/anatomical view that the MCITP type courses cover.

The first video in the series covers database structures: the ways that data is stored and managed on the disk. It's presented by Paul Randal, who spent 9 years working on the storage engine for SQL server, so the info is really from the horse's mouth.

To paraphrase the summary of the video:

Records - the rows, or "slots" which make up our tables,
Pages - the 8kB chunks where the records live,
Extents - collections of 8 contiguous pages,
Allocation bitmaps - keep an eye on the extents, and;
IAM chains and allocation units - keep track of what's living where.

If you've a spare 42 minutes, check it out here.

Formatting SQL Code for Blogs

Up until recently, all the SQL code on this blog looked awful: completely lacking in formatting and unreadable (though still useful!). It looked like this:

SELECT
*
FROM
Ow.My.Eyes.Hurt

Then I discovered The Simple-Talk Code Prettifier.

You pop your formatted SQL code in (copied out of SSMS or Visual Studio), choose the style of HTML (forums in my case), whether to correct indenting and lenth of tabs etc., and voila: html code is produced which results in something real pretty like the below:

SELECT
         *
   FROM
         Oh.That.Be.Nice
         

Thanks to the HOBT (Aaron Alton) for blogging about this tool better and sooner than I.

Importing an Excel Sheet to a Database Table.

I've always found the SQL Server Import and Export Wizard a great way to pull data out of Excel into SQL Server. Except for when it doesn't work. It can be a frustrating beast, with all kinds of little idiosyncratic things to consider.

However, it's easily avoided for basic imports from Excel. If you just want a table in a SQL database which looks and feels exactly like the source data, this command is your new friend:

SELECT
         *
         INTO
         dbName.schemaName.tableName
   FROM
         OPENROWSET('Microsoft.ACE.OLEDB.12.0'
            , 'Excel 12.0;Database=excelFilePathName.xlsx'
            , [worksheetName$])

Works a charm, and avoids clicking through a GUI which tends to crash when there's any slight problems.

If you have any "missing provider" problems, which may happen on new 64 bit SQL servers, download the provider from Microsoft Access Database Engine 2010 Redistributable.