Understanding Database User Accounts and Schemas Flashcards
Explore essential concepts of database user accounts and schemas with these informative flashcards.
What is a Windows user account?
A Windows user account or a login allows a user to log in to the system.
What is required to access a database in the system?
A user needs a database user account to work with the database.
How many database user accounts does a user need?
Users must have a database user account for each database they want to use.
What is the syntax to create a login account?
CREATE LOGIN login_name { WITH option_list1 | FROM {WINDOWS [ WITH option_list2 [,…] ] | CERTIFICATE certname | ASYMMETRIC KEY key_name }}
Example: CREATE LOGIN mary WITH PASSWORD = ‘you1know4it9’
What are the types of principals in a database schema?
A principal can be either an indivisible principal or a group principal.
What does an indivisible principal represent?
An indivisible principal represents a single user, such as a login or Windows user account.
What does a group principal represent?
A group principal can be a group of users, such as a role or a Windows group.
What is a significant benefit of separating database users from schemas?
One principal can own several schemas.
What happens when a database user is dropped?
Dropping a database user does not require the renaming of objects contained by that user’s schema.
What is the default schema in a database?
Each database has a default schema, which is used to resolve the names of objects that are referred to without their fully qualified names.
What are the three Transact-SQL schema-related statements?
CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA.
What is the syntax to create a schema?
CREATE SCHEMA my_schema AUTHORIZATION peter
What does the ALTER SCHEMA statement do?
The ALTER SCHEMA statement transfers an object between different schemas of the same database.
What is the syntax for the ALTER SCHEMA statement?
ALTER SCHEMA schema_name TRANSFER object_name
Example: ALTER SCHEMA humanresources TRANSFER person.address
How do you change the ownership of a schema?
Use the ALTER AUTHORIZATION statement to change the ownership of a schema.
Example: ALTER AUTHORIZATION ON SCHEMA ::my_schema TO mary
What does the DROP SCHEMA statement do?
The DROP SCHEMA statement removes a schema from the database.
What is required for a DROP SCHEMA statement to execute successfully?
The schema must not contain any objects.
What is the syntax to create a database user account?
CREATE USER user_name [FOR {LOGIN login | CERTIFICATE cert_name | ASYMMETRIC KEY key_name}] [ WITH DEFAULT_SCHEMA = schema_name]
Example: CREATE USER peter FOR LOGIN [NTB11901\pete]
What does the ALTER USER statement do?
The ALTER USER statement modifies a database username, changes its default schema, or remaps a user to another login.
What does the DROP USER statement do?
The DROP USER statement removes a user from the current database.
