Script out all permissions (select/execute etc) from a sql server database

SELECT ‘GRANT ‘ + dp.permission_name collate latin1_general_cs_as
+ ‘ ON ‘ + s.name + ‘.[‘ + o.name + ‘] TO ‘ + dpr.name
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN (‘public’,’guest’)
— AND permission_name=’EXECUTE’

Source: http://stackoverflow.com/questions/1987190/scripting-sql-server-permissions

Pass a value from dynamic sql to the calling scope

–declare a variable which will hold your sql and populate it
–note that because we want to pass @time_inner to scope of the call
we do not need to declare it as a variable
–instead we use it as an output parameter which define later

DECLARE @sql NVARCHAR(MAX) = ‘set @time_inner = getdate()’ –sql must
be unicode hence the N

DECLARE @time_outer DATETIME –if are going to pass the output to the
calling scope we need somewhere to store it – @time_outer

EXEC sp_executesql @sql, N’@time_inner datetime OUTPUT’,@time_inner =
@time_outer OUTPUT
–the second parameter is a list of parameters used by the code you
are executing
–in this case only one which we mark as OUTPUT
–the third parameter is where we assign the output to a variable in
the calling scope, mark it output
–if there are any further outputs they can be assigned in additional parameters

PRINT @time_outer
–proove it has worked by printing the calling scope variable

Creating a comma separated list from a table (XML alternative)

@comma_seperated_list = COALESCE@comma_seperated_list +’,’,”)+field