Change Database Compatibility Level

Recently I came across a database environment that had SQL Server 2005 (don't ask me why), but the databases (there were about 50 databases) were running at SQL Server 2000 compatibility level. After ensuring that nothing will break if I changed a database's compatibility level to 2005, I decided to go ahead and run a script to change all databases' compatibility level to 2005.

Below is a script that you can run in your environment.

DECLARE  @tmpTable TABLE (Name varchar(50),compLevel INT,StateDesc varchar(50))

INSERT INTO @tmpTable(Name,CompLevel,StateDesc)
SELECT Name,Compatibility_level,State_Desc FROM sys.databases 

DECLARE @DBName varchar(50)
DECLARE curDBName CURSOR FOR
    SELECT Name FROM @tmpTable WHERE StateDesc='ONLINE'
OPEN curDBName
FETCH NEXT FROM curDBName INTO @DBName
WHILE @@FETCH_STATUS=0
    BEGIN
        DECLARE @compLevel INT
        SELECT @compLevel=compLevel FROM @tmpTable WHERE Name=@DBName
        If @compLevel=80
            BEGIN
            Exec sp_dbcmptlevel @DBName,90
            END
        FETCH NEXT FROM curDBName INTO @DBName
    END
CLOSE curDBName
DEALLOCATE curDBName

You can change the version to move from 2000 to 2008 or 2005 to 2008, but before you do that, make sure that your database will not break. One way to check would be to create database scripts by logging into Management Studio > Right Click on Database > Tasks > Generate Scripts. Make sure you select all objects i.e. tables, views, stored procedures, functions etc. and then save scripts. Run these scripts to create an empty database in the SQL environment you are upgrading your database to. If there are no errors, there is a good chance that your production database will work fine.

Thank you.

SQL Server - Grant Permission to Objects

Suppose you are using integrated security to login to SQL Management Studio and create stored procedures, views etc. But in production environment, the application connects to SQL Server via a SQL Server account that belongs to public role.

Unless you have assigned execute permissions to Public security group (server role), you will get a nasty permission denied error message. While manually assigning proper security rights to a single stored procedure isn't a problem, it becomes a hassle when you have several stored procedures to take care of.

This is a simple script that will recursively scan all your stored procedures and grant execute permissions to public.

--Grant permissions on Procedures
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='P'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT EXECUTE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

You can grant SELECT/UPDATE/DELETE permissions on tables and views

Tables

--Grant permissions on Tables
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='U'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Views

--Grant permissions on Views
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='V'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Thank You.

Using Cursors in SQL Server

Cursors are generally not recommended and are slower compared to other methods of reading data. But there are situations where you must use cursors. I have found them to be useful when I am trying to run a SELECT on a table in Query Analyzer and performance is not a concern.

For example, consider a Stores table that has one to many relationship with another table called StoreHours. The store closes at different time depending on weekday. Also stores close at different times in different regions. You need to run a query to determine the latest closing time for each store. You can easily achieve this by running a cursor.

As I mentioned earlier, cursors are rather slow, but you can increase their performance using FAST_FORWARD and READ_ONLY hints. Also remember to always deallocate and close cursor when done.

Let's use the cursor to select latest closing time for each store, insert them into a temporary table and then select the records from the temp table.

DECLARE @tmpStoreClosing(StoreID INT, ClosingTime DateTime)
DECLARE @StoreID INT
DECLARE curStore CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT StoreID FROM Stores
OPEN curStore
FETCH NEXT FROM curStore INTO @StoreID
WHILE @@FETCH_STATUS=0
    BEGIN
         INSERT INTO @tmpStoreClosing(StoreID,ClosingTime)
         SELECT TOP 1 @StoreID,ClosingTime FROM StoreHours 
         WHERE StoreID=@StoreID ORDER BY ClosingTime DESC
         FETCH NEXT FROM curStore INTO @StoreID
    END
CLOSE curStore
DEALLOCATE curStore

SELECT StoreID,ClosingTime FROM @tmpStoreClosing

You can also improve the performance using INSENSITIVE or STATIC keyword. When this keyword is used, cursor copies the data into tempDB and runs the select from there. The drawback of this approach is that any changes made to the data are not reflected.

FAST_FORWARD is same as FORWARD_ONLY and READ_ONLY combined. Data can only be read and in forward only manner. When using this flag, you can only use FETCH NEXT.

There are ways to achieve the same thing using a WHILE loop, which doesn't have the drawbacks of a cursor. We will discuss using a WHILE loop to achieve the same objective in future post.

Cursors should be avoided and if used they should be used primarily for reading the data in forward only manner.

Thank you.