SQL creates a login account, creates a security account for the new account, and grants the database access role

Create a login user and perform System Authorization
Use databasedemo
Go
-- Add the Logon account 'hanguoji 'with the password '123456'. The default database is 'databasedemo'
Exec sp_addlogin 'hanguoji', '20170901', 'irmdmc316'
Go
-- Add the security account 'guoji' to the database 'databasedemo' for the Logon account 'hangoji'
Exec sp_grantdbaccess 'hanguoji', 'guoji'
Go
-- Authorize the security account 'guoji' as the 'db _ owner' database role
Exec sp_addrolemember 'db _ owner', 'guoji'
Go


-- Change the password sequence: original password, new password, and logon Username
Exec sp_password '000000', '000000', 'hangzhouji'
Go

-- Delete a security account in the order of deletion --> delete a Logon account.
-- Delete a security account
Exec sp_revokedbaccess 'guoji'
Go
-- Delete a Logon account
Exec sp_droplogin 'hanguoji'
Go



-- Grant the Logon account any operations for SQL Installation
Exec SP_ADDSRVROLEMEMBER 'hanguoji', 'sysadmin'
Go
-- Authorize the Administrator server to log on to the Logon account
Exec SP_ADDSRVROLEMEMBER 'hanguoji', 'securityadmin'
Go
-- Delete the 'db _ owner' database role of the security account
Exec sp_droprolemember 'db _ owner', 'guoji'


-- Add a role
Exec sp_addrole 'myrole'
Go
-- Grant the role all permissions on the table tmp_table
Grant all on tmp_table to myrole
Go
-- Grant the role the query permission on the table tmp_table.
Grant select on tmp_table to myrole
Go
-- Refuse the security account Guoji to query the table tmp_table
Deny select on tmp_table to Guoji
Go
-- Delete the access permission for the security account Guoji table tmp_table
Revoke select on tmp_table to Guoji
Go