This document provides an overview of managing security in SQL Server. It discusses principals like logins, users and roles. It also covers securables like servers, databases and schemas. Finally, it describes how to configure permissions between principals and securables at different scopes, and manage SQL Server authentication.
This document provides an overview of managing security in SQL Server. It discusses principals like logins, users and roles. It also covers securables like servers, databases and schemas. Finally, it describes how to configure permissions between principals and securables at different scopes, and manage SQL Server authentication.
This document provides an overview of managing security in SQL Server. It discusses principals like logins, users and roles. It also covers securables like servers, databases and schemas. Finally, it describes how to configure permissions between principals and securables at different scopes, and manage SQL Server authentication.
This document provides an overview of managing security in SQL Server. It discusses principals like logins, users and roles. It also covers securables like servers, databases and schemas. Finally, it describes how to configure permissions between principals and securables at different scopes, and manage SQL Server authentication.
Download as PPT, PDF, TXT or read online from Scribd
Download as ppt, pdf, or txt
You are on page 1/ 15
Managing Security
Overview of SQL Server Security
What Are Principals?
What Are Securables? SQL Server 2005 Permissions What Are Principals?
Securables Permissions Principals Windows Group Domain User Account Windows Local User Account
SQL Server Login
Server Role SQL Server
User Database Role Group Database What Are Securables?
Securables Permissions Principals Windows Group Domain User Account Windows Local User Account
SQL Server Login Server
Server Role SQL Server Database
User Database Role Schema
Group Database SQL Server 2005 Permissions
Securables Permissions Principals Windows Group Domain User Account ACL
Windows Local User Account
GRANT/REVOKE/DENY CREATE SQL Server Login ALTER Server DROP Server Role CONTROL SQL Server CONNECT SELECT Database EXECUTE User UPDATE DELETE Schema Database Role INSERT Application Role TAKE OWNERSHIP VIEW DEFINITION Database Group BACKUP What Are SQL Server Authentication Modes?
Windows Authentication Mode
Users are authenticated by Windows Users access SQL Server through a login mapped to their Windows account
SQL Server and Windows Authentication Mode
Users connecting over a trusted connection access SQL Server by using Windows authentication Users connecting over a nontrusted connection are validated by SQL Server How to Manage SQL Server Logins
CREATE CREATE LOGIN LOGIN Alice [SERVERX\SalesDBUsers] WITH FROM WINDOWS = ‘Pa$$w0rd’ Password WITH DEFAULT_DATABASE = AdventureWorks What Are Fixed Server Roles?
Role Description sysadmin Perform any activity dbcreator Create and alter databases diskadmin Manage disk files serveradmin Configure server-wide settings
securityadmin Manage and audit server logins
processadmin Manage SQL Server processes
bulkadmin Run the BULK INSERT statement
setupadmin Configure replication and linked servers
Server-Scope Permissions
Server permissions
USE master GRANT ALTER ANY DATABASE TO [ADVENTUREWORKS\Holly]
Server scope securable permissions
USE master GRANT ALTER ON LOGIN :: AWWebApp TO [ADVENTUREWORKS\Holly] How to Manage Users
Provide access to individual databases
Can map to an individual user login or a Windows group login Created using SQL Server Management Studio or CREATE USER statement Special Users
dbo user Exists in all databases by default Members of sysadmin role and the sa login account are mapped to dbo Cannot be deleted What Are Database Roles?
Fixed database roles
Granted permissions for common database tasks User-defined database roles Group users with similar permission requirements Public role Contains all users in the database How to Manage Data Base User
USE AdventureWorks CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem; Database-Scope Permissions
Database permissions
USE AdventureWorks GRANT ALTER ANY Schema TO HRManager
Database-scope securable permissions
USE AdventureWorks GRANT SELECT ON SCHEMA :: Sales TO SalesUser Schema-Scope Permissions
User-defined type permissions
USE AdventureWorks GRANT EXECUTE ON TYPE :: Person.addressType TO SalesUser
All other schema-scope permissions
USE AdventureWorks GRANT SELECT ON Sales.Order TO SalesUser
