Tuesday, September 23, 2014

PASS Chapter Leaders - Remember To Talk About Elections With Your Chapters!

Last week before our monthly MagicPASS meeting I went through my usual routine of grabbing the PASS chapter deck and noticed that there are no slides mentioning the upcoming Board of Directors elections. I'm surprised because the elections are a great discussion topic for people who might not otherwise be exposed to how PASS is run, or for that matter might not think they're qualified or informed enough to vote.

I took a few minutes to add a slide to the deck and talked about it during our meeting - why it's important to vote, the impact Board members have on the organization (and ultimately them), where to start to learn more about candidates, etc. Will someone who wouldn't have previously considered voting do so now? That's my hope, anyways, and I know that there's a better chance of that happening than by not saying anything at all.

Chapter leaders, you can grab the September MagicPASS slides from OneDrive. Elections are on slide #8 but feel free to use any or all of what I added as examples for things to include in your own monthly decks. If your meeting has already happened it's not too late to remind your members via LinkedIn, Facebook, or your mailing lists that voting opens tomorrow, Sept 24.


2014 Elections

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:








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 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+)

   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)

   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.

   Execute this query in the database that you wish to script permissions for

@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
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
SELECT  assembly_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'ASSEMBLY' AS [class_desc] ,
'ASSEMBLY' AS [class_name]
FROM        sys.assemblies
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
SELECT  certificate_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'CERTIFICATE' AS [class_desc] ,
'CERTIFICATE' AS [class_name]
FROM        sys.certificates
SELECT  principal_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'DATABASE_PRINCIPAL' AS [class_desc] ,
CASE type_desc
END AS [class_name]
FROM        sys.database_principals
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
SELECT  stoplist_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'FULLTEXT_STOPLIST' AS [class_desc] ,
FROM        sys.fulltext_stoplists
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
SELECT  remote_service_binding_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
FROM        sys.remote_service_bindings
SELECT  route_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'ROUTE' AS [class_desc] ,
'ROUTE' AS [class_name]
FROM        sys.routes
SELECT  [schema_id] AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'SCHEMA' AS [class_desc] ,
'SCHEMA' AS [class_name]
FROM        sys.schemas
SELECT  service_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'SERVICE' AS [class_desc] ,
'SERVICE' AS [class_name]
FROM        sys.services
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
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
SELECT  user_type_id AS [major_id] ,
QUOTENAME(name) COLLATE database_default ,
'TYPE' AS [class_desc] ,
'TYPE' AS [class_name]
FROM        sys.types
SELECT  xml_collection_id AS [major_id] ,
QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
+ QUOTENAME(name) COLLATE database_default ,
FROM        sys.xml_schema_collections
SELECT  CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
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 ';'
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]
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
END + SPACE(1) + PERM.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN PERM.state <> 'W' THEN ';'
END AS ' --Database Level Permissions'
FROM   sys.database_permissions AS PERM
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;