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...

Tuesday, October 22, 2013

Windows 8.1 Upgrade Deletes VirtualBox Host-Only Network Adapter

Windows 8.1 was released to the Windows Store last week and aside from having to download the same 3.9 GB update on each machine I've installed it on it's been a relatively easy and smooth update. I did, however, run into one small problem related to Oracle VirtualBox. When you install VirtualBox a "Host-Only" network adapter is installed in Windows to allow guest VMs to communicate with the Windows host, as the screenshot below shows:

Network Connections Window

Unfortunately the Windows 8.1 upgrade process deletes the network adapter. After the upgrade you'll be faced with a warning message in the VM properties window and your VM won't start.

VirtualBox VM Settings Window

The fix is easy - just reinstall VirtualBox. It's not a bad idea, actually, as at the same time as Windows 8.1 went live so did VirtualBox Version 4.3. The latest version has a few nice improvements that make it worth the upgrade, including: Multi-touch support, virtual USB webcam, a new NAT adapter that enables hosts and guests to communicate without requiring a host-only adapter, and the ability to record the entire VM session to video.

As always, if you upgrade VirtualBox it's a good idea to upgrade the guest additions as well.

Thursday, October 17, 2013

SQL Power Doc Update - Version 1.0.2.1

I'm at the 2013 PASS Summit in Charlotte, NC, this week, and presented a session about documenting your databases using SQL Power Doc. I'm blown away that it's been downloaded over 3,000 times since version 1.0 and have received some really great feedback from people using it in their environments.

To celebrate Summit week, the presentation, and the download milestone I'm pleased to announce that version 1.0.2.1 is now available! Full release notes are available here, and you can download the latest version here.

In addition to lots of big fixes, this release includes three significant features:

  • I've corrected an issue with connectivity to named instances running locally using the Shared memory protocol. This has been a big annoyance since a lot of people are trying out SQL Power Doc on their local machines where they're running named instances of Developer Edition. Out of the box Developer edition has only shared memory enabled so this is nice to get this fixed.
  • I've added columns to the tabs for Server Permissions, Server Roles, Database Permissions, and Database Roles in the Database Engine workbook with the TSQL syntax to recreate the permissions/role associations.
  • I've added Windows API calls to resolve the members of AD Groups used for Server logins. This is especially useful for auditing because you can now see the actual members of the group at the time the server was documented.

Big thanks for the 3,000+ people who have downloaded SQL Power Doc so far and I hope you enjoy the new version!

Friday, October 11, 2013

What Does The PASS Community Mean To You?

In less than 72 hours I'll be in Charlotte with over 3,000 of my closest SQL Server friends for the 2013 PASS Summit. This year I'm excited to be co-hosting two "Get To Know Your Community" sessions with my friend Kevin Kline (Blog | @kekline) on Tuesday afternoon before the opening reception. Geared towards folks who are new to the PASS Community, we're going to be covering the many ways attendees can stay connected with the PASS (and SQL Server) community once the Summit is over.

We've been working for the last few weeks on the slides and feel pretty comfortable with the material, but it still feels like we're missing something. That's where you come in...

Imagine you're introducing someone to the PASS Community for the first time. You've got a sentence or two to tell them what makes it great and hook them or they lose interest and move on. What do you say?

Leave a comment below and let me know why you think the PASS community rocks. I'll use the (SFW) comments in our slide deck Tuesday afternoon. This is your chance for 15 seconds of fame - don't let it PASS you by. (har-har!)

See you at the Summit next week!