SQL Server database security

-- Create a login user
-- Create login login_name from windows with default_database = database | default_language = language;
Create login [localhost \ hoojo-pc] from windows with default_database = testHome;
-- Create a login user
-- Create login login_name with password = 'pwd' [hashed] [must_change] [check_expiration] [check_policy]
Create login admin with password = '000000', default_database = testHome;

-- View login users
Select * from sys. SQL _logins;

-- Modify a user
Alter login admin with name = jackson;
Alter login jackson with password = 'abc ';

-- Disable and enable users
Alter login jackson disable; -- disable
Alter login jackson enable; -- enable

-- Delete a user
Drop login jackson;

-- Modify the ing credential (map the login name MacraeS to the credential Custodian04)
Alter login jackson with credential = Custodian04;

------- Database User
-- Create a database user
Create user jack from login admin;
Create user jason for login jackson;

Create user jack from login admin with default_schema = temp_schema;

-- Start the guest user (not recommended)
-- Special users dbo and guest; dbo users corresponding to sa. Other users without ing are guest users.
Grant connect to guest;

-- Modify a user
Alter user jack with name = jason;
Alter user jason with default_schema = jason_schema;

-- Delete a user
Drop user jason;

-------- Database role
-- Grant the buyers role to the jack user
Create role buyers authorIzation jack;

-- Modify a role
Alter role buyers with name = new_buyers;

-- Delete a role
Drop role new_buyers;

-------- Architecture Management
-- Create
Create schema temp_schema;

-- Specify a user
Create schema jason_scheam authorization jason;

-- Authorization Query
Grant select to jason;

-- Delete
Drop schema jason_scheam;

-------- Permission
-- Authorize table Creation
Grant create table to jason;
-- Authorize jason to query the student table
Grant select on student to jason;

-- Revoke permissions
Revoke create table to jason;
Revoke select on student to jason;