Quickly script permissions in SQL 2005+

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.

 

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

SQL 2008 R2 – SSIS Data Export Oddities

I don’t know if anyone else has had these sorts of issues, but we work quite a bit with conversions of various source data into our SQL Server database. I recently was handed a backup from SQL 2008 R2. I figured that wasn’t a huge problem. Restore onto my local test box, use SSIS to push over to a SQL 2005 instance so it can work its way to Production. No big deal, right?

The first time I tried this using the SQL Native Client 10.0 on both sides, I ran into an errors with the mappings. I can’t quite figure that one out because from what I can see, there’s nothing at all in the source database using a feature that would not be available in SQL 2005.

I tried exporting to an MS Access MDB file. No luck from SQL 2008 R2 with the default settings because of an invalid size error on a varchar to longtext conversion.

I was able to successfully export using SNAC from SQL 2008 R2 to a SQL 2008 instance. So from there I thought I could upload directly to the SQL 2005 instance. No such luck using SNAC – again. I was able to export from there to an Access MDB file and pull that into SQL 2005.  I don’t quite get why that worked, but figure I’ve got some odd mapping in the XML files defining the defaults that I’m missing.

 

I was recently asked to repeat this task and figured there had to be a better way. This time I restored the DB to my R2 instance – no issues. I then used the SNAC client to access my R2 instance, but set up an OLEDB connection to my SQL Server 2005 target. For some reason, the mappings are just different enough that this worked with no issues. I was able to transfer directly. I now wish I’d tried that the first time, but I’d already blown a couple of hours on it.

If anyone else has encountered that and knows why SNAC from 2008 R2 doesn’t seem to work directly to SNAC on 2005, I’d love to know the reasons as well. If not and you encounter something similar, maybe trying the OLEDB connections will work for you.