SQL Server - Query Analyzer Shortcut Keys

some very useful Query Analyzer Shortcut Keys in SQL Server .

Keyboard Shortcuts in SQL Query Analyzer


CTRL-SHIFT-F2 -- Clear all bookmarks. CTRL+F2 -- Insert or remove a bookmark (toggle). F2 -- Move to next bookmark. SHIFT+F2 -- Move to previous bookmark. ALT+BREAK -- Cancel a query. CTRL+O -- Connect. CTRL+F4 -- Disconnect. CTRL+F4 -- Disconnect and close child window. ALT+F1 -- Database object information. CTRL+SHIFT+DEL -- Clear the active Editor pane. CTRL+SHIFT+C -- Comment out code. CTRL+C or Ctrl+Insert -- CopyCTRL+X or Shift+Del -- CutSHIFT+TAB -- Decrease indent. CTRL+DEL -- Delete through the end of a line in the Editor pane. CTRL+F -- Find. CTRL+G -- Go to a line number. TAB -- Increase indent. CTRL+SHIFT+L -- Make selection lowercase. CTRL+SHIFT+U -- Make selection uppercase. CTRL+V or Shift+Insert -- Paste. CTRL+SHIFT+R -- Remove comments. F3 -- Repeat last search or find next. CTRL+H -- Replace. CTRL+A -- Select all. CTRL+Z -- Undo. F5 or Ctrl + E -- Execute a query. F1 -- Help for Query Analyzer. SHIFT+F1 -- Help for the selected Transact-SQL statement. F6 -- Switch between query and result panes. Shift+F6 -- Switch panes. CTRL+W -- Window Selector. CTRL+N -- New Query window. F8 -- Object Browser (show/hide). F4 -- Object Search. CTRL+F5 -- Parse the query and check syntax. CTRL+P -- PrintCTRL+D -- Display results in grid format. CTRL+T -- Display results in text format. CTRL+B -- Move the splitter. CTRL+SHIFT+F -- Save results to file. CTRL+R -- Show Results pane (toggle). CTRL+S -- SaveCTRL+SHIFT+INSERT -- Insert a template. CTRL+SHIFT+M -- Replace template parameters. CTRL+L -- Display estimated execution plan. CTRL+K -- Display execution plan (toggle ON/OFF). CTRL+I -- Index Tuning Wizard. CTRL+SHIFT+S -- Show client statistics CTRL+SHIFT+T -- Show server trace. CTRL+U -- Use database

Running SQL Server Agent with a least privilege service account

Problem

For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Solution
As a best practice, SQL Server Agent service account rights should be kept as low as possible to prevent exposing your system to security risks. Making the SQL Server service account an administrator, at either a server level or a domain level, grants too many unneeded privileges and should never be done. Ideally, all the SQL Server services should run from a different account and each account should have exactly the privileges that it needs to do its job and no additional privileges.

During a new installation, SQL Server setup does not default the SQL Server engine service and SQL Server Agent service to any account. The account specification is a required step for these services. Using a local user or domain user that is not a Windows administrator is the best choice.

If the server that is running SQL Server is part of a domain and needs to access domain resources, such as file shares or uses linked server connections to other computers running SQL Server, a domain account should be used. If the server is not part of a domain, a local user that is not a Windows administrator is preferred.

The SQL Server Agent service account requires sysadmin privileges in the SQL Server instance that it is associated with. In this tip I have tried to put forth a solution by running SQL Server agent under group (SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER). This group has all the required privileges and is not part of the administrator group.

NOTE: When you install Microsoft SQL Server to run using a Microsoft Windows NT account, SQL Server sets various Windows user rights and permissions on certain files, folders, and registry keys for that account. If you later change the startup account for the SQL Server Agent service using SQL Server Configuration Manager, SQL Server automatically assigns all the required permissions and Windows user rights to the new account for you, so that you do not have to do anything else.

Steps to SQL Agent Account
STEP 1
Add the account under which you want to run the SQL Server agent service in the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group. (Right click my computer -> Manage -> Local Users and Groups -> Groups). Then find the group, right click on it and select Properties. This group is pre-configured with all the required permissions to run the SQL Agent service. Also, make sure the account you add to thsi group is not a member of the local administrator group. In this example I am adding "Agent test" to this group.


STEP 2
Change the log on account of the SQL Server Agent service in SQL Server Configuration Manager in SQL Server 2005. Use the account that you just added to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group, in my case it is "Agent test". You may also consider doing it through services.msc, but it is recommended to do it using SQL Server Configuration Manager. The reason is, when you install SQL2005, a service master key for encryption is created. This key is then used to encrypt certificates and any other encryption keys. The service master key (SMK) is linked to the service account and changing this account can make the key invalid and then it can’t open the certificates anymore. When you change your service account through SQL Server Configuration Manager, SQL 2005 will take care of creating a SMK. By default, only members of the local administrators group can alter the service account, start, stop, pause, resume or restart a service.


STEP 3
Restart the SQL Server Agent service, so that the new account goes into affect.



STEP 4
You can check in SSMS that SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group is a member of sysadmin role. In SSMS, go to Security -> Sever Roles. Right click on "sysadmin" and select Properties to view the scrreen below. If it is not already there, follow the steps in step 4a.


STEP 4a - add login and role
If this group is not already part of the sysadmin role, follow these steps.

Add the group SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER as a Windows authenticated login in SQL Server


And then assign the sysadmin role to this login.




Now SQL Server agent is running under an account which is not a member of the local administrators group on the server.

Note: there is limitation for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group. Enlisting target servers to a master server may fail with the following error message: "The enlist operation failed." To resolve this error, restart both the SQL Server and the SQL Server Agent services.

ASP Codes to prevent MSSQL Injection Data Cleaner for Database Security

This is a simple input field cleaner to help prevent SQL Injection problems. SQL Injection occurs when a hacker fills out a form on your web site or posts data to a script. If the script which accepts and processes the data reads or writes to a SQL database, the hacker can include SQL commands.

Those commands can insert data to your database and/or can output details about the tables and fields in your database.

There is much more to SQL injection, but this simple script will help to clean submitted data. It is intended for alphanumeric fields. You can clean numeric fields separately by testing for isNumeric and Not isNull.

ASP FUNCTIONS CODES

Function fncInputDataCleaner(StringToClean)

If Len(StringToClean) > 0 Then
For x = 1 to 3
StringToClean = Replace(stringToClean, "'", "''")
StringToClean = Replace(stringToClean, "--", "-")
StringToClean = Replace(stringToClean, ";", " ")
StringToClean = Replace(stringToClean, "<", " ") StringToClean = Replace(stringToClean, ">", " ")
StringToClean = Replace(stringToClean, "%", " ")
StringToClean = Replace(stringToClean, " Next
End If

CleanInputData = stringToClean

End Function



Instructions
Add this function to your Classic ASP script or include it in a file with commonly used functions. Call the function like this:

strMyName = Request("MyName")
strMyName = fncInputDataCleaner(strMyName)

How to find & Stop SQL injection attacks from happening

There’s a lot of stuff out there about SQL injection attacks , but there’s not much that will help you figure out how to stop these attacks from occurring.

First, let’s talk about what a SQL Injection Attack really is. Some people think it’s a virus of sorts, that is “inside your site.” Not the case. These are bot attacks by other virus infected computers. They simply use a brute force approach of scanning URLs that take POST/GET inputs and attempt to send their own data to them.

So, how do you track these down and stop them? For web sites powered by Microsoft’s IIS, here are our suggestions:

1.Look at your IIS logs
Try searching for the word “DECLARE” or “EXECUTE.” If you’ve been hit by an attack, these will more than likely show up in your IIS logs — at least for any attack that was attempted using “GET” posts. If you do find any instances of “DECLARE” or “EXECUTE” these are the pages to start with.

2.Use centralized database connection handling
Simple, make a centralized file (e.g. connection.asp if you are using ASP — see our free example) that handles all of your DB access. This way, it’s easier to make sure that you are SQL encoding your pages. You can easily search queries for “DECLARE” and “EXECUTE” and stop the attacks dead in their tracks.

3.Implement a site wide solution
If you are running your own server, we highly recommend ISAPI_Rewrite from HeliconTech (http://www.helicontech.com/isapi_rewrite). This is an ISAPI filter that allows you to do a variety of things, including scan URL data. This will stop 99% of attacks without changing ANY code on your site!

SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio offers users two keyboard schemes. By default, it uses the Standard scheme, with keyboard shortcuts based on Microsoft Visual Studio. A second scheme, called SQL Server 2000, closely resembles the tools from SQL Server 2000, in particular the keyboard shortcuts from the Query Analyzer. In a few cases, SQL Server Management Studio cannot offer the keyboard shortcuts from Query Analyzer. To change the keyboard scheme or add additional keyboard shortcuts, on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page.

Menu Activation Keyboard Shortcuts



Action
Standard
SQL Server 2000

Move to the SQL Server Management Studio menu bar
ALT
ALT

Activate the menu for a tool component
ALT+HYPHEN
ALT+HYPHEN

Display the context menu
SHIFT+F10
SHIFT+F10

Display the New File dialog box to create a file
CTRL+N
No equivalent

Display the New Project dialog box to create a new project
CTRL+SHIFT+N
CTRL+SHIFT+N

Display the Open File dialog box to open an existing file
CTRL+O
CTRL+SHIFT+INS

Display the Open Project dialog box to open an existing project
CTRL+SHIFT+O
No equivalent

Display the Add New Item dialog box to add a new file to the current project
CTRL+SHIFT+A
No equivalent

Display the Add Existing Item dialog box to add an existing file to the current project
ALT+SHIFT+A
No equivalent

Display the Query Designer
CTRL+SHIFT+Q
CTRL+SHIFT+Q

Close a menu or dialog box, canceling the action
ESC
ESC

MSSQL Servers - Truncating Log Files and Recovering Space

A common issue for users of SQL Server databases is disk space and the size of the physical log file and database. While we’re not going to attempt to make “one size fits all” statement on database maintenance plans, we though it would be helpful to provide a few suggestions that will help you trim the size of your files when you are in a pinch.

Steps to truncating log files and shrinking your database:

1. Get the physical names of your database file (MDF) and log file (LDF):
Run the following system stored procedure:

use
exec sp_helpfile

This command will return a variety of information, including the physical size (the “size” column) and the path and name of your database and log files (in the “filename” column).

Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if filename contains “C:\sqldatabases\yourdatabase_data.mdf” you want to save the string “yourdatabase_data”)

2. Truncate the database and shrink the database
The following set of SQL will shrink your database and “truncate” the log file. File in the parmaters surrounded by <…>. Note that you’ll need the two filename values from step 1.

USE
GO
BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO
DBCC SHRINKFILE (, 1)
GO
exec sp_helpfile

When complete, this script will output the same information as in step 1. Compare the new size with the old.

Comparing Files from Different Folders Using SQL Server and XML

Problem
Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.
To further illustrate the issue I have set this up into three folders:
Source - files to be processed
Destination - files that have been processed
NewFiles - holding folder for new files to be processed after they have been compared
Elaborating the problem in depth, let’s say I have three text files (or any type of file) in the Source folder and the Destination folder contains two files. The problem is to identify differences between the files in the Source and Destination folders and copy all the modified or new files from the Source Folder to the folder NewFiles.
Shown below are the set of files with their modified date in the Source folder:
Shown below are the set of files with their modified date in Destination folder:
Looking at the images above, it is clear that Source Folder contains one extra file ‘text3’ and one modified file ‘text2’.
I want to compare the files in these two folders and copy the modified file (text2) and the new file (text3) into folder NewFiles.
SolutionMy first task was to get all the files from the specified folder. This can be achieved via the ‘DIR’ command of DOS. DOS commands can be executed in SQL Server through xp_cmdshell system stored procedure, but it needs to be configured first.
The below code creates a new stored procedure called "MakeXML" which will create a XML document of all of the files in a folder. The code does a few things which you can either turn on or off as desired.
It first drops stored procedure MakeXML so it can create a new sp with this name
It enables xp_cmshell so we can shell out to DOS to get the folder listing
It does a DIR command against the specified folder with the /O-D parameter to order the list by modified date
It then inserts the data into a temporary table
Deletes any rows from this table that are not files that we want to compare
Parses the modified date
Parses the filename
Then it creates this data as a temporary XML object
IF OBJECT_ID (N'MakeXML') IS NOT NULL DROP PROCEDURE MakeXML GO -------INPUT THE FILEPATH FOLDER AND IT WILL RETURN AN XML CREATE PROCEDURE MakeXML @FilePath VARCHAR(2000), @xmlFile XML OUTPUT AS DECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY,FullPath VARCHAR(2000),ModifiedDate datetime, FileName VARCHAR(100)) DECLARE @CommandLine VARCHAR(4000) ----Configure xp_cmdshell in 2005 -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 -- To update the currently configured value for advanced options. RECONFIGURE -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 -- To update the currently configured value for this feature. RECONFIGURE ----- run dir command on the path specified by ordering on the date modified SELECT @CommandLine =LEFT('dir "' + @FilePath + '" /O-D',10000) ------use xp_cmdshell stored procedure to run dos commands INSERT INTO @MyFiles (FullPath) EXECUTE xp_cmdshell @CommandLine ------Delete all the files that are other than .txt or can specify the file type you want to compare on DELETE FROM @MyFiles WHERE fullpath IS NULL OR Fullpath NOT LIKE '%.txt%' ------Separate the modified date from the fullpath UPDATE @myfiles SET ModifiedDate=SUBSTRING(fullpath,1,PATINDEX('%[AP]M%',fullpath)+1) ------Separate out FileName from fullpath UPDATE @myfiles SET FileName=SUBSTRING(fullpath,40,LEN(fullpath)) -----Make an xml out of separated ModifiedDate,FileName columns and SET the OUTPUT parameter SELECT @xmlFile=(SELECT ModifiedDate,FileName FROM @MyFiles FOR XML PATH('FILE'), ROOT('ROOT') ) GO
For our example the stored procedure would return XML for the Source folder ordered on modified date as shown below.
For our example the stored procedure would return XML for the Destination folder ordered on modified date as shown below.
Now, the next task is to compare the two XML documents from Source and Destination, this is achieved by the following stored procedure.
It takes three input parameters
@Sourcepath which is ‘D:\Source’
@DestPath is ‘D:\Destination’ and
@NewFilesPath as ‘D:\NewFiles’
IF OBJECT_ID (N'CompareXML') IS NOT NULL DROP PROCEDURE CompareXML GO --------COMPARE XML CREATE PROCEDURE CompareXML @SourcePath VARCHAR(255), @DestPath VARCHAR(255), @NewFilesPath VARCHAR(255) AS DECLARE @SourceXML AS XML,@DestXML AS XML ---Call the MakeXML sp to return the XML for Source and Destination Folders EXECUTE MakeXML @SourcePath,@SourceXML OUTPUT EXECUTE MakeXML @DestPath,@DestXML OUTPUT DECLARE @CURSOR_File AS CURSOR ---------Join both the tables retrieved from xml on filename and get all the files having modifieddate mismatch ---------Fetch all the values into a cursor SET @CURSOR_File= CURSOR FOR SELECT A.FNAME FROM ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @SourceXML.nodes('ROOT/FILE') AS X(N) )A INNER JOIN ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @DestXML.nodes('ROOT/FILE') AS X(N) )B ON A.FNAME=B.FNAME AND A.MDATE<>B.MDATE UNION ----------Fetch all the extra files in the source folder SELECT A.FNAME FROM ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @SourceXML.nodes('ROOT/FILE') AS X(N) )A LEFT OUTER JOIN ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @DestXML.nodes('ROOT/FILE') AS X(N) )B ON A.FNAME=B.FNAME WHERE b.fname IS NULL DECLARE @file AS VARCHAR(255) DECLARE @Command AS VARCHAR(500) --------First copy all the files found mismatched on modified date into @NewFilesPath OPEN @CURSOR_File FETCH NEXT FROM @CURSOR_File INTO @file WHILE @@Fetch_Status=0 BEGIN SET @Command='xcopy "'+@SourcePath+'\'+ @file+ '" "' + @NewFilesPath + '"' EXEC xp_cmdshell @Command,no_output FETCH NEXT FROM @CURSOR_File INTO @file END CLOSE @CURSOR_File DEALLOCATE @CURSOR_File GO
The CompareXML stored procedure above will first call the ‘MakeXML’ sp and get the source and destination xml. It will fetch all the values from the nodes of both the XML documents and make two types of comparison on those.
First the comparison would be on the basis of filename having different modified date values.
The second comparison will fetch all the files from Source folder that do not exist in Destination folder.
The output of this is put into a Cursor so that such files are then copied to the ‘NewFiles’ folder one by one using the DOS xcopy command.
After the two stored procedures have been created the code is executed as follows:
EXECUTE CompareXML @SourcePath='D:\Source', @DestPath='D:\Destination', @NewFilesPath='D:\NewFiles'
Based on our example the "NewFiles" folder will now look like this.
The NewFiles folder has the same set of files which had a different modified date and the file which was extra in the Source folder. Now, just copy the files from this NewFiles folder to Destination folder.
The problem of comparing files based on modified date and filename has been drastically reduced compared to doing it manually.