Wednesday, August 27, 2014

Presenting At OPASS August 2014 Meeting On 8/28

OPASS_newlogo_4Thursday night (8/28) I'll be presenting Inspector Insert And The Case Of The Mistaken IDENTITY for the Orlando SQL User Group (OPASS). Identity values seem like a straightforward topic but as SQL Server has evolved different methods for working with them have been introduced and each has caveats to be aware of. My goal for this presentation is to make you aware of all the different ways - both good and bad - to work with identity values and motivate you to double check your TSQL code when you go back to work on Friday to make sure you don't wind up with a case of mistaken identity.

If you're in the Orlando area and need another reason to come for the evening, here it is: Mark Souza - General Manager, Cloud and Enterprise Engineering Team at Microsoft and otherwise regarded as "Mr. SQL Server" within Microsoft - is coming to Orlando in October for a joint OPASS/MagicPASS meeting and everyone who attends OPASS this month is guaranteed a seat. We've reserved enough space for 200 people in October and we expect to reach capacity so wouldn't it be nice to know you've got a spot?

You can see full details about the August OPASS meeting and RSVP at http://opassaugust2014.eventbrite.com/ - and note that this month the group meets at a new location, ITT Technical Institute in Lake Mary, FL.

Wednesday, June 25, 2014

Thoughts On The 2014 PASS Summit Selections

The session lineup for the 2014 PASS Summit was announced today and I'm honored to be selected as a presenter. This will be my 6th year at the Summit and 5th as a presenter, and every year it's tougher to be picked as the PASS community - and the number of sessions submitted (over 900 this year!) - grows.

The increase in submissions year over year also makes it tougher on the program committee to review, select, and schedule the lineup. The committee is made entirely of volunteers who put a ton of hours into the selections - often a thankless task done behind the scenes - so I want to make sure the acknowledge their efforts. Every year there are people not happy about how the selection process works, that they didn't get selected, etc...and while there are things that can be improved on I don't fault any individual on the committee for having put the time and effort into trying to put together the best lineup of sessions possible.

As I look over the selections there are a few things that stand out worth mentioning. But first, a eye-candy filled analysis of the numbers for this year:

image

image

image

image

image

image

image

What do I take away from looking at the numbers and the list of accepted sessions?

  • Most speakers are scheduled for 1 session. The majority of speakers with 2 (or more) are because they're delivering a full day precon in addition to a regular session.
  • There are some notable names missing from the list of Summit regulars who submitted but were not selected. I'll avoid naming names so I don't play favorites, but I think it shows that you don't get a session just because you're well known in the community. I do hope people who weren't selected are able to find other ways to attend.
  • No 90 minute spotlight presentations this year. First year that's happened?
  • No 500 level precons - maybe in part due to lack of submissions, maybe the ones that were submitted weren't the right fit...still, I'd easily pay for a 500 level DBA precon. Maybe next year?
  • Comparing the list of accepted speakers against the current PASS Board Members shows two sessions accepted, one by Jen Stirrup (a half day session) and one by Adam Jorgensen (a precon with John Welch and Buck Woody). I wish it were none - the Board has plenty to do that week on behalf of PASS without having to worry about delivering a session on top of it. I can understand why Jen wants to make the trip worthwhile since she's travelling to the US from Europe, but the other...
  • 3 Preconference sessions by Pragmatic Works employees are on the list, including one delivered by PASS Executive Vice President, Finance & Governance Adam Jorgensen who is also President and Managing Partner of Pragmatic Works. I know a lot of folks that work at Pragmatic and they're good at what they do, but having 3 precon sessions (where presenters usually make good money from the sales) selected for the same company as one of PASS's execs...smells. I'd like to give PASS the benefit of doubt on this one, but I'll it's very hard to ignore, even if Adam wasn't one of the presenters.
  • The last chart highlights that 39% of the General sessions are BI focused. If the Summit were the only paid conference organized by PASS I'd be OK with that number, but now that we've got the Business Analytics conference I'm wondering if that percentage should be lower. I'd also be curious how many BA conference sessions are repeats at the Summit, though I realize that most of the people who attend Summit probably do not attend the BA conference.

All in all it looks like a good lineup - one that I'm happy to be a part of. I'm looking forward to this year's Summit and I hope that you are, too!

EDIT: Buck Woody is not one of the speakers for Adam's precon as I had initially written.

Tuesday, June 3, 2014

I'm A Candidate For The 2014 PASS Nomination Committee

The 2014 PASS election cycle begins today when voting opens for the Nomination Committee (NomCom) and I'm one of eleven candidates running for three seats on the committee.

Why am I running for the NomCom?
  • It's a chance to use my time and experience to do something good for PASS.
  • This year's NomCom will have the chance to review the election process and recommend changes. I was vocal about this after last year's election and I'm putting my money where my mouth is - I'd like to help improve the process.
  • It's a change for me personally to do something new while doing something to help PASS.
Why am I qualified?

I have been involved with PASS since 2007 at all levels: speaker, chapter leader, regional mentor, SQLSaturday organizer, and I served as a member of the Board in 2012. I have also run for the Board of Directors twice so I have a good understanding of how the NomCom works. My familiarity with PASS from the grassroots all the way up to HQ gives me invaluable experience to draw on when evaluating the candidates running for the Board. I was also called on as an advisor for the original Election Review Committee a few years ago; that means I know how we've gotten to where we are today and can use that to make recommendations for improving future elections.

What can you expect if I'm elected?
  • I'll ask difficult - but relevant - questions. In past NomCom interviews I expected tough questions about strategy, vision for PASS, etc. and being surprised that they weren't asked. I appreciate that Board members are volunteers, but we're asking them to be a part of managing a community with an annual budget of $8 million USD and it's the NomCom's job to ensure each candidate has the right mix of business acumen and connection with the community that make them qualified to run for the Board.
  • I'll be fair to all candidates. No bias because I may know someone or passing judgment because I don't agree with how they think PASS should be run.
  • I'll push for changes to the voting process that reduce the possibility of duplicate votes. The current NomCom process works (for the most part) and doesn't require any major overhaul, but having the possibility exist for duplicate votes is a problem that we cannot ignore and need to fix. I understand that it's not a simple effort so understand that any changes will likely take effect in the 2015 elections.
  • I'll be transparent about the NomCom experience - if elected I commit to blogging once every 2 weeks (or more) about NomCom timelines and progress, lessons learned, and what to expect next. Note that some parts of the NomCom process are under NDA so I can't blog about them, but I will be as open as possible.

Ballots should be mailed today and I encourage you to take a minute to review all 11 candidates along with my application and qualifications before casting your vote.

Thanks for your support!

Tuesday, February 4, 2014

Using sys.objects When Scripting Permissions? You're Missing Something!

I suspect most DBAs have a script in their T-SQL toolbelt which scripts out database permissions for a specific user (or all users). I do, too,  and for years I've used it and always assumed it did the job - until recently when I found that permissions on a certificate weren't in the output for a user I was looking at.

My script, like just about every other one you can find with your search engine of choice, joined sys.database_permissions against sys.objects to get specific permissions on objects (e.g. tables, views, and stored procedures). While sys.objects does contain quite a few things, there are quite a few securables that it does not - specifically:

  • Assemblies
  • Asymmetric Keys
  • Certificates
  • Database Principals (users, application roles, and database roles)
  • Fulltext Catalogs
  • Fulltext Stoplists
  • Schemas
  • Service Broker Message Types
  • Service Broker Remote Service Bindings
  • Service Broker Routes
  • Service Broker Services
  • Service Broker Service Contracts
  • Symmetric Keys
  • Types
  • XML Schema Collections

Fortunately, there are catalog views for everything listed above and all we have to do is join sys.database_permissions against each of them to ensure that we're including all securables.

SMO also has the same issue - the Database.EnumObjectPermissions method does not include permissions on any of the aforementioned securables. That means the only way to retrieve all database permissions with SMO is to iterate through each of the securables listed above and call the object's EnumObjectPermissions method in addition to calling the Database.EnumObjectPermissions method - not pretty or efficient. (For what it's worth, version 1.0.2.1 of SQL Power Doc does NOT do this, meaning some permissions could be excluded from the documentation. I'll have a new beta that fixes this posted within the next week or two)

Below is the updated version of my script, which you can also download from SkyDrive OneDrive.



/*********************************************************************************************
Security - Script Database Level Permissions (2005+)

Description:
   Scripts database permissions (including objects) for a specific user or all database users

   (C) 2014, Kendal Van Dyke (mailto:kendal.vandyke@gmail.com)

Version History:
   v1.00 (2014-02-03)

License:
   This query is free to download and use for personal, educational, and internal
   corporate purposes, provided that this header is preserved. Redistribution or sale
   of this query, in whole or in part, is prohibited without the author's express
   written consent.

Note:
   Execute this query in the database that you wish to script permissions for
  
*********************************************************************************************/
SET NOCOUNT ON

DECLARE    
@user SYSNAME;

-- Set this to NULL for all users
-- SET @user = 'DOMAIN\USER';
SET @user = NULL;

SELECT 'USE ' + QUOTENAME(DB_NAME()) + ';' AS '--Database Context';


-- Logins
SELECT 'CREATE USER [' + usr.name + '] FOR LOGIN [' + susr.name + ']'
      
+ CASE WHEN usr.TYPE = 'S'
              
THEN ' WITH DEFAULT_SCHEMA=[' + usr.default_schema_name + '];'
              
ELSE ';'
        
END AS ' --Logins'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
      
INNER JOIN sys.server_principals AS susr ON usr.sid = susr.sid
WHERE  PERM.permission_name = 'CONNECT'
      
AND susr.sid != 0x01
      
AND usr.NAME = COALESCE(@user, usr.NAME);


-- Role Members
SELECT 'EXEC sp_addrolemember @rolename = ' + QUOTENAME(usr.name, '''')
       +
', @membername = ' + QUOTENAME(usr2.name, '''') + ';' AS '--Role Memberships'
FROM   sys.database_principals AS usr
      
INNER JOIN sys.database_role_members AS rm ON usr.principal_id = rm.role_principal_id
      
INNER JOIN sys.database_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
WHERE  /*usr2.is_fixed_role = 0*/
      
usr2.sid != 0x01
      
AND usr2.NAME = COALESCE(@user, usr2.NAME)
ORDER BY rm.role_principal_id ASC;


-- Object permissions
WITH   cteObject ( [major_id], [name], [class_desc], [class_name] )
        
AS ( SELECT   obj.[object_id] AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.'
                      
+ QUOTENAME(obj.name) AS [major_name] ,
                      
'OBJECT_OR_COLUMN' AS [class_desc] ,
                      
'OBJECT' AS [class_name]
              
FROM        sys.all_objects AS obj
              
UNION ALL
              
SELECT  assembly_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASSEMBLY' AS [class_desc] ,
                      
'ASSEMBLY' AS [class_name]
              
FROM        sys.assemblies
              
UNION ALL
              
SELECT  asymmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASYMMETRIC KEY' AS [class_desc] ,
                      
'ASYMMETRIC_KEY' AS [class_name]
              
FROM        sys.asymmetric_keys
              
UNION ALL
              
SELECT  certificate_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'CERTIFICATE' AS [class_desc] ,
                      
'CERTIFICATE' AS [class_name]
              
FROM        sys.certificates
              
UNION ALL
              
SELECT  principal_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'DATABASE_PRINCIPAL' AS [class_desc] ,
                      
CASE type_desc
                        
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
                        
WHEN 'DATABASE_ROLE' THEN 'ROLE'
                        
ELSE 'USER'
                      
END AS [class_name]
              
FROM        sys.database_principals
              
UNION ALL
              
SELECT  fulltext_catalog_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_CATALOG' AS [class_desc] ,
                      
'FULLTEXT CATALOG' AS [class_name]
              
FROM        sys.fulltext_catalogs
              
UNION ALL
              
SELECT  stoplist_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_STOPLIST' AS [class_desc] ,
                      
'FULLTEXT STOPLIST' AS [class_name]
              
FROM        sys.fulltext_stoplists
              
UNION ALL
              
SELECT  message_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'MESSAGE_TYPE' AS [class_desc] ,
                      
'MESSAGE TYPE' AS [class_name]
              
FROM        sys.service_message_types
              
UNION ALL
              
SELECT  remote_service_binding_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'REMOTE_SERVICE_BINDING' AS [class_desc] ,
                      
'REMOTE SERVICE BINDING' AS [class_name]
              
FROM        sys.remote_service_bindings
              
UNION ALL
              
SELECT  route_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ROUTE' AS [class_desc] ,
                      
'ROUTE' AS [class_name]
              
FROM        sys.routes
              
UNION ALL
              
SELECT  [schema_id] AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SCHEMA' AS [class_desc] ,
                      
'SCHEMA' AS [class_name]
              
FROM        sys.schemas
              
UNION ALL
              
SELECT  service_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE' AS [class_desc] ,
                      
'SERVICE' AS [class_name]
              
FROM        sys.services
              
UNION ALL
              
SELECT  service_contract_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE_CONTRACT' AS [class_desc] ,
                      
'CONTRACT' AS [class_name]
              
FROM        sys.service_contracts
              
UNION ALL
              
SELECT  symmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SYMMETRIC_KEY' AS [class_desc] ,
                      
'SYMMETRIC KEY' AS [class_name]
              
FROM        sys.symmetric_keys
              
UNION ALL
              
SELECT  user_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'TYPE' AS [class_desc] ,
                      
'TYPE' AS [class_name]
              
FROM        sys.types
              
UNION ALL
              
SELECT  xml_collection_id AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
                      
+ QUOTENAME(name) COLLATE database_default ,
                      
'XML_SCHEMA_COLLECTION' AS [class_desc] ,
                      
'XML SCHEMA COLLECTION' AS [class_name]
              
FROM        sys.xml_schema_collections
            
)
  
SELECT  CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
                
ELSE 'GRANT'
          
END + SPACE(1) + PERM.permission_name + ' ON ' + obj.class_name
          
+ ' :: ' + obj.name + CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                                      
ELSE ' (' + QUOTENAME(cl.name) + ')'
                                
END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
          
+ CASE WHEN PERM.state <> 'W' THEN ';'
                  
ELSE ' WITH GRANT OPTION;'
            
END AS '--Object Level Permissions'
  
FROM    sys.database_permissions AS PERM
           INNER JOIN
cteObject AS obj ON PERM.major_id = obj.major_id
                                          
AND PERM.class_desc = obj.class_desc
          
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
          
LEFT JOIN sys.all_columns AS cl ON PERM.minor_id = cl.column_id
                                              
AND PERM.major_id = cl.[object_id]
                                              
AND PERM.class_desc = 'OBJECT_OR_COLUMN'
  
WHERE   usr.is_fixed_role = 0
          
AND usr.sid != 0x01
          
AND usr.NAME = COALESCE(@user, usr.NAME)
  
ORDER BY PERM.permission_name ASC ,
          
PERM.state_desc ASC;


-- Database permissions (non-object specific)
SELECT CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
            
ELSE 'GRANT'
      
END + SPACE(1) + PERM.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
      
+ CASE WHEN PERM.state <> 'W' THEN ';'
              
ELSE ' WITH GRANT OPTION;'
        
END AS ' --Database Level Permissions'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE  PERM.major_id = 0
      
AND usr.NAME != 'dbo'
      
AND usr.NAME = COALESCE(@user, usr.NAME)
ORDER BY PERM.permission_name ASC ,
      
PERM.state_desc ASC;

Thursday, December 5, 2013

How To Get The TableDiff Utility

Tablediff is a command line utility that comes with SQL Server to compare the data and schema in two tables for differences and generate a T-SQL script to synchronize them. It's a rudimentary but useful tool that most people don't know is available and it gets the job done when you don't have a more sophisticated tool like Red Gate's SQL Compare at your disposal.

You can find the tool in a version-specific subfolder of your SQL Server shared features installation directory. For example, look in C:\Program Files\Microsoft SQL Server\110\COM if you've got SQL 2012 installed on the C: drive.

I've always assumed that it's included with Management Studio but recently I found out that's not the case. After testing various installation options I discovered that it's included when the SQL Server Replication features are installed, meaning you also need to install the Database Engine Services to get it:

image

Although not limited to replication scenarios, Microsoft's documentation hints that it was designed with replication in mind:

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.

Perhaps this is the reason most people don't know about it - replication isn't widely used and if you exclude the feature from your database install you won't get tablediff.

Now you know...