2012-05-29

Stopwatch

Some time ago I write about timestamp difference, and I have used it for measuring execution times.
But actually there is a much better way.

By using the Stopwatch class (System.Diagnostics.Stopwatch) I get a measument by a single object in stead of two DateTime (System.DateTime) objects.
Also I can take a look at the stopwatch while it is running by the Elapsed property.
The result given is a TimeSpan (System.TimeSpan) object, and by reading the value with the ToString method it is very usefull.

The Stopwatch is created by calling the static method StartNew():
PS C:\> $mywatch = [System.Diagnostics.Stopwatch]::StartNew()
Stopping the stopwatch is
PS C:\> $mywatch.Stop()
Reading the stopwatch without formatting the answer is also quite simple:
PS C:\> $mywatch.Elapsed

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 14
Milliseconds      : 975
Ticks             : 149755134
TotalDays         : 0,000173327701388889
TotalHours        : 0,00415986483333333
TotalMinutes      : 0,24959189
TotalSeconds      : 14,9755134
TotalMilliseconds : 14975,5134

Reading the stopwatch with standard formatting [HH:mm:ss.ddddddd] gives you the result more readable:
PS C:\> $mywatch.Elapsed.ToString()
00:15:02.9033008

I use it for execution times. On scripts for the entire script and some subtasks.

2012-05-25

Describe database role

When you script out a database role in SQL Server Management Studio (SSMS) or another tool like Red-Gate SQL Compare, you most likely will get just the creation but the rights.
Yesterday I needed the rights for a database role on tables and other database objects. This I got by a small T-SQL script:

:Setvar _RoleName "sqlanchor_user"

DECLARE @script nvarchar(MAX) = N'USE [' + DB_NAME() + N'];' + CHAR(13) + N'GO' + CHAR(13);

SELECT @script += N'CREATE ROLE [' + [db_role].[name] + N'] AUTHORIZATION [' + [owner].[name] + N'];' + CHAR(13)
FROM [sys].[database_principals] AS [db_role]
INNER JOIN [sys].[database_principals] AS [owner]
  ON [db_role].[owning_principal_id] = [owner].[principal_id]
WHERE [db_role].[type] = N'R' AND [db_role].[name] = N'$(_RoleName)';

-- Database object rights
SELECT @script += 'GRANT ' + [db_perm].[permission_name] + ' ON [' + USER_NAME(OBJECTPROPERTY([db_perm].[major_id], 'OwnerId')) + '].[' + OBJECT_NAME([db_perm].[major_id]) + '] TO [' + [db_role].[name] + N'];' + CHAR(13) COLLATE Latin1_General_CI_AS
FROM [sys].[database_permissions] AS [db_perm]
INNER JOIN [sys].[database_principals] AS [db_role]
  ON [db_perm].[grantee_principal_id] = [db_role].[principal_id]
WHERE db_perm.[class] = 1  -- class 1 : Db Object or Column
  AND [db_role].[name] = N'$(_RoleName)';

PRINT @script;


The script only show how to create the database role and assign rights on database objects.
Rights on schemas for example is not by this script. Consider the script as a spike solution, that can be extended.
Take a look in the documentation for "sys.database_permissions" on the possibilities.