How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned 
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

How to create Custom Database Role and Manage its permissions

Custom database roles is the best way to manage object permission for the given database in structured way. Following script will demonstrate the steps necessary to create a new role and manage object permissions through it

--- Step 1 -------- Create Role ------------
USE [MyTestDB]
GO
CREATE ROLE [TestRole1] AUTHORIZATION [dbo]
Go

--- Step 2 -------- Assign Permissions to role  ------------
USE [MyTestDB]
GO
GRANT ALTER,EXECUTE, SELECT,INSERT,UPDATE,DELETE
ON SCHEMA ::dbo
TO TestRole1
Go

USE [MyTestDB]
GO
GRANT BACKUP DATABASE,BACKUP LOG To [TestRole1]
Go

--- Step 3 ---- Add user to our role --------------
USE [MyTestDB]
GO
EXEC sp_addrolemember N'TestRole1', N'SPUser7'
GO

-- ******  Create user *********************

--- Step 1 ---- Create Windows Login For SQL Server ------------
USE [master]
GO
CREATE LOGIN [TestDomain\SPUser7] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTestDB]
GO

--- Step 2 ---- Create Database User for our database --------------
USE [MyTestDB]
GO
CREATE USER [SPUser7] FOR LOGIN [TestDomain\SPUser7]
GO

How to use more than 4GB RAM on SQL Server?

SQL Server is an highly optimized and well tuned piece of code. It try to utilise Operating System resources as optimum as possible. Due to this, most DBA think that if they will increase RAM on the server then SQL Server will automatically start using it, right? Wrong, SQL Server can only use RAM more than 4GB if and only if,

• You are running Enterprise Editions of Windows 2003 Servers orAdvanced/Datacenter version of Windows 2000
• You have enabled Operating System to use the extra RAM you have installed. Please check here for the details and how to do it.
• You are not running Express or Workgroup version of SQL Server. Please check here for further details about limitations in different versions of SQL Server
• you have enabled awe enabled option and set max server memory to the maximum memory you can allocate to SQL Server. This is applicatiable for 32 but versions of OS and not required in 64 bit version of Windows servers. Please check here for further details and how to configure the memoryfor SQL Server

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.

Here it is:

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

</code>

<code>/* Return the final answer */
return @answer;
End

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

From

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

Order by Parent_Table,Child_Table

Two quickest ways to find file information of a given database

If you want to check the name of the files and their respective sizes for the given database then following are two quick tsql scripts:

1. Use sp_helpfile

This stored procedure returns the file information for the selected database e.g.

USE AdventureWorks;

GO

EXEC sp_helpfile;

GO

It will return the resultset similar to following

By default, this view returns a column called size but this is actually the size in 8KB pages therefore, to get the exact size in KB, as returned by sp_helpfile, multiply it by 8 as mentioned in above query.

Resource Governor in SQL Server 2008 – Beware of catches

Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization.  Following are some points to remember for better utilization of this feature:

1. Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.
2. Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.
3. If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

Welcome to SQL Server 2008

Finally, wait is over and just one day before start of 2008 Olympic, Microsoft has released SQL Server 2008. It is a big day as many of us were waiting for the final release to start taking architectural decision based on new technologies offered by SQL Server 2008. The 180 day trial version is available here
To start with, I will recommend to watch following videos covering disfferent aspect of technologies used in SQL Server 2008
SQL Server 2008 Demos and Videos
You can also check these Web Casts

In my next articles, I will try to cover some fundamental technological advancements in this version of SQL Server 2008.

SQL Server Panel Interview

I participated in a panel interview of SQL Server DBAs conducted by OdinJobs This interview an email interview and following other industry experts also interviewed (listed in ascending order)

• Andrew Fryer
• Brent Ozar
• Brian Kelly
• Cristian Lefter
• Chris Shaw
• Denny Cherry
• Pinal Dave

This interview covers what issues faced by SQL Server DBAs, new features of SQL Server 2008 etc. It is available at

• Part 1: MS SQL beats the “ACID” test for Database Technology in Features, Ease & Cost
• Part 2: Stress, Pet Peeves, Roles and Responsibilities of a SQL Server DBA/Developer
• Part 3: Becoming a SQL Server MVP and other Career Choices

I will recommend everyone to read this as it gives you a good idea about genearl thoughts of DBAs

Multiple Inserts in one statement – Row Constructor in SQL Server 2008

Don’t you feel boring sometimes when you have to write an script to insert multiple rows in a table and you do not have any other choice except writing multiple insert statements? In one of my previous posts I showed a way to insert multiple rows by using UNION ALL but wait! there is another way available in SQL Server 2008 called Row Constructor which is an ANSI terminology for pseudo table of rows.
It is actually a way to provide a set of row values in one statement. Following example will help you understand better. Please note that this example can work only on SQL Server 2008 or above:

–Switch to tempdb
Use tempdb
Go

–Create a test table in temdb
Create table tblCountries (id int, country varchar(50)
Go

–Inserting multiple values
Insert into tblCountries (id,country)
Values
(1,‘USA’), –Row 1
(2,‘UK’), –Row 2
(3,‘France’) –Row 3

–Now select, you will get 3 rows
Select * from tblCountries