[SQL Server] Security (user, role, and permission) in SQL Server)

-- Brief Introduction to SQL Server Security (users, roles, permissions)
/*
Users are divided into Server login and database users.
Roles are divided:
Server role (inherent, cannot be deleted or customized );
Database roles (inherent, cannot be deleted or custom roles can be added );
View online for more help
*/

-- Practical application description

-- SQL Server 2000 creates logon, user, and role, and sets Permissions
-- Add Server login
Exec master. DBO. sp_addlogin @ loginame = n 'logintest', @ passwd = '123 ',
@ Defdb = n 'dbtest', @ deflanguage = N'

/*
-- Set the login role
Exec master .. SP_ADDSRVROLEMEMBER @ loginame = n 'logintest', @ rolename = n 'sysadmin'
Go
*/
-- One Login
-- Users can be created on multiple databases
-- However, you cannot create multiple users in a database.
-- Add the security account usertest to the database dbtest to log on to logintest.
Use dbtest
Go
Exec DBO. sp_grantdbaccess @ loginame = n' logintest ', @ name_in_db = n' usertest'

-- Set database roles for users
-- Sp_addrole can be used to add custom database roles.
Exec sp_addrolemember n 'db _ datareader ', N 'usertest'
Go
Exec sp_addrolemember n 'db _ datawriter ', N 'usertest'
Go
-- Logintest can only log on to the dbtest database, but we can access databases such as master and pubs.
-- This is because there is a guest user in those databases, and the guest user can be associated with the login name of the user not set.
-- That is, if logintest does not set associated users on the master database, the guest user is used.

-- Delete database users. before deleting login, you must delete all database users logged in.
If exists (select * From DBO. sysusers where name = n 'usertest ')
Exec DBO. sp_revokedbaccess n 'usertest'
Go

-- Delete logon. before deleting logon, you must delete all logged-in database users.
If exists (select * from Master. DBO. syslogins where name = n 'logintest ')
Exec master. DBO. sp_droplogin @ loginame = n 'logintest'
Go

----------------------------------
---- Custom roles
-- Create role r_test
Exec sp_addrole 'r _ Test'

-- Grant role r_test all permissions on the jobs table
Grant all on jobs to r_test
-- Grant the role r_test the select permission on the titles table.
Grant select on titles to r_test

-- Add login l_test, set password to PWD, default database to pubs
Exec sp_addlogin 'l _ test', 'pwd', 'pubs'

-- Add the security account u_test to the database pubs for login l_test
Exec sp_grantdbaccess 'l _ test', 'U _ Test'

-- Add u_test as a member of role r_test
Exec sp_addrolemember 'r _ test', 'U _ Test'

-- Deny the select permission of the security account u_test on the titles table
Deny select on titles to u_test

/* -- After completing the preceding steps, log on with l_test to perform all operations on the jobs table, but cannot query the titles table. Although the role r_test has the select permission on the titles table, however, the Select permission for titles has been explicitly denied in the security account, so l_test has no select permission for the titles table --*/

-- Delete a security account from the database pubs
Exec sp_revokedbaccess 'U _ Test'

-- Delete login l_test
Exec sp_droplogin 'l _ Test'

-- Delete role r_test
Exec sp_droprole 'r _ Test'

---------------------------------
-- Create can be used in SQL Server 2005.
Use [Master]
Go
Create login [logintest] with Password = n' 111111 ', default_database = [Master], check_expiration = OFF, check_policy = on
Go
Use [dB]
Go
Create user [logintest] For Login [logintest]
Go
Use [dB]
Go
Exec sp_addrolemember n 'db _ denydatareader ', N 'logintest'
Go
Use [dB]
Go
Exec sp_addrolemember n 'db _ denydatawriter ', N 'logintest'
Go
-- Server role

-- For detailed permission settings, it is recommended to view online
Grant, deny, revoke