Script Database or Application Role

Leave a comment

I was surprised that SSMS does not allow the scripting of user defined database/application roles. Looked around the googlesphere and couldn’t find a script that would produce what my purpose required which is migration. The below script will provide you with the GRANT statement for your secured principles. All that remains, is a CREATE ROLE statement preceding the GRANT statements for the environment you’re migrating too. (CREATE ROLE BLUEBOOKapp [YourRoleName])

SELECT ‘GRANT ‘ + dp.permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO ‘ + + CHAR(13) COLLATE Latin1_General_CI_AS

FROM sys.database_permissions dp
JOIN sys.database_principals rl
ON dp.grantee_principal_id = rl.principal_id
WHERE rl.NAME = [YourRoleName]

Example of the T-SQL produced by the script.

Example of the T-SQL produced by the script.


Remove data from data (.mdf) file

Leave a comment

SHRINKDB with the EMPTYFILE argument is a handy command that I for some reason continue to forget about.


EMPTYFILE – “Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.”




Converting Time data type columns in sysschedules table

Leave a comment

Working on a report that will provide a schedule similar to an Outlook calendar for SQL job scheduling.

From Book Online. . .
“Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.”

So, taking into consideration the time is formatted as HHMMSS I used the following script to parse and concatenate the time.

Partial Script
LEFT(RIGHT('0' + CAST(active_start_time AS VARCHAR), 6), 2) + ':' + SUBSTRING(RIGHT('0'
+ CAST(active_start_time AS VARCHAR),6), 3, 2) + ':' + RIGHT(CAST(active_start_time AS VARCHAR), 2) 'Start Time'
FROM dbo.sysjobs
INNER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules
ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

I might post the report once completed. 😉

%d bloggers like this: