Satheesh Kannan Chandra's Blog

I came across of this T-SQL code from a SQL Community site (SQLBang / SQLCon), I wonder... how to store tailed embeded space in a string.

----------------Begining of the script ------------
declare @myStr varchar(10)
declare @urStr varchar(10)

set @myStr = 'String'
set @urStr = 'String '

if @myStr = @urStr
begin
print 'Equal'
end
else
begin
print 'Not Equal'
end

----------------End of the script ------------

SQL Server has two types of roles:

standard roles and
applicaton roles.

Standard roles function like groups. For standard roles, users are assigned to the roles and then permissions are granted to those roles. Application roles are different from other roles; they never have member. The main purpose of the application role is to provide an extra layer to SQL Server security mechanism.

First the application (Query Analyser, VB application or a web application) connects to the database using a username and password. This may be the guest account or a trusted Windows NT account. Then the application switches to the application role using the sp_setapprole system stored procedure. Once this command is issued on a SQL Server connection all security is reset to whatever permissions have been granted to the particular application role. This command overrides any per user permissions assigned. The only way to disable this and revert back to your user permissions is to close the connection and reopen it.


To understand the ability of the application role, we will play like this in query analyser.
Assume query analyser is our sample application.


Open a session with your SQL server thru query analyser. and execute the following T-SQL commands.

use pubs

select * from sales
-- Having access to sales table as a 'sa' user.

sp_addapprole 'testapp','apppass'
-- Adding application role 'testapp' with password 'testapp'

deny SELECT on [sales] to [testapp]
-- Denying SELECT permisson to testapp application role.

sp_setapprole 'testapp','apppass'
-- Now the application (Query Analyser) switches
-- its security context from 'sa' to 'testapp' application role.
-- SQL Server returns the following message
-- "The application role 'testapp' is now active."

select * from sales
-- The current connection wont be able to access pubs.dbo.sales table
-- since, it is in application role's context.. even the connection
-- user is 'sa'.
-- SQL Server throws the following error message.

/*
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'sales', database 'pubs', owner 'dbo'.
*/


An important note is that application roles are database specific. Also it is connection specific. In out previous example, if you open an another connection to the SQL server thru 'sa'. Then the new connection will not have any impact from 'application role'.

To change collation type for all the character type fields to a new collation (other than editing manually each table in 'Design Table' through EM) . The following scrtipt worked fine for me.

This script has some problem while changing column with constrainsts / indexes. So can make another scripts for droping and recreating constrainsts / indexes. Execute those constraint scripts before and after the collation change.

declare @collation varchar(50)
set @collation = 'SQL_Latin1_General_CP1_CI_AI '

select 'alter table [' + sysusers.name + '].[' + sysobjects.name + '] ' +
'alter column [' + syscolumns.name + '] ' +
systypes.name +
case
when systypes.name = 'nvarchar' or systypes.name = 'nchar'
then '(' + cast(syscolumns.length / 2 as varchar) + ') '
else '(' + cast(syscolumns.length as varchar) + ') '
end +
' collate ' + @collation +
case when syscolumns.isnullable = 1 then 'NULL' else 'NOT NULL' end sql
from syscolumns
inner join sysobjects
on sysobjects.id = syscolumns.id
inner join systypes
on systypes.xtype = syscolumns.xtype
inner join sysusers
on sysusers.uid = sysobjects.uid
where syscolumns.collation is not null
and sysobjects.xtype = 'U'
and systypes.name <> 'sysname'
and syscolumns.type = 39

--
--
--Find the collation of the current server
select serverproperty ('collation')

--Find the collation of a particular database
select convert(sysname,databasepropertyex('Northwind','Collation'))

--Find the collation of the current database
select databasepropertyex(db_name(),'Collation')

--Find collation of all columns in a table
use pubs
go
select name, collation from syscolumns (nolock) where [id]=object_id('Titles')

--Find all collation available in SQL Server
select * from ::fn_helpcollations()
--
--

SQL Server 6.0 / 7.0 doesn't allow to change collation (code page and sort order) on the fly. To change the collation of an existing database, we need to rebuild the master database or reinstall SQL Server. Whereas SQL Server 2000 supports Collation changes in much degree. We can have different collations within database.

Some of the frequent issues we face when we have different collation settings are:

A) When you create a temporary table, the columns get the default collation of the server. When you try to join that temporary table to a user table on a column that has a different collation, you get an error message about SQL not being able to resolve a collation conflict.

For this always use the COLLATE DATABASE_DEFAULT clause for each column when creating a temporary table.

B) When you run a query that joins a user table to a system table in the master database, again with different collations - produces the same error.

For this always use that clause next to the column from the system table in your ON clause.