The difference between REVOKE and DENY

If I GRANT SELECT (or UPDATE/INSERT/DELETE) permissions to a user, then they can use those permissions to view data in a table. If I REVOKE the permissions, it’s the same as if the user never had them. They would need to be GRANTed permissions again to see the data.

However, if I DENY them the ability to see data, then that’s different. They can’t see the data, but a subsequent GRANT will not allow them to see the data because the DENY will still be in effect.

It’s a more permanent change, and should be used when you need to be sure that someone cannot see data, not when you are looking to remove permissions. To undo a GRANT, use REVOKE.

Posted in SQL | Leave a comment

The 5 types of programmers

http://stevenbenner.com/2010/07/the-5-types-of-programmers/

Posted in Links | Leave a comment

Finding all Columns of an Index

Copy and paste the script in a database to analize, then replace de variable @tblName with the table name to analize and execute
/************ Identify all columns of all indexes on a table***********/

DECLARE @tblName AS sysname
select @tblName =’TABLE_NAME’
SELECT
sys.objects.object_id, sys.objects.name AS object_name,
sys.indexes.index_id, sys.indexes.name AS index_name,
sys.indexes.type, sys.indexes.type_desc,
partitions.Rows, partitions.SizeMB,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, ‘—‘) AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, ‘—‘) AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN (
SELECT DISTINCT sys.index_columns.object_id, sys.index_columns.index_id
FROM
(
SELECT object_id, column_id
FROM sys.index_columns
WHERE key_ordinal=1 AND is_included_column=0
GROUP BY object_id, column_id
) AS index_columns_dupe
JOIN sys.index_columns ON index_columns_dupe.object_id=sys.index_columns.object_id AND index_columns_dupe.column_id=sys.index_columns.column_id AND sys.index_columns.key_ordinal=1
) AS dupe_index_objects ON sys.objects.object_id=dupe_index_objects.object_id
JOIN sys.indexes ON sys.objects.object_id=sys.indexes.object_id AND dupe_index_objects.index_id=sys.indexes.index_id
JOIN (
SELECT
object_id, index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
(
SELECT sys.columns.name + ‘, ‘
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH(”)
) AS index_columns_key,
(
SELECT sys.columns.name + ‘, ‘
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.object_id=sys.columns.object_id
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH(”)
) AS index_columns_include
) AS Index_Columns
WHERE
sys.objects.name =@tblName
ORDER BY sys.objects.name, sys.indexes.name

Posted in SQL | Tagged , , | Leave a comment

Date and Time Functions in SQL Server 2008

Higher-Precision System Date and Time Functions 

SYSDATETIME

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
Example: 2011-07-26 23:48:50.2038641

SYSDATETIMEOFFSET

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.
Example: 2011-07-26 23:48:50.2038641 +02:00

SYSUTCDATETIME

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. This function returns the Greenwich Mean Time (GMT) or the Coordinated Universal Time (UTC). This time is derived from the current local system time and the time zone settings of the server where SQL Server 2008 is running.
Example: 2011-07-26 21:48:50.2038641

Lower-Precision System Date and Time Functions

CURRENT_TIMESTAMP

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
Example: 2011-07-26 23:53:53.417

GETDATE

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
Example: 2011-07-26 23:54:46.913

GETUTCDATE

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).
Example: 2011-07-26 21:55:13.400

Functions That Get Date and Time Parts

DATENAME

Returns a character string that represents the specified datepart of the specified date.

DATEPART

Returns an integer that represents the specified datepart of the specified date.

DAY

Returns an integer that represents the day day part of the specified date.

MONTH

Returns an integer that represents the month part of a specified date.

YEAR

Returns an integer that represents the year part of a specified date.

Functions That Get Date and Time Difference

DATEDIFF

Returns the number of date or time datepart boundaries that are crossed between two specified dates.

 Functions That Modify Date and Time Values

DATEADD

Returns a new datetime value by adding an interval to the specified datepart of the specified date.

SWITCHOFFSET

SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value. This function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset. It is similar to the TODATETIMEOFFSET function, but instead of only changing the time zone value of a DATETIMEOFFSET data type, this function also changes the time as well. When you use this function the new date, time and time zone will be adjusted to reflect what time it would be in the new time zone based on the date/time being changed. This function is useful when displaying a date that matches a specific time zone even when the base date contains a date that represents a different time zone. This function will be great when trying to support an application that supports users in multiple time zones.

Example:

SELECT SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],
SWITCHOFFSET (SYSDATETIMEOFFSET(), ‘-00:00’) AS [SWITCHOFFSET];

SYSDATETIMEOFFSET                                     SWITCHOFFSET
——————————-                       ————————————-
2010-03-03 22:00:00.00 +05:30                    2010-03-03 16:30:00.2343750 +00:00

TODATETIMEOFFSET

TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

SELECT TODATETIMEOFFSET (GETDATE(),’+05:30′) AS [TODATETIMEOFFSET];

TODATETIMEOFFSET
———————————
2010-03-03 22:00:00.000 +05:30

To use this function you pass two parameters. One parameter is a DATETIMEOFFSET data type value and the other is a time zone value. You might want to do this if you copy a database that stores the local time in a DATETIMEOFFSET column to another system that has another time zone and you want to make the date reflect the time zone of the new system.

Posted in SQL | Tagged , , , , , , , , , , , , , , , , , , | Leave a comment

SQL Server 2008 New DATETIME DataTypes

This article is going to cover the newly introduced data types in SQL Server 2008. The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIME2 and DATETIMEOFFSET.

DATE Data Type

In SQL Server 2005, there is no data specific datatype to store only a Date. You must use the DATETIME or SMALLDATETIME data types. In addition to the date you have entered, you will see a time component, which will appear as 12:00 AM. You then need to format your output to display only the date component. Most of the time you can use the getdate() function to store the current date. If you save the getdate() value in a SMALLDATETIME or DATETIME column in SQL Server 2005, you will also store the current time, which may lead many issues.

In SQL Server 2008 we get a new DATE date type that allows you to store a date without a time. Notice that by default the date is displayed in YYYY-MM-DD format. The range for the DATE datatype is from 0001-01-01 through 9999-12-31. The DATE data type only takes three bytes to store its values as compared to eight bytes for a DATETIME and it accepts date values, consisting of year, month, and day. No parameters are required when declaring a DATE data type.

TIME Datatype

Similar to the Date datatype, there is a TIME datatype in cases where you need to store only the time. Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999. The TIME data type accepts time values, consisting of hours, minutes, and seconds. No parameters are required when declaring a TIME data type. Date values should be specified in the form: HH:MM:SS.

DATETIME2 Data Type

The new DATETIME2 datetype is a date/time datatype with larger fractional seconds and year range than the existing DATETIME datatype. You have the option of specifing the number of fractions that you need. The maximum fraction you can specify is 7 while the minimum fraction is 0. In SQL Server 2008 a new datatype datetime2 can support storing of date values from the year 0001 to 9999.

DATETIMEOFFSET Datatype

Currently when saving the date and time in a column, it will not indicate what time zone that date and time belongs to. This can be especially important when you are dealing with data including several different countries with different time zones. The new datatype DATETIMEOFFSET defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm:

  • hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
  • mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone offset.
  • + (plus) or – (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

The Datetimeoffset data type was introduced in SQL Server 2008 (and .Net Framework 3.5) and is the most advanced datetime date type available. It is made up of a date time and offset relative to the UTC time zone.

The following example compares the results of casting a string to each date and time data type.

SELECT

CAST(getdate() AS time(7)) AS ‘Time’

,CAST(getdate() AS date) AS ‘Date’

,CAST(getdate() AS smalldatetime) AS ‘Smalldatetime’

,CAST(getdate() AS datetime) AS ‘Datetime’

,CAST(getdate() AS datetime2(7)) AS ‘datetime2’

,CAST(getdate() AS datetimeoffset(7)) AS ‘Datetimeoffset’;

Here is the result set.

Data type

Output

Time 23:35:29. 1234567
Date 2011-07-26
Smalldatetime 2011-07-26 23:35:00
Datetime 2011-07-26 23:35:29.123
datetime2 2011-07-26 23:35:29. 1234567
Datetimeoffset 2011-07-26 23:35:29.1234567 +12:15
Posted in SQL | Tagged , , , | Leave a comment

Voicemail hacking and the ‘phone hacking’ scandal

http://blog.mobilephonesecurity.org/2011/07/voicemail-hacking-and-phone-hacking.html

Posted in Links | Leave a comment

Why programmers are not paid in proportion to their productivity

http://www.johndcook.com/blog/2009/12/23/why-programmers-are-not-paid-in-proportion-to-their-productivity/

Posted in Links | Leave a comment