Schema, User and Login To understand what is Schema, User and Login on SQL Server.

Introduction
In Microsoft® SQL Server™, each database have Schema, User and Login. In this article we are going to see the basic difference between these objects.


Login Name
Login identifiers (Ids) are associated with users when they connect to Microsoft® SQL Server™ 2000. Login IDs are the accounts that control access to the SQL Server system. A user cannot connect to SQL Server without first specifying a valid login ID. Members of the sysadmin fixed server role define login IDs.


Purpose : It is used to connect the SQL server.


sp_addlogin (sql server authentication) sp_grantlogin (NT authentication)


User Name
A user identifier (ID) identifies a user within a database. All permissions and ownership of objects in the database are controlled by the user account. User accounts are specific to a database; the xyz user account in the sales database is different from the xyz user account in the inventory database, even though both accounts have the same ID. User IDs are defined by members of the db_owner fixed database role.


A login ID by itself does not give a user permissions to access objects in any databases. A login ID must be associated with a user ID in each database before anyone connecting with that login ID can access objects in the databases. If a login ID has not been explicitly associated with any user ID in a database, it is associated with the guest user ID. If a database has no guest user account, a login cannot access the database unless it has been associated with a valid user account.


When a user ID is defined, it is associated with a login ID. For example, a member of the db_owner role can associate the Microsoft® Windows® 2000 login NETDOMAIN\Joe with user ID abc in the sales database and user ID def in the employee database. The default is for the login ID and user ID to be the same.


Purpose : If specific login wants to access on specific database they should be added into the database.


sp_grantdbaccess


Schema Name

It is inside the database; specific to Table and View permission aginst to the User(not login). It will describe the permission(s) on the Table and View. In otherword it is inline permission statement while creating Tables and Views.


Purpose: You can grant permission while creating (or just before creating) the Table or View. Here there is no specific order where the grant statement should appear(ie, you can first give the permission before creating table or view).


CREATE SCHEMA AUTHORIZATION



Conclusion

So, the Top level is Login name, to connect the SQL Server, User Name is the access permission to the Database, Schema Names are inline access permissions while creating the database. Database objects are named using UserName.ObjectName. UserName and LoginName may not be identical. ie., you can change the UserName for any login name. By default the Username and LoginName are same. (ex. DBO is username of SA loginname)

Refrences

http://msdn2.microsoft.com/en-us/library/ms189462.aspx


Copped by Manivannan.D.Sekaran