SQL 2005 - Login & User Creation

The syntax for adding a login to SQL Server 2000 would be as follows:

sp_addlogin 'UserA','Password','public_db' which means add the userA with password as 'password' and the default Database when the user logs in would be public_db.

Still the same hold good in SQL 2005 but due to the threat that this SP might be removed from future versions lets start using the below statement which is almost similar

CREATE LOGIN UserA WITH PASSWORD = 'password' DEFAULT_DATABASE = 'public_db';

For adding Windows login we used sp_grantlogin but from now on try using the below to add Windows login to Servers

CREATE LOGIN [DomainA\Robert] FROM WINDOWS;

And for adding user to DB we used sp_adduser which adds the user to public role,instead use CREATE USER

CREATE USER UserA FOR LOGIN UserA WITH DEFAULT_SCHEMA = Test_Schema;