hello

Simulation of Orphan Users and the fix !

A) EXECUTE THESE ON SERVER A

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

-------------------------------------------------
-------------------------------------------------


B) EXECUTE THESE ON SERVER B

restore database test22
from disk='C:\backups\full_test1.bak'
with file=1,
move 'test1' to 'C:\backups\test_rest1.mdf',
move 'test1_log' to 'C:\backups\test_rest2.ldf'

sp_change_users_login 'report'
The output would be as follows :
sqlcat1 0x1770DFE18F9CD64A812C27DD61CC03C8
sqlcat2 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 0xB884905510F3C0438BDFDE1D888DD612

Now execute : sp_change_users_login 'auto_fix','sqlcat1',NULL,'SQLcat123!'

When u specify null then it means user doesnt have a login to be linked to.
after the script is executed,for the user 'sqlcat1' a new login would be created with the same name as 'sqlcat1' and then the password given would be set to that login.

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner NULL NULL sqlcat2 6 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL


watch the sqlcat1 it has now a login associated with it.

now create a new login for the server instance B as follows :
create login catsql1
with password='CATsql123!'

sp_change_users_login 'update_one','sqlcat2','catsql1'

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner catsql1 master sqlcat2 6 0xCE4CEA864162EA469F5FBB55FEEBC0E4
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL

Have a look at the above query result.Watch for sqlcat2 it has now got the sql login associated with it and its nothing but catsql1.

What is Column level Encryption in sql server 2005 ?

use hello

if not exists(select * from sys.symmetric_keys where name='##MS_DATABASEMASTERKEY##')
create master key
encryption by password='Annai1!'
go
select * from sys.certificates

if not exists(select * from sys.certificates where name='cert246')
create certificate cert246
with subject='certi for sam'
go



create symmetric key sym246
with algorithm=AES_256
encryption by certificate cert246
go
create table sam
(
name varchar(20),
ssn varbinary(256)
)
go

create view sam_decry
as


select name,
convert(nvarchar,DecryptByKey(ssn)) as "ssn"
from sam

go

create trigger trig246
on sam_decry
instead of insert
as
begin
open symmetric key sym246
decryption by certificate cert246;

insert into sam(name,ssn)
select
i.name,
EncryptByKey(Key_GUID('sym246'),ssn)from inserted i

end


-----------------\\


insert into sam_decry
values('sudan','madhuxyu')

select * from sam

select * from sam_decry

Differences in SQL 2000 and higher versions !

1) Declaring Cursor differs in sql server 2000 from that of sql server 2005.
In sql server 2000,we issue the below command :
declare cursorinput CURSOR for select........

In sql server 2005,we issue the below command :
declare @cu cursor
set @cu=CURSOR for select.......

Articles of other Authors !

1) Deploying scripts on multiple instances :
http://www.sqlservercentral.com/articles/Administration/2629/

2) Mapping users to logins in sql server 2000 :
http://www.mssqltips.com/tip.asp?tip=1063

Replication in sql server 2008

Q) What are the types of replication ?
A) Snapshot

SQL Replication

SQL Server's snapshot replication technology allows you to automatically transfer information between multiple SQL Server databases. This technology is a great way to improve the performance and/or reliability of your databases. Here are some examples of replication use cases:
Geographically distributing data to databases located at remote sites. This improves performance for end users by placing the data in a network location close to them and simultaneously reduces the load on intersite network connections.
Distributing data across multiple servers for load-balancing purposes. One common deployment strategy is to have a master database that is used for all update queries and then several subordinate databases that receive snapshots and are used in read-only mode to provide data to users and applications
Update data on a backup server to be brought online in the event the primary server fails
When you use snapshot replication, you copy the entire database from the Publisher SQL Server to the Subscriber SQL Server(s) on a one-time or recurring basis. When the Subscriber receives an update, it overwrites its entire copy of the data with the information received from the Publisher. This can take quite a long time with large datasets and it is imperative that you carefully consider the frequency and timing of snapshot distribution. For example, you would not want to transfer snapshots between servers in the middle of a busy data on a highly congested network. It would be much more prudent to transfer the information in the middle of the night when users are at home and bandwidth is plentiful.

Initiating snapshot replication is a three-step process and About Databases contains detailed tutorials explaining each step:
Create the distributor
Create the publication
Subscribe to the publication
You may repeat the final step of creating a subscriber as many times as necessary to create all of the subscribers you would like.

Snapshot replication is a powerful tool that allows you to transfer data between SQL Server installations in your enterprise. The tutorials linked above will help you get started moving data in a matter of hours.

SQL Profiler