Why (not how) things work

Why (not how) things work

I\’ve always been a bit of a refusenik when it comes to Apple products.

Someone once said, when told that Windows can run on Apple hardware: \”Great ! The reliability of Windows with the value-for-money of Apple !\”. That pretty much summed up my thoughts on the subject, too.

Until recently, that is.

You see, I now have an iPhone. And I\’m not ashamed to say that I love it. Why ? Because it just works. It just does all the things I want it to, and integrates seemlessly with my life right now.

So, why am I writing this, telling you about something you already know / don\’t much care for* ?    *delete as appropriate

Because it\’s GREAT design. I\’m not a fanboi of anything much, but what I do like is when some serious thought has gone into a design, and the design principles are adhered to every step of the way, from conception to delivery and beyond. It appeals to some innate sense of uniformity, some desire for consistancy, and an appreciation of elegant solutions to complex issues.

Douglas Adams once wrote (and I\’m not sure that it\’s true) that Sir Isaac Newton\’s greatest invention was the cat-flap – a door within a door. \”It takes a special kind of genius to render the previously non-existant blindingly obvious.\” And it\’s that attribute that all great design has – the \”why didn\’t we think of that before\” factor.

The point I\’m trying (probably unsucessfully) to make is that phones / toasters / cars / databases / whatever can be great things. All they need is some serious thought as to how they\’re going to work, and someone with the will strong enough to enforce a non-deviation policy from the design principles.

So, next time you get asked for your opinion on how to do something, do your utmost to make it the best it can be from the start. Don\’t compromise your principles (even if that may make you unpopular) – it\’s better to do the right thing first time than spend months patching and propping up a poorly designed solution. People may not explicitly thank you for your trouble, but that\’s better than having to explain why serious production issues were not designed-out of the finished product in the first place.

Back soon…

"Users ?" Check. "Roles ?" Check. "Permissions ?" Check. "Show me ?" …ermmm…

\”Users ?\” Check. \”Roles ?\” Check. \”Permissions ?\” Check. \”Show me ?\” …ermmm…

Security, security, security.

Security is good, right ? Defense in depth, segregated logical access, all that stuff. Lovely.

Sadly, it\’s a bit of a pain when it comes to seeing who is a member of which user-defined database role, and what permissions that role has in any given database.

So, I knocked up the following script because, as I believe I\’ve mentioned before, Books Online is only part of the answer. I thought that if I\’d searched around for this kind of script and

came up blank, then others may well have had the same experience. And what is life for if not for sharing.

Read the comments for what it does (and, more importantly, what it DOESN\’T do). Please note, I didn\’t write all of this script (except the comments). So if you did put it on social.msdn.com, let me know and I\’ll give you a credit for it. Plagiarism is a nasty business.

Back soon….

/**
Script to extract database users, their User-Defined Role membership,
        and object permissions granted to that role.

Will only return results for database roles that have members.

Empty roles are not scripted, because it was hard,
        and if they\’re empty, there\’s no point.

Where Object = NULL and Permisson = NULL, these rows are the user / role mapping
This is there to include fixed database role membership

PLEASE NOTE: the role PUBLIC is never scripted. And in any case, you
        haven\’t applied any permissions to PUBLIC. Have you ?

**/

–User / Role Mapping

SELECT  @@SERVERNAME AS [Server Name],
                DB_NAME() AS [Database],
                a.name AS [Database User],
                b.name AS [RoleName],
                NULL AS [Object],
                NULL AS [Permission],
                GETDATE() AS [Date Extracted]
FROM sys.database_role_members c
INNER JOIN sys.database_principals a
        ON a.principal_id = c.member_principal_id
INNER JOIN sys.database_principals b
        ON b.principal_id = c.role_principal_id

UNION ALL  — OK, not pretty, but at least it\’s Excel friendly.

–Role permissions

SELECT  @@SERVERNAME AS [Server Name],
                DB_NAME() AS [Database],
                b.name AS [Database User],
                a.name AS [Role Name],
                o.NAME AS [Object],
                p.permission_name AS [Permission],
                GETDATE() AS [Date Extracted]
FROM sys.database_role_members c
LEFT OUTER JOIN sys.database_permissions p
        ON c.role_principal_id= p.grantee_principal_id
LEFT OUTER JOIN sys.database_permissions p1
        ON c.member_principal_id = p1.grantee_principal_id
LEFT OUTER JOIN sys.all_objects o
        ON p.major_id = o.OBJECT_ID
INNER JOIN sys.database_principals dp
        ON p.grantee_principal_id = dp.principal_id
INNER JOIN sys.database_principals a
        ON c.role_principal_id = a.principal_id
INNER JOIN sys.database_principals b
        ON c.member_principal_id = b.principal_id
WHERE dp.[type]=\’R\’ –Role
        AND p.major_id > 0  –system objects have a negative major_id

ORDER BY 5 ASC, 4 ASC –because I hate typing column names in an ORDER BY…