Archive

Archive for November, 2011

SQL Server 2012: Service Accounts Changes

22/11/2011 Leave a comment

After SQL Server 2012 RC0 installation I noticed a few changes in SQL Server services account configuration. In previous versions (SQL Server 2005 and 2008/R2) during stand-alone SQL Server installation, local Windows group is created and service account for services or service SID is added as a member of the service group.

In SQL Server 2012 that is changed.  Local windows groups are created only for SQL Server Browser (SQLServer2005SQLBrowserUser$ComputerName), SSAS (SQLServerMSASUser$ComputerName$MSSQLSERVER) and PowerPivot for SharePoint (SQLServerMSASUser$ComputerName$PowerPivot).
Permissions and ACL for all other services (SQL Server Engine, SQL Server Agent, Full-text search…) are set to the per-service SIDs. 

Other interesting thing is number of created logins in the SQL Server Database Engine after SQL Server 2012 RC0 installation. Regarding the service SID for SQL Server Engine and SQL Server Agent there is no difference between SQL Server 2012 RC0 and SQL Server 2008. The service SID for SQL Server Engine and SQL Server Agent is added as a login to the sysadmin server role. 
In SQL Server 2012 RC0, logins are created for the service SID of the SQL Server VSS Writer (NT SERVICE\SQLWriter) and the Windows WMI provider (NT SERVICE\Winmgmt). These service SID logins are added to the sysadmin fixed server role. 

There are other changes and news. Windows 7 and Windows Server 2008 R2 have two new types of service accounts: managed service accounts (MSA) and virtual accounts.
I wrote a few changes I noticed after installation and after I read documentation.  It’s good to know what changes are done with SQL Server services accounts and I recommend that you read the BOL article: “Configure Windows Service Accounts and Permissions”.

DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it?

11/11/2011 Leave a comment

Yesterday I run into interesting situation with DBCC CHECKDB WITH ESTIMATEONLY command. Later I find out that it’s a known bug in SQL Server 2008 R2, but I experienced it for the first time and I think that it’s a good topic for my post. Here is the story.
Yesterday, after I restored database from the backup I decided to run DBCC CHECKDB on it. Data file size of tempdb database was 2.5GB. I know that DBCC CHECKDB is a resource-intensive task and it also require space in tempdb. I wanted to be sure that tempdb size is appropriate to accommodate the CHECKDB requirements so I run DBCC CHECKDB WITH ESTIMATEONLY.

DBCC CHECKDB(DatabaseName) WITH ESTIMATEONLY

Here is the output:
Estimated TEMPDB space needed for CHECKALLOC (KB): 40443
Estimated TEMPDB space needed for CHECKTABLES (KB): 13619

It’s ~ 53MB. I expected larger values because database occupies 28GB of space. But I thought that tempdb with 2.5GB of data file size is enough because there will be no other workload on the server.

I run DBCC CHECKDB(DatabaseName) and command completed successfully in about 11 minutes. Later I noticed that the size of tempdb data file is 3GB and data file auto growth occurred around the time of the DBCC CHECKDB execution.
That was very strange. Estimated tempdb space was about 53MB but it used more than 2.5GB of tempdb space. It is almost 50 times more than estimated space needed and it can lead to problem especially on very large databases. I decided to run DBCC CHECKDB command again and see usage of tempdb database.
Here is the script:

DECLARE @read BIGINT
DECLARE @write  BIGINT

SELECT  @read = SUM(fs.num_of_bytes_read),
@write = SUM(fs.num_of_bytes_written)
FROM    tempdb.sys.database_files AS df
INNER JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

DBCC CHECKDB(DatabaseName)

SELECT  (SUM(fs.num_of_bytes_read) - @read) / 1024. / 1024. AS tempdb_read_MB,
(SUM(fs.num_of_bytes_written) - @write) / 1024. / 1024. AS tempdb_write_MB,
(SELECT  internal_objects_alloc_page_count * 8. / 1024
FROM    sys.dm_db_task_space_usage
WHERE   session_id = @@SPID) AS internal_objects_alloc_size_MB
FROM    tempdb.sys.database_files AS df
JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

Results:
tempdb_read_MB         tempdb_write_MB        internal_objects_alloc_size_MB
3192.21093750000       3192.60156250000       3194.562500

From results, we can see that more than 3.1GB of data are written and read during DBCC CHECKDB execution.
I executed few more tests with different databases to see a difference between estimated tempdb space and the real usage during CHECKDB operation and only first database had issue with it.

I googled and found the excellent blog post by Paul Randal (blog|twitter): How does DBCC CHECKDB WITH ESTIMATEONLY work? At the end of the post Paul wrote:

 “PS Beware also that I’ve heard of several cases of a bug in SQL Server 2008 R2 where the output is incorrectly very low. The dev team is aware of this issue and are working on it.”

Be careful if you need to rely on DBCC CHECKDB WITH ESTIMATEONLY output.  You can be surprised, especially if you are working with large or very large databases.

User-defined Server Roles in SQL Server 2012

07/11/2011 Leave a comment

In SQL Server 2008 R2 we can create database role, assign appropriate permission to it and then add users or groups as a member of database role. It works well for database level permissions. For server level permissions, we can only add user in one or more predefined fixed server roles or assign individual permissions to it. Usually, my users don’t need all permissions from the fixed server role and I like to add least privilege to my users, especially on server level,  so I end up by giving individual server level permissions to individual users. That will change in SQL Server 2012.
User-defined server role is one of the new security enhancements in SQL Server 2012. It can be created and managed using the CREATE SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE commands, or we can use SSMS UI.  Only server scope permission can be added to server roles. To see all built-in permissions that can be assigned to server role we can run:

SELECT * FROM sys.fn_builtin_permissions(default)
WHERE class_desc in ('SERVER', 'ENDPOINT','LOGIN','AVAILABILITY GROUP','SERVER ROLE')
ORDER BY class_desc

I decided to test new feature by creating server role for a performance checking. It should allow the user to do some server checking, running DMVs, reading definitions, running server side traces and extended event sessions. Next script will create server role PerfRole with required permissions and will add PerfLogin as a member of the role.

USE [master]
GO
CREATE SERVER ROLE [PerfRole] AUTHORIZATION [sa]
GO
GRANT CONNECT SQL TO [PerfRole]
GRANT VIEW ANY DATABASE TO [PerfRole]
GRANT VIEW ANY DEFINITION TO [PerfRole]
GRANT VIEW SERVER STATE TO [PerfRole]
GRANT ALTER ANY EVENT SESSION TO [PerfRole]
GRANT ALTER TRACE TO [PerfRole]
GO

ALTER SERVER ROLE [PerfRole] ADD MEMBER [PerfLogin]
GO

I log in as a PerfLogin and everything work fine. I can run DMVs, server side traces and SQL profiler. I can create and manage Extended Event session. I can see what was happening on the server without having to be in the sysadmin role or have individual permissions assigned to my login.

As you know, member of fixed server role can add members to the same role, but member of a user server role will not have permission to add members to the same role.  Login must have, at least, ALTER permission on that role to add members to the role.

GRANT ALTER ON SERVER ROLE::[PerfRole] TO [PerfLogin]
GO

If you want that all members of a user defined role can add members to the role you can give ALTER permission to the role.

GRANT ALTER ON SERVER ROLE::[PerfRole] TO [PerfRole]
GO

User-defined server roles are nice addition to SQL Server 2012. These will simplify administration of instance-level rights and help increase the security of SQL Server instances.