Change the properties of a SQL Server login account

Enabling a disabled login
The following example enables the login Emma.

ALTER LOGIN Emma ENABLE

Changing the password of a login
The following example changes the password of login Emma to 123EEDWQxz829.

ALTER LOGIN Emma WITH PASSWORD = '123EEDWQxz829'


Changing the name of a login
The following example changes the name of login Emma to DavidB.

ALTER LOGIN Emma WITH NAME = DavidB

Changing the Name of a Database User

The following piece of T-SQL changes the name of a database user named james to jesse.


Use AdventureWorks;
Alter USER Lara WITH NAME = jesse
GO

Display Identity Columns in all Tables

This piece of T-SQL displays all the identity columns in all the tables in a Database.

Use Adventureworks
Go
Select o.name, i.name As 'ColumnName'
From sys.identity_columns i, sys.objects o
Where i.object_id = o.object_id
Go

Dynamically Passing Parameters at run time to a Package

Imagine developing an SSIS package and using variables to dynamically pass parameters to the package at runtime. Your package works perfectly on SSIS development environment.
Now you want to schedule this package as a job and wondering the hassles of opening and modifying the package on SSIS development environment each time you want the package to run with different parameters.

Well with SSIS, you can dynamically pass a parameter to a package at run time. The example below will demonstrate how to schedule a package to run as a job by dynamically assigning a parameter at run time.

Connect to your SQL Server.
Click SQL Server Agent to expand it.
Right click jobs, Select New job.
On the General Page, Enter job name.
Click Step, on the Steps page, Click New button.
On the General page of select a step page window, Enter the step name.
On the Type heading, Select SQL Server Integration Services Package from the drop down box.
ON Package source, select where the package is stored. For this demonstration I will select SQL Server.
ON Serve heading: select the server.
Choose your authentication mode.
On Package heading, select the package.



Now to dynamically pass parameters at run time to this package,
Click the Set values tab.
On the Property Path column, type this: \Package.Variables[Users::myvariable].value
On the Value column, type the value of the parameter you want to pass. In this demonstration, I typed "November" for the value column.



Click Ok and Ok again.

Note:
myvariable is the name of the varibale that i declared during the development of the package on BIDS(Business Intelligence Development Studio).
Also remember that variables within SSIS packages are case sensitive.

Lock Monitor

Lock Monitor thread detects deadlock. It does that by inspecting processes ecery 5 seconds to determine if there are any deadlock cycles. When it finds one, it automatically chooses one thread as the deadlock victim. It then rolls back the
victims transaction, cancel its query and returns error 1205.

The Lock Monitor chooses the deadlock victim by choosing the least expensive transaction to roll back.
You can overide this by using SET DEADLOCK_PRIORITY to low

Error Number 18456: Login Failed For User

When an attempt to connect is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: "Login failed for user ''. (Microsoft SQL Server, Error: 18456)".
The following message might also be returned:

Msg 18456, Level 14, State 1, Server , Line 1"
"Login failed for user ''."

To troubleshoot this failure, look at the error state that maps to an authentication failure condition. Compare the error state to the table list below to determine the reason for the login failure.

Copying Tables

To create a copy of a table, you can use SELECT INTO to extract all the rows from an existing table into a new table. The new table must not exist already.

The following example will copy all the rows from Address table to a new table called TempAddress

Use AdventureWorks
Go
Select * into TempAddress from Person.Address

This statement does not copy the indexes, constraints,triggers, Keys etc. to the new table

Determining System and Server Information

The system and server information is available on the General page of the server properties dialog box. You can also use the extended stored procedure xp_msver.

exec xp_msver

Command-Line Utilities

Command Utilities provides database administrators different way to access the databse engine. SQL Server 2005 provides new command line utilities such as:
DTA
TableDiff
sac: which can be used to import or export settings that relates to surface area configuration.
sqlcmd: Lets you execute T-SQL statements and scripts from the command prompt or from query editor window is SSMS. sqlcmd provides a mechanism for utilizing variables within sqlcmd input files or scripts.

To demostrate the use of scripting variables, lets use this example.

A new input file named backupAdventureWorks.sql is created with the following sql statements inside it.

BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Scripting varaibles are refrenced using the $() designators. The -v option is used to assign a value to a variable at the command prompt as shown in the following example, which backups Adventureworks database.

Now execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql" -v DatabasetoBackup = AdventureWorks

Scripting variables can also be assigned within the script using :SETVAR command. The input file from the previous backup would be modified as follows to assign the DatabasetoBackup variable within the script:


:SETVAR DatabasetoBackup AdventureWorks
BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Then execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql"

There are also other command-line utilities that are also new to SQL Server 2005 that are used to lauch the graphical tools that come with SQL Server.

• profiler90: Lauches the SQL Server Profiler application.
• sqlwb: This utility launches the SQL Server Management Studion(SSMS).
• dtswizard: This util luanches import/export wizard that allows you to import and export data between SQL Server data sources and other data sources.

DTA Command-Line Utility

dta is the command line version of the graphical Database Engine TunningAdvisory. They both provide performance recommendations based on the workload provided to them.
The workload to tune would either be a workload file or a workload table. The workload filemust be either a profiler trace file or an SQL script or SQL Server trace file(.log).
We will use the T-SQL below which is stored in a workload file named c:\dtascript.sql.


USE AdventureWorks
Go
Select * from HumanResources.Employee
where gender = 'M'

To demonstrate the use of dta, lets look at this example.

C:\>dta -S pavilion -E -D AdventurewORKS -if c:\dtascript.sql -s Mysession -of c:\dtaOutputscript.sql -F

Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utilityVersion 9.00.3042.00Copyright (c)
Microsoft Corporation. All rights reserved.

Tuning session successfully created. Session ID is 6.
Total time used: 00:00:01Workload consumed: 100%, Estimated improvement: 47%

Tuning process finished.Successfully generated recommendations script: c:\dtaOutputscript.sql.

The output of the dtaoutputscript.sql will contain the following performance recommendation:

use [AdventureWorks]
go
CREATE NONCLUSTERED INDEX [_dta_index_Employee_14_869578136__K9_1_2_3_4_5_6_7_8_10_11_12_13_14_15_16] ON [HumanResources].[Employee]
( [Gender] ASC)
INCLUDE ( [EmployeeID],
[NationalIDNumber],
[ContactID],[LoginID],
[ManagerID],[Title],[BirthDate],
[MaritalStatus],
[HireDate],
[SalariedFlag],
[VacationHours],
[SickLeaveHours],
[CurrentFlag],
[rowguid],
[ModifiedDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

The dta ouput recommends that a new non-clustered index be created on the Gender column in the Employee table.

TableDiff Command-Line Utility

Tablediff is an SQLCMD command utility that allows you to compare the data contained within two different tables. This was developed for replication when data in two tables should be the same.This tool can determine if the content of tables involve in replication are the same, and if they are different, it can identify what data in the tables are different.

Let me demostrate this with this simple example:

USE AdventureWorks
GO
Create table table1( id int identity(1,1) NOT NULL, name varchar(50) )GO
Create table table2( id int identity(1,1) NOT NULL, name varchar(50) )GO
insert into table1 values('Jesse')
insert into table1 values('Emmanuela')
GO
insert into table2 values('Jesse')
insert into table2 values('Emma')
GO
--Now run the tablediff util on the command line from this folder:C:\program files\microsoft sql server\90\com\tablediff.exe

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "pavilion" -sourcedatabase "AdventureWorks" -sourceschema "dbo" -sourcetable "table1" -destinationserver "pavilion\sql2005" -destinationdatabase "AdventureWorks" -destinationschema "dbo" -destinationtable "table2" -f c:\Tablediff_output.txt

-- The execution of the utility with the above parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver pavilion\sql2005
-sourcedatabase AdventureWorks
-sourceschema dbo-sourcetable table1
-destinationserver pavilion\sql2005
-destinationdatabase AdventureWorks
-destinationschema dbo
-destinationtable table2
-f c:\Tablediff_output.txt

Table [AdventureWorks].[dbo].[table1] on pavilion\sql2005 and Table [AdventureWorks].[dbo].[table2] on pavilion\sql2005 have 1 differences.Fix SQL written to c:\Tablediff_output.txt.sql.

Err id Col
Mismatch 2 name
The requested operation took 1.46875 seconds.

The output displays the summary and shows the comparison results. It found one difference between table1 and table2. The -f parameter caused tablediff utility to output a SQL file that can be used to fix the difference in the destination file.

The output of the SQL file is:

-- Host: pavilion\sql2005
-- Database: [AdventureWorks]
-- Table: [dbo].[table2]
SET IDENTITY_INSERT [dbo].[table2] ON
UPDATE [dbo].[table2]
SET [name]='Emmanuela' WHERE [id] = 2
SET IDENTITY_INSERT [dbo].[table2] OFF

Encrypting Data with a PassPhrase

The simplest form of encryption is encrypting data by a PassPhrase using the EncryptByPhrase function.

EncryptByPhrase function lets you encrypt your data using a passphrase. In order to decrypt the same data, you use the DecryptByPassPhrase function. This lets you decrypt the data using the same passphrase you used in encrypting the data. THe example below demonstrate how to use a Passphrase in encrypting and decrypting data.


Declare @text varchar(100)
Declare @key varchar(100)
Declare @decrypted varchar(100)
Declare @encrypted varbinary(102) --requires two additional bytes

Set @text ='Hello World'
Set @key = 'Pa55word'

-- Encrypt the text using the @key Pasphrase(Pa55word)
Set @encrypted = EncryptByPassPhrase(@key, @text)
Select @text as 'Text to Encrypt', @encrypted as 'Ecnrypted text'

-- Deccrypt the text using the @key Pasphrase(Pa55word)
Set @decrypted = DecryptByPassPhrase(@key,@encrypted)
Select @encrypted as 'Encrypted text', @decrypted as 'Decrypted text'

Output:

-- The text has been encrypted using the PassPhrase set at @key variable
Text to Encrypt Encrypted Text
----------------------------------------------------------------------------------------
Hello World 0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF

-- The text has been decrypted using the PassPhrase set at @key variableEncrypted
Encrypted text Decrypted Text
0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF Hello World

Note that the PassPhrase are case sensitive. Secondly, you can only decrypt the text using the same passphrasethat were used in ecrypting the data.

Percentage of Logspace Used

You can use this SQL below to check the percentage of logspace currently used by your database.



dbcc sqlperf(logspace)

Apart from setting up alert to tell you when your database is running out of logspace, the above query does the same as well. However, you actually have to manually run the query to view the database logspace usage.

Current Active Transactions

To find how many active transactions are currently running, use the SQL below.



select * from sys.dm_tran_session_transactions

Concatenate Single Column into Row

This query concatenates a single column into one row

CREATE TABLE Numbers
(
number VARCHAR(20)
)
GO
INSERT Numbers SELECT 'one'
INSERT Numbers SELECT 'two'
INSERT Numbers SELECT 'three'
INSERT Numbers SELECT 'four'
GO

declare @list varchar(4000)
set @list = ''
select @list = @list + ',' + number from numbers
select @list = substring( @list, 2, len( @list ) -1) -- To Remove first comma
select @list

OUTPUT
-----------------------------------
one,two,three,four