Sunday, May 23, 2010

How Many Instances Available in SQL Server

Start -- Run -- Cmd

sqlcmd -L -- displays the list of sql server instances installed in the current machine. If the machine is connected to network, it displays all the instances which are available in the network including express edition versions.

Monday, May 17, 2010

Hide Instance Option in SQL Server

In SQL Server 2005 it is very easy to find in what machines sql server is installed. If you open management studio and browse for server it automatically list all the sql servers. To avoid this and to hide the sql server instance there is an option provided. Follow the below steps to hide instance of an SQL Server
1. open sql server configuration manager
2. right click on protocols under sql server 2005 network configuration
3. select properties
4. on the flags tab -- select hide instance as "Yes"
5. click on Apply and then OK

After that if you search our sql server instance will not visible via network.

Friday, May 7, 2010

What are Pool Resources? -- Understanding Pool Resources

When a machine boots up, the Memory Manager creates two dynamically sized memory pools. These two pools are known as the Paged Pool and NonPaged Pool. Each of these pools start at an initial size that is based upon the amount of physical memory present in the system. Pool memory is a subset of available memory and is not necessarily contiguous. If necessary, these pools can grow up to a maximum size that is determined by the system at boot time.
what distinguishes Paged Pool and NonPaged Pool memory?
The first difference is that Paged Pool is exactly what its name implies - it can be paged out. The NonPaged Pool cannot be paged out. Drivers use the NonPaged Pool for many of their requirements because they can be accessed at any Interrupt Request Level (IRQL). The IRQL defines the hardware priority at which a processor operates at any given time.

Let's examine what type of items reside in each of these pools. Within the NonPaged pool, you would find handles that are used by applications in the user-mode space as well as Kernel-Mode drivers (typically ending in a .sys file extension). Examples of Paged Pool items are Token Objects, Kernel-Mode drivers and the Registry.

Adding Service Account to Lock Pages In Memory for SQL Server

On the Start menu, click Run. In the Open box, type gpedit.msc.

The Group Policy dialog box opens.

On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

Expand Security Settings, and then expand Local Policies.

Select the User Rights Assignment folder.

The policies will be displayed in the details pane.

In the pane, double-click Lock pages in memory.

In the Local Security Policy Setting dialog box, click Add.

In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

Wednesday, May 5, 2010

xp_cmdshell without sysadmin privileges

1. Create a window login (for ex : Test\kalyan)
2. Create window authentican login in the SQL Server
3. Execute the following command in the master database
EXEC sp_xp_cmdshell_proxy_account 'Test\kalyan', 'k@!y@n'
4. After than login into SQL Server using windows authentication and try execute exec xp_cmdshell 'dir'