GO /* Create Test Table */ CREATE TABLE TestTable (FirstCol VARCHAR(10)) GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Change the database collation */ ALTER TABLE TestTable ALTER COLUMN FirstCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Database Cleanup */ DROP TABLE TestTable
When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.
Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.
The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:
Which application, database or login is using the most resources, and which queries are responsible for that
Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
What queries are running slower in today’s data compared to a previous set of data
You can also test how the system will behave with some change (different service pack or hotfix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.
Microsoft has published following three security related white papers. I suggest to all my readers to read them. Read the summary know what is covered in those white papers.
Engine Separation of Duties for the Application Developer- Separation of duties is an important consideration for databases and database applications. By properly defining schemas and roles, you can create a distinction between users who can manipulate data from those that administer the database. This paper discusses the topics of which application developers should be aware and provides a heuristic example to guide you in achieving separation of duties.
Database Encryption in SQL Server 2008 Enterprise Edition – With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, which is similar to two Windows features: the Encrypting File System (EFS) and BitLocker Drive Encryption, the new volume-level encryption introduced in Windows Vista, both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption, EFS, or BitLocker. This white paper compares TDE with these other encryption methods for application developers and database administrators. While this is not a technical, in-depth review of TDE, technical implementations are explored and a familiarity with concepts such as virtual log files and the buffer pool are assumed. The user is assumed to be familiar with cell-level encryption and cryptography in general. Implementing database encryption is covered, but not the rationale for encrypting a database.
Cryptography in SQL Server – Although cryptography provides SQL Server with powerful tools for encryption and verification, these are often not well understood. This can lead to poor or incomplete implementations. This white paper presents an overview of cryptographic functionality and discusses how this applies to authentication, signed procedures, permissions, and encryption. Because the target audience is the database professional and not necessarily security experts, the focus is on practical ways to use cryptography in SQL Server.
Recently I have got two desktop computers at home and both of them are very powerful machine.
Machine 1 : Windows Vista SP1 with SQL Server 2008
Machine 2 : Windows 2003 with SQL Server 2005 with SP2
When I was trying to connect from SQL Server 2008 to SQL Server 2005 using Windows Authentication I was getting following error.
Login failed for user ‘UserName’. The user is not associated with a trusted SQL Server connection.
To resolve this error follow the steps below on computer with SQL Server 2005.
Create new user with Administrator privilege with same username and password as of SQL Server 2008 in operating system.
On SQL Server database create new user by expanding DatabaseNode >> Security >> Login >> Create New User and add this new user with Windows Authentication radio button selected. This user can be only added by selected Windows Authentication it is Operating system’s User Login.
Once above two steps are completed when connected from SQL Server 2008 to SQL Server 2005 using Windows Authentication it will connect successfully.
Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP are encrypted it is not possible to get original text of the SP from SP itself. User who created SP will need to save the text to be used to create SP somewhere safe to reuse it again.
What prompted me to write this article is when SP are encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command and does not display the tab of execution plan if only Encrypted SP is called.
To understand this scenario let us create two SPs. First without encryption and second with encryption. Once SPs are created run them one by one with having Actual Execution Plan turned on. To learn how to turn on Actual Execution Plan read my previous article SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan.
/* Create SP without Encryption */ CREATE PROCEDURE #RegularSP AS
SELECT TOP 10 City FROM Person.Address
GO /* Create SP with Encryption */ CREATE PROCEDURE #EncryptSP WITH ENCRYPTION AS
SELECT TOP 10 City FROM Person.Address
GO /* Execute SP - Execution Plan Tab shows up */ EXEC #RegularSP GO
/* Execute SP - Execution Plan Tab does not shows up */ EXEC #EncryptSP GO
/* Clean Up */ DROP PROCEDURE #RegularSP DROP PROCEDURE #EncryptSP GO
It is very clear from above example that Encrypted SP does not show the actual execution plan as from Actual Execution Plan user can figure out the logic behind the SP. To avoid this issue for encrypted SP Management Studio does not display the execution plan.
I have seen developer confused many times when they receive following error message.
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
The reason for this error is when user is tring to attempt to run query or procedure or logic which is not compatible with previous version of the SQL Server. When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to next version. When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw above error.
Change the database compatibility level using following command.
For SQL Server 2005: EXEC sp_dbcmptlevel 'DatabaseName', 90
For SQL Server 2008: EXEC sp_dbcmptlevel 'DatabaseName', 100