Friday, December 2, 2011

SQL Server Execution Context and Security

What is Execution Context?

Whenever a user connects to SQL Server, it will create the User Session called Execution Content which has the all the user access details  and whenever that user executes a SQL statement, SQL Server uses these details to validate the user permissions before executing  the query.

Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

What is Principals in SQL Server ?

Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

  • Windows level
  • SQL Server level
  • Database level

Windows-level principals

  • Windows Domain Login
  • Windows Local Login

SQL Server-level principal

  • SQL Server Login

Database-level principals

  • Database User
  • Database Role
  • Application Role

What are SQL Server Securable?

SQL Server Securable contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

· Server: It includes server roles, logins, etc.

· Database: It includes database users, application roles, database roles, etc.

· Schema: It includes various database objects such as tables, views, procedures, etc.

The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

What is the use of the Public database role in SQL Server?

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Explain Certificate based SQL Server Logins/Principals?

Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

  • ##MS_SQLResourceSigningCertificate##
  • ##MS_SQLReplicationSigningCertificate##
  • ##MS_SQLAuthenticatorCertificate##
  • ##MS_AgentSigningCertificate##
  • ##MS_PolicyEventProcessingLogin##
  • ##MS_PolicySigningCertificate##
  • ##MS_PolicyTsqlExecutionLogin##

Monday, November 7, 2011

Moving tempdb to a new location

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

  1. Determine the logical file names of the tempdb database and their current location on disk.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
  4: GO
  5: 
       2.   Change the location of each file by using ALTER DATABASE.



  1: USE master;
  2: GO
  3: ALTER DATABASE tempdb 
  4: MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
  5: GO
  6: ALTER DATABASE  tempdb 
  7: MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
  8: GO
  9: 


      3.   Stop and restart the instance of SQL Server.



      4.  Verify the file change.



  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
 


     5.  Delete the tempdb.mdf and templog.ldf files from their original location

Tuesday, November 1, 2011

SQL Server Service Accounts

Service account for

Files and folders

Access

MSSQLServer

Instid\MSSQL\backup

D:\backups

Full control

 

Instid\MSSQL\binn

Read, Execute

 

Instid\MSSQL\data +

G:\MSSQL10.MSSQLSERVER\MSSQL\DATA + index folders

H:\MSSQL10.MSSQLSERVER\MSSQL\LOG\

(include Tempdb directory)

Full control

 

Instid\MSSQL\FTData

Full control

 

Instid\MSSQL\Install

Read, Execute

 

Instid\MSSQL\Log

Full control

 

Instid\MSSQL\Repldata

Full control

 

100\shared

Read, Execute

SQLServerAgent

Instid\MSSQL\binn

Full control

 

Instid\MSSQL\Log

Read, Write, Delete, Execute

 

100\com

Read, Execute

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

 

ServerName\EventLog

Full control

FTS

Instid\MSSQL\FTData

Full control

 

Instid\MSSQL\FTRef

Read, Execute

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

 

Instid\MSSQL\Install

Read, Execute

 

Instid\MSSQL\jobs

Read, Write

SQLServerReportServerUser

Instid\Reporting Services\Log Files

Read, Write, Delete

 

Instid\Reporting Services\ReportServer

Read, Execute

 

Instid\Reportingservices\Reportserver\global.asax

Full control

 

Instid\Reportingservices\Reportserver\Reportserver.config

Read

 

Instid\Reporting Services\reportManager

Read, Execute

 

Instid\Reporting Services\RSTempfiles

Read, Write, Execute, Delete

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

SQL Server Browser

100\shared\ASConfig

Read

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

SQLWriter

N/A (Runs as local system)

 

User

Instid\MSSQL\binn

Read, Execute

 

Instid\Reporting Services\ReportServer

Read, Execute, List Folder Contents

 

Instid\Reportingservices\Reportserver\global.asax

Read

 

Instid\Reporting Services\ReportManager

Read, Execute

 

Instid\Reporting Services\ReportManager\pages

Read

 

Instid\Reporting Services\ReportManager\Styles

Read

 

100\tools

Read, Execute

 

90\tools

Read, Execute

 

80\tools

Read, Execute

 

100\sdk

Read

 

Microsoft SQL Server\100\Setup Bootstrap

Read, Execute

 

+any physical folders the application access

 

 

** this needs the service restart

1. Need separate domain accounts with least priority for each service. Create one each for the following services

a. SQL Server

b. SQL Server agent

c. Reporting services

2. To change the service accounts, password, service startup type, or other properties of any SQL Server–related service, use SQL Server Configuration Manager. For reporting services, use the Reporting Services Configuration Tool.

 

** must grant SQL Service Account account the windows permission "Perform Volume maintenance Tasks"

What is Database Mirroring

In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the primary site and the Secondary Site will be the disaster recovery (DR) site.
The different failure scenarios are as follows:
- In case of a failure at the Primary Site, Node-A would automatically fail-over to Node-B on the Primary Site.
-In case of a Node-B failure, Node-C at Disaster Recovery site will need to be manually, brought online.
-In case of a Node-C failure, Node-C will automatically fail-over to Node-D.
Asynchronous database mirroring is to be implemented to replicate the data across these sites. Fail-over and fail-back is to be performed manually from PRIMARY SITE to Secondary Site.
Database Mirroring Overview
Database mirroring is a software technology built into Microsoft SQL Server providing high availability of application databases. Mirroring is implemented on a per database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.
Database mirroring offers a substantive increase in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. Database mirroring works by maintaining a hot standby server. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.
Pros
- Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimize data loss.
-Database mirroring has automatic server failover and client failover capabilities.
-Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
-Because propagation can be done asynchronously it requires less bandwidth than synchronous method (e.g. host-based replication, clustering). Replication is not limited by geographical distance with current technology.
-Database mirroring supports full-text catalogs.
-Database mirroring does not require special hardware (such as shared storage, heart-beat connection) and cluster hardware, thus potentially has lower infrastructure cost.
Cons
-As the mirroring database is in a constantly restoring state a database snapshot is required to use the database for reporting.
-There is a risk in using automatic fail-over on a SQL Server cluster set-up, as in case of hardware failure on primary node there is a possibility of fail-over occurring to the mirrored database instead of secondary node.
-Additional SQL Server instances may be required for witnessing.

Tuesday, October 11, 2011

How do you specify a different port number in SQL Management Studio?

Specifying a port number on SSMS is pretty easy

Under the server name , specify the server(or ip)\instanceName, ( comma) port

eg: 10.10.10.151\SQL2008,1433

Another way is to setup an alias in Config Manager. Then simply type that alias name when you want to connect. This makes it much easier and is more prefereable when you have to manage several servers/instances and/or servers on multiple ports and/or multiple protocols. Give them friendly names and it becomes much easier to remember them.

Finding the Port Number for a particular SQL Server Instance

One of the developers recently asked me this question: “I have a SQL Server instance running – how can I tell what port it is running on?”.  There are a couple of ways of finding this information.

1) Using the GUI:

If you expand the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance) in the right hand pane you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).  If you double click on TCP/IP the properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.

2)  Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.

3)  Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

4) You can also chose to trace the client to server communication by using Microsoft Network Monitor or a network sniffer utility.  You can also use the TCP/IP netstat utility

Task Manager
View | Select Columns | Check PID | OK
Find sqlservr.exe as the Image Name, note the PID
Command prompt:
netstat -ano | findstr *PID*

The PID is displayed in the far right. Look for the TCP entry. The port will be after the colon (e.g. 0.0.0.0:1433 means the port is 1433).”

Wednesday, August 31, 2011

Table Size in SQL Server | Find Rows and Disk space Usage

There are two ways to find out the table size;

first option will be to run the system stored procedure “sp_spaceUsed”

  1: EXEC sp_SpaceUsed 'table_name'


Option 2 uses the dmv sys.dm_db_partition_stats



  1: SELECT  t.schema_name + '.' + t.table_name AS table_name
  2:        ,t.index_name
  3:        ,SUM(t.used) AS used_in_kb
  4:        ,SUM(t.reserved) AS reserved_in_kb
  5:        ,SUM(t.tbl_rows) AS rows
  6: FROM    ( SELECT    s.Name schema_name
  7:                    ,o.Name table_name
  8:                    ,COALESCE(i.Name, 'HEAP') index_name
  9:                    ,p.used_page_count * 8 used
 10:                    ,p.reserved_page_count * 8 reserved
 11:                    ,p.row_count ind_rows
 12:                    ,CASE WHEN i.index_id IN ( 0, 1 ) THEN p.row_count
 13:                          ELSE 0
 14:                     END tbl_rows
 15:           FROM      sys.dm_db_partition_stats p
 16:           INNER JOIN sys.objects AS o ON o.object_id = p.object_id
 17:           INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
 18:           LEFT OUTER JOIN sys.indexes AS i ON i.object_id = p.object_id
 19:                                               AND i.index_id = p.index_id
 20:           WHERE     o.type_desc = 'USER_TABLE'
 21:                     AND o.is_ms_shipped = 0 ) AS t
 22: GROUP BY t.schema_name
 23:        ,t.table_name
 24:        ,t.index_name
 25: ORDER BY 5 DESC
 26: 
 27: 

Friday, August 5, 2011

Generating Calendar in SQL Server 2008

Here is the simple query that generates a calendar.

  1: DECLARE @startDay DATE = '20110101'
  2:        ,@endDay DATE = '20130101'
  3: 
  4: ;WITH calendar ( [Date], [Year], [Quarter], [Month], [Day], [DayOfWeek], [MonthName], [DayName], [WeekNumber], isWeekDay )
  5: AS ( SELECT @startDay
  6:            ,YEAR(@startDay)
  7:            ,DATEPART(qq, @startDay)
  8:            ,DATEPART(mm, @startDay)
  9:            ,DATEPART(dd, @startDay)
 10:            ,DATEPART(dw, @startDay)
 11:            ,DATENAME(month, @startDay)
 12:            ,DATENAME(dw, @startDay)
 13:            ,DATEPART(wk, @startDay)
 14:            ,CASE WHEN DATEPART(dw, @startDay) IN ( 1, 7 ) THEN 0 ELSE 1 END
 15:    UNION ALL
 16:    SELECT  DATEADD(dd, 1,[date])
 17:            ,YEAR(DATEADD(dd, 1,[date]))
 18:            ,DATEPART(qq, DATEADD(dd, 1,[date]))
 19:            ,DATEPART(mm, DATEADD(dd, 1,[date]))
 20:            ,DATEPART(dd, DATEADD(dd, 1,[date]))
 21:            ,DATEPART(dw, DATEADD(dd, 1,[date]))
 22:            ,DATENAME(month, DATEADD(dd, 1,[date]))
 23:            ,DATENAME(dw, DATEADD(dd, 1,[date]))
 24:            ,DATEPART(wk, DATEADD(dd, 1,[date]))
 25:            ,CASE WHEN DATEPART(dw, DATEADD(dd, 1,[date])) IN ( 1, 7 ) THEN 0 ELSE 1 END
 26:    FROM     calendar
 27:    WHERE    DATEADD(dd, 1,[date]) < @endDay )
 28: SELECT *
 29: FROM   calendar
 30: OPTION  ( MAXRECURSION 1000 )
 31: 
 32: 

Friday, July 29, 2011

Is TRUNCATE TABLE transactional ?

1: CREATE TABLE tTest ( i INT )  
2: INSERT tTest ( i ) VALUES( 1 ) 
3: INSERT tTest ( i ) VALUES( 1 ) 
4: INSERT tTest ( i ) VALUES( 1 ) 
5: INSERT tTest ( i ) VALUES( 1 ) 
6: 
7: BEGIN TRAN 
8:  TRUNCATE TABLE tTest 
9:  SELECT * FROM tTest 
10: ROLLBACK 
11: 
12: SELECT COUNT(*) FROM tTest 
13: DROP TABLE tTest 
14: 
15: 


So, yes - it is subject to the transaction; also - it is a logged operation, its just that the individual rows are not logged "individually" like they would if you used DELETE.

How to remove blank line in SSMS “Find and Replace” dialogue

The answer is simple; in Management Studio go into Find and Replace (ctrl+H ) , in "Find Options" check "Use" and select "Regular Expressions".

Now, in the Find what: box enter the text ^\n

In the Replace with: box enter nothing, please don't take me literally on that and actually type the word nothing, I mean leave the text box empty.

Click Replace All.

Wednesday, July 20, 2011

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, we may need a quick script to find relationship  between tables. Here is the script which helped me a lot, hope this will be useful for you too

  1: SELECT  OBJECT_NAME(rkeyid) ParentTable
  2:        ,OBJECT_NAME(fkeyid) ChildTable
  3:        ,OBJECT_NAME(constid) FKey
  4:        ,c1.name FKey_Col
  5:        ,c2.name Ref_KeyCol
  6: FROM    sys.sysforeignkeys s
  7: INNER JOIN sys.syscolumns c1 ON ( s.fkeyid = c1.id
  8:                                   AND s.fkey = c1.colid
  9:                                 )
 10: INNER JOIN syscolumns c2 ON ( s.rkeyid = c2.id
 11:                               AND s.rkey = c2.colid
 12:                             )
 13: ORDER BY 1,2

TSQL Function to convert decimal to Hex, Octal or any other base

Frequently I see the questions in newsgroups about a function to convert integer value to other bases like base 2 (binary), base 8 (octal) and base 16(hex). Following TSQL function, which was orginally mentioned by Itzik Ben-Gan in his book Inside Microsoft SQL Server 2005:TSQL Querying, provides you the ability to convert a given integer into any target base. I have just updated the function with more meaningful names and added some comments to clear the logic.

 

USE [AdventureWorks]
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[fn_decToBase]')
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[fn_decToBase]
Go
Create function [dbo].[fn_decToBase]
(
    @val as BigInt,
    @base as int
)
returns varchar(63)
as
Begin
    /* Check if we get the valid base */
    If (@val<0) OR (@base < 2) OR (@base> 36) Return Null;
    /* variable to hold final answer */
    Declare @answer as varchar(63);
    /*    Following variable contains all
        possible alpha numeric letters for any base
    */
    Declare @alldigits as varchar(36);
    Set @alldigits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    /*    Set the initial value of
        final answer as empty string
    */
    Set @answer='';
    /* Loop until your source value is greater than 0 */
    While @val>0
    Begin
        Set @answer=Substring(@alldigits,@val % @base + 1,1) + @answer;
        Set @val = @val / @base;
    End
    /* Return the final answer */
    return @answer;
End

TSQL Function to encode HTML Text

While browsing through the SharePoint content database, I found a very useful TSQL utility function which can be used in any application. This function encodes the given html so that it can be safely used in XML tags and other html rendering requirements.

Please note, this function is originally provided in Sharepoint content database and I have just copied it because of its usefulness. PLEASE DO NOT CALL it directly from Sharepoint database as it is not recommended and supported by Microsoft. Just create this function in your own database and modify it as per your requirement.

 

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[fn_HtmlEncode] (
       @Value NVARCHAR(1023)
      ,@PreserveNewLine BIT )
RETURNS NVARCHAR(4000)
AS 
	BEGIN
	DECLARE @Result NVARCHAR(4000)
	SELECT  @Result = @Value
	IF @Result IS NOT NULL AND LEN(@Result) > 0 
	BEGIN
		 SELECT @Result = REPLACE(@Result, N'&', N'&amp;')
		 SELECT @Result = REPLACE(@Result, N'<', N'<')
		 SELECT @Result = REPLACE(@Result, N'>', N'>')
		 SELECT @Result = REPLACE(@Result, N'''', N'&#39;')
		 SELECT @Result = REPLACE(@Result, N'"', N'&quot;')
		 IF @PreserveNewLine = 1 
			SELECT  @Result = REPLACE(@Result, CHAR(10), CHAR(10) + N'<br>')
	END
    RETURN @Result
END