- Is schema associated with login name?
No. A login is a server-level principal used to connect to the SQL Server instance. This does not infer any permissions or access to any databases unless a database user has been created and associated with the login, or high-privilege role membership (such as sysadmin) has been granted to the login.
- Is login name the same as 'user name' under SQL Server
Not exactly. When you create a login (server-level principal) the field login name is used and can be either a SQL Authentication username or a Windows username (in the format DOMAIN\username). When you create a user (database-level principal) the user name field is used and represents the database username. This database-level principal can be linked to a server-level login or it can be a contained user that does not link to principals outside of the database. If it is contained, "user name" represents the username used to connect to the database as that principal. If it is linked to a server-level login then you still log in to SQL Server with the login name but that login is represented within the database as the username for the linked database principal.
- Let's say I login as username 'ITSupport', so any table I created will be sth like ITSupport.TableName, so I don't need to prefix ITSupport to access TableName. So what's my default schema name ITSupport or dbo
Default schema is set at the database user level and if your database user has permissions to create objects (such as membership in the db_ddladmin database role) and does not specify the schema then any objects created by that user where the schema is not specified will be created in the default schema for that user.
Note that a schema does not exist simply because a user was created. If you have not created a schema and set the default schema for your user, you will by default be using dbo and any non-prefixed object names will assume the schema is dbo.
The below script will create some example principals to illustrate the above points:
-- Create a test database
CREATE DATABASE SecTest
-- Create a login. This is a server-level principal that is used to connect to the SQL Server instance.
CREATE LOGIN [SecTestLogin] WITH PASSWORD=N'test1234!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
-- Create a schema for this user.
CREATE SCHEMA [SecTestSchema]
-- Create a database user and associate it with the server login. Note the difference in names and the default schema specification
CREATE USER [SecTestUser] FOR LOGIN [SecTestLogin] WITH DEFAULT_SCHEMA=[SecTestSchema]
-- Permissions setting to allow user to create objects
ALTER AUTHORIZATION ON SCHEMA::[SecTestSchema] TO [SecTestUser]
ALTER ROLE db_ddladmin ADD MEMBER SecTestUser
ALTER ROLE db_datareader ADD MEMBER SecTestUser
ALTER ROLE db_datawriter ADD MEMBER SecTestUser
The below script will highlight some of the points above:
-- LOGIN AS THE LOGIN CREATED EARLIER
-- Create some tables. Note the prefixed vs non-prefixed tables
CREATE TABLE TableCreate1 (ID INT)
CREATE TABLE dbo.TableCreate2 (ID INT)
CREATE TABLE SecTestSchema.TableCreate3 (ID INT)
-- Show the tables and the schema they're in. Note the schema for non-prefixed table TableCreate1
SELECT s.[name] AS [Schema], t.[name] AS [Table]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
-- Shows the database user and server login for the current user
SELECT USER_NAME() AS [Current Database Principal], SUSER_NAME() AS [Current Server Principal]