0

I'm new to SQL Server, still struggling in understanding schema.

1.Is schema associated with login name?

  1. Is login name the same as 'user name' under SQL Server Authentication?

3.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

3
  1. 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.

  1. Is login name the same as 'user name' under SQL Server Authentication?

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.

  1. 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:

USE [master]
GO
-- Create a test database
CREATE DATABASE SecTest
GO

-- 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
GO

USE SecTest
GO

-- Create a schema for this user.
CREATE SCHEMA [SecTestSchema]
GO

-- 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]
GO

-- Permissions setting to allow user to create objects
ALTER AUTHORIZATION ON SCHEMA::[SecTestSchema] TO [SecTestUser]
GO

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]
  • so my textbook says the default schema is dbo, what does this mean? can you create table on the behalf of other user(different schema)? – user180527 Jun 7 at 0:41
  • 1
    The default schema is dbo by default, but you can change the default schema if you need to. If you have permissions on the database or the schema, you can create objects in that schema even if you're not the owner. – HandyD Jun 7 at 1:06
  • Let's say we have two tables in two schemas: ITSupport.Customer and dbo.Customer, and my schema is ITSupport, if I use select * from Customer, which table I'm accessing? if it is ITSupport.Customer, that means my default schema is ITSupport, not dbo? – user180527 Jun 7 at 1:10
  • 1
    It depends on who you are logged in as. If you're logged in as a user called ITSupport, and the default schema setting for that user is ITSupport then your query will run against ITSupport.Customer. If your default schema is dbo then your query will run against dbo.Customer. This is why, when you have multiple schemas in a SQL Server database, it is important to include the schema in all your queries. – HandyD Jun 7 at 1:15
  • If you're logged in as a user called ITSupport, I'm ttying to access a table name(DefaultCustomer) which doesn't belong to ITSupport but belong to dbo, if I use select * from DefaultCustomer, does it mean that the database engine will try to access ITSupport.DefaultCustomer first, since it doesn't exist under ITSupport, then it will access dbo.DefaultCustomer for me automatically? – user180527 Jun 7 at 1:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy