Grant permissions to view stored procedure text

Sometimes in a production environment you may want to give developers read access to the text of the stored procedures but not be able to modify or execute them. In SQL Server 2005, you can grant VIEW DEFINITION permissions. Here are different ways to it

To grant view permissions to the entire schema

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]

To grant permissions to specific stored procedure

GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]

To grant view definition on all the stored procedures in the database

--temporary table

DECLARE @tmpTable TABLE (

PK_ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[name] SYSNAME

)

--declare variables

DECLARE @name SYSNAME,

@RowCount INT,

@RecCount INT,

@strSQL VARCHAR(1000)

INSERT INTO @tmpTable ([name])

SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'

-- counters for while

SET @RecCount = (SELECT count(*) FROM @tmpTable)

SET @RowCount = 1

WHILE (@RowCount < @RecCount + 1)

BEGIN

SELECT @name = [name]

FROM @tmpTable

WHERE PK_ID = @RowCount

SET @strSQL = N'Grant VIEW Definition on ' + rtrim(cast(@name AS VARCHAR(128))) + ' to [UserName]'

--Execute the Sql

EXEC(@strSQL)

--Decrement the counter

SET @RowCount = @RowCount + 1

--reset vars, just in case...

SET @name = null

END

SELECT * FROM @tmpTable

Database Snapshots

Database snapshot is a read-only static database which provides the view of data at a point in time. Snapshots use the process of copy on write operation. If a page in the source database is modified for the first time, the original page is copied from the database to the snapshot. Updates after that don’t affect the contents of the snapshot.

Snapshots are very useful to off load historical reports from the OLTP systems. If mirroring is used for high availability, snapshots can be created on the mirror database.

SQL server management studio does not provide an option to create snapshots. The following T-SQL creates a snapshot named ProductSnap_0100 (0100 is the time stamp of when the database is created i.e. 1am) on the Products database with its sparse file named Products_data.ss.

--Drop if the snapshot exists

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ProductSnap_0100')

DROP DATABASE ProductSnap_0100

--Create a snapshot on CORIS database

CREATE DATABASE ProductSnap_0100

ON (NAME = N'Products_Data', FILENAME = N'E:\Snapshot\Products_data_0100.ss')

AS SNAPSHOT OF Products

Here is an excellent blog about how to switch the reports to the new snapshot.

http://blogs.msdn.com/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx

NULLIF - ISNULL - COALESCE

One thing which I always need more information is the NULL, because it is very tricky. Here is just a simple notes….

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

NULLIF is equivalent to the case statement.

Example from BOL:


USE AdventureWorks;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ISNULL (expr_check, expr_replace)

Replaces null value with the replacement value.

USE AdventureWorks;
GO
SELECT ProductID,(ISNULL(Weight, 10))
FROM Production.Product;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COALESCE (expr1...exprn)

Returns first non-null expression among its arguments


USE AdventureWorks;
GO
SELECT ProductID,(COALESCE(Weight, 10))
FROM Production.Product;
GO


Difference between ISNULL and COALESCE

ISNULL takes only 2 parameters. COALESCE can take any number of parameters.

ISNULL returns the datatype of first parameter whereas the return type of COALESCE is determined
by the data precedence rules; explicit casting should be done to get the desired data type.

For example the following two statements give different results

DECLARE @var varchar(2) -- Please use meaningful variable names in real code
SET @var = null
SELECT ISNULL( @var, 'Test' )
SELECT COALESCE( @var, 'Test' )

Checking fragmentation in SQL Server 2005

SQL Server 2005 provides sys.dm_db_index_physical_stats DMV (dynamic management view) which can be used to check index fragmentation.

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), NULL, NULL, NULL , NULL);

If the avg_fragmentation_in_percent > 30 then Rebuild the index

If the avg_fragmentation_in_percent >5 and <30 then Reorganize the index

In a nutshell, rebuilding an index takes more server resources than reorganizing.

Mirroring – Role of Witness Server and Quorum

When a witness server is set, a mirroring session (high safety mode with automatic failover mode) needs quorum to keep the database service. A quorum is the minimal relationship among all connected servers required for synchronous database mirroring session.

Now the next question that comes in mind is about the single point of failure for witness. It is not a single point of failure because if witness fails, principal and mirror will still continue to form a quorum.

Various types of quorum are possible.

Say for example

A = Principal
B = Mirror
C = Witness

Full Quorum – Both partners and witness are included - A∩B∩C

Quorum of partners – Only the two partners are included - A∩B

Quorum with witness and partner – Witness and one of the partners are included C∩(AUB)

Quorum loses sessions

If all the servers are disconnected then the session loses quorum

Now that we know all possible types of quorum, let’s see how each one affects the database and application.

If witness is disconnected when either partner goes down, the database is unavailable since quorum cannot be formed. If the session loses quorum, then the database will not be available until the quorum is re-established.

SQL Server 2005 Database Mirroring

I have been working with database mirroring feature in SQL Server 2K5 and these are some of interesting points I have noted during setting up the process.

1. In SQL Server 2005 SP1 and later versions, database mirroring is fully supported and is made available for general production use. To use database mirroring in a production environment, upgrade to SQL Server 2005 Service Pack 1 (SP1).

Source: http://support.microsoft.com/kb/907741


2. An error occurred while starting mirroringDatabase Mirroring Transport is disabled in the endpoint configuration.(Microsoft SQL Server, Error: 1486)

Solution: Run DBCC TRACEON (1400)

3. The mirror database, "XXX", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Solution: After the full backup on the principal, you need to backup the Transaction log on the principal server. Restore the full backup on mirror in ‘NORECOVERY MODE’ and then restore the Transaction log in ‘NORECOVERY MODE’

4. Can we use database mirroring for the purpose of transferring data from production to test or development server?

In mirroring, if the first server should become unavailable, the system will failover to the mirrored database and SQL Server, helping to ensure high availability. I don't think you want to failover to test or development box.However, you can use Database Mirroring and create a Database Snapshot against the mirror. This Database Snapshot can be periodically dropped and recreated in order to give access to more up to date information. You can use this snapshot for reports or testing.

5. Can we use Database mirroring with Log Shipping?

This MS article explains it clearly

http://msdn2.microsoft.com/en-us/library/ms187016.aspx

T-SQL Convert Hex value to Integer

--Convert Hex value to int
SELECT CONVERT(INT, 0x000FFEFF)

--Convert int value to hex
SELECT CONVERT(VARBINARY(8), 1048319)