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 ‘ + rl.name + 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.

MSDN – http://msdn.microsoft.com/en-us/library/ms189493.aspx

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

DBCC SHRINKFILE (EMPTYFILE)

 

 

Instant File Initialization

Leave a comment

Should be on all DBA’s initial install/setup checklists…

Kimberly Tripp

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

 

Paul Randal

http://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/

%d bloggers like this: