Allow user login SQL over Network( from other PC)

1. Set up login
- Management Studio> DBServer > Security > Login > New Login // Create public login
- Login name=BOB , pass=pass1234 ,Sql authentication , default db=mycomp
- mycomp> Security> User > RC > new user
-Login name=BOB , username=BOB , db_owner * BOB is owner of comp

2. Enable remote connection
-SQL Server Config Manager > Sql 2005 Network Config > Enable TCP/IP * allow network connection

3. Unblock Firewalls to SQL Service
* If firewall block, do following
-control panel> security center> Windows Firewalls > Exception tab
-Add program> browse (MSSQL\Binn\sqlservr > OK
-Add port > name: UDP port > close

pls. Above steps enable PC2 login to SQL2005 hosted by PC1 with SQL Auth BOB=pass1234
If Windows Auth is chosen, any pcX can access to SQL2005 without credential.

pls. Bob can't open other DB beside mycomp once logging in SQL 2005

Set Up Database Diagram Designer

1. Change compatiblity level of DB
EXEC sp_dbcmptlevel 'database_name', '90';

2. Change DB owner to Valid login in stance of SQL Server

ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa