[Blog Update] June posts on SQLServerFAQ

I recently worked on a soft-NUMA configuration issue for SQL Server 2008. I decided to document the steps to configure soft-NUMA in a blog post on SQLServerFAQ on MSDN. Here it is:

How to set Soft-NUMA for SQL Server 2008 R2

Happy NUMA configuration! 🙂 It can be quite daunting if you have multiple CPUs.

SQL Server Version Numbers

Understanding SQL version numbers can be a bit confusing at times and remembering the builds an even bigger hassle. I just replied with the following post on MSDN recently. I thought I would convert the post into a blog post for other people to see and use as appropriate.

SQL Server has the following components:

1. Database engine (Engine, Replication, Full-text)

2. SSIS – Integration Services (SQL Server 2005 and above)

3. Reporting Services

4. Tools and Shared Components (Eg. SSMS)

5. Notification Services (SQL Server 2005 and above)

6. Analysis Services

When you install a SQL Server component, the RTM version of that component is installed. The SQL Server release version has a version major number which will stay constant for a release. The 4 digit number that keeps varying is the build number of the current version of your component. This number changes whenever you apply a hotfix/security fix/cumulative update/service pack for that particular component of SQL Server. Very rarely do the build numbers remain constant across patches (Seen to happen in Reporting Services for certain builds).

Version major numbers: This is the first number in the version output:

SQL Server 7.0 – 7.00.XXXX

SQL Server 2000 – 8.00.XXXX

SQL Server 2005 – 9.00.XXXX

SQL Server 2008 – 10.00.XXXX

SQL Server 2008 R2- 10.50.XXXX

The above article has all the build numbers that were released for SQL Server.

Additional reference:

http://support.microsoft.com/kb/321185

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

CU10 is a must for all instances where mirroring is being used

If you happen to use Database Mirroring on SQL Server 2005, then Cumulative Update 10 for SQL Server 2005 Service Pack 3 is a must for your instances. There are 2 database mirroring related fixes in this cumulative package. I would suggest that all SQL Server 2005 instances that have database mirroring enabled need to have this update installed ASAP.

982933 (http://support.microsoft.com/kb/982933/ )
Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers

983480 (http://support.microsoft.com/kb/983480/ )
FIX: Various issues occur when a database is involved in a database mirroring session and in a transactional replication if trace flag 1448 is enabled

983500 (http://support.microsoft.com/kb/983500/ )
FIX: The role switch is delayed when a mirroring automatic failover occurs in SQL Server 2005

Cloning SQL Permissions for users

I have an application which has a bunch of specific permissions granted to some objects within the database. I encountered a problem while trying to migrate this application from one server to another because I needed to recreate these permissions which were added over the period of a year as and when new features were added to the application.

I ended up using sp_helprotect to list out the permissions for the objects that I was interested in. But I still needed to convert the permissions into the necessary GRANT/REVOKE statements. This can be tedious when you are trying to manipulate permissions across databases and multiple objects. What I ended up doing was writing a crude T-SQL script to help me create the necessary GRANT/REVOKE scripts based on the sp_helprotect output.

Note: sp_helprotect does not return information about securables that were introduced in SQL Server 2005. Use sys.database_permissions and fn_builtin_permissions instead.

So, if you have a securable that you are interested in which was introduced in SQL Server 2005, then this is not going to help you.

Note: This script has been tested only for the following objects:

1. Tables

2. Stored Procedures

3. Functions (UDFs/TVFs)

Modification: Aug 8, 2012: Thanks to Graham [blog] for suggesting the corrections.

Script:

set nocount on

-- Declare and initialize local variables
declare @newuser varchar(255)
set @newuser = 'newuser' -- This is the new user that you need to add permissions for

-- Create temp table to store sp_helprotect output

create table #tbl_permissions
(Owner varchar(50),
Object varchar(255),
Grantee varchar(255),
Grantor varchar(255),
ProtectType varchar(50),
Action varchar(50),
Col varchar(50)
)

-- Store sp_helprotect output in a temp table
insert into #tbl_permissions
exec sp_helprotect null,'olduser'

-- Get the necessary GRANT/REVOKE/DENY T-SQL commands
select RTRIM(LTRIM(ProtectType)) + ' ' +
CASE Action WHEN 'CONNECT' THEN Action + ' TO ['+@newuser+']'
ELSE Action + ' ON OBJECT::'+OWNER+'.'+OBJECT+' TO [' +@newuser+']'
END
from #tbl_permissions

-- Drop the temporary table
drop table #tbl_permissions

Now that a bit of my work was easier, I decided to put this script out on the web so that someone else can benefit from the same. I am sure that there are multiple T-SQL scripts which do the same but I found the one below very helpful. I shall try and make it more comprehensive by using sys.database_permissions in a future blog post.

Zeollar presentation on SQL Nexus

Get Microsoft Silverlight

If you have difficulty viewing this session, click here to view it in the original website.

Here is the recording of the webcast that I presented today! Feel free to contact me through this blog or post on the discussion forum on the SQL Nexus discussion forum.

In case you are not able to view the presentation, then you can view it directly on the Zeollar site.