1. Window 網域帳號登入，歸Window 作業系統
2. SQL server 帳號登入，SQL server 管理(Server roles are usually reserved for database and server administrators.)
3. Windows Authentication is the recommended security model when using SQL Server.
- Login security—Connecting to the server (登入 Server)
- Database security—Getting access to the database (存取DB)
- Database objects—Getting access to individual database objects and data(存取DB的某一個object/data)
1.Server Role -DBA 維護，管理整台server
2.Database Role - 管理單一的database
3.public role 只要一加入 SQL server,都有public role
- db_owner: Members have full access.
- db_accessadmin: Members can manage Windows groups and SQL Server logins.
- db_datareader: Members can read all data.
- db_datawriter: Members can add, delete, or modify data in the tables.
- db_ddladmin: Members can run dynamic-link library (DLL) statements.
- db_securityadmin: Members can modify role membership and manage permissions.
- db_bckupoperator: Members can back up the database.
- db_denydatareader: Members can’t view data within the database.
- db_denydatawriter: Members can’t change or delete data in tables or views.
Server Role(Predefine ):
- SysAdmin: Any member can perform any action on the server.
- ServerAdmin: Any member can set configuration options on the server.
- SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
- Security Admin: Any member can manage server security.
- ProcessAdmin: Any member can kill processes running on SQL Server.
- DbCreator: Any member can create, alter, drop, and restore databases.
- DiskAdmin: Any member can manage SQL Server disk files.
- BulkAdmin: Any member can run the bulk insert command.