1 / 10

SQL Server 2000 & Security

SQL Server 2000 & Security. Palak Patel. SQL Server Authentication Modes. Windows Authentication Mode Mixed Mode (Windows Authentication + SQL Server Standard) Setting the Authentication Mode. SQL Server Logins. Login: sa Standard Logins Create a Login in EM

emma
Download Presentation

SQL Server 2000 & Security

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2000 & Security Palak Patel

  2. SQL Server Authentication Modes • Windows Authentication Mode • Mixed Mode (Windows Authentication + SQL Server Standard) • Setting the Authentication Mode

  3. SQL Server Logins • Login: sa • Standard Logins • Create a Login in EM • sp_addlogin @loginame= ‘test’, @passwd=‘test’, @defdb=‘Northwind’ • Windows Logins • Single User • Windows User Created Group • Windows Built-in Group • sp_grantlogin @loginame = ‘Domain\User’

  4. sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin bulkadmin Assigning SQL Server Logins to fixed server roles using EM sp_addsrvrolemember @loginame = ‘test’, @rolename =‘serveradmin’ Fixed Server Roles

  5. Database Users • Special Users • dbo • guest • Understanding Database Users • Creating Database Users Using • EM • Sp_grantdbaccess @loginame=‘test’

  6. Statement Permissions At Server Level Create Database As Database Level Create Function Create Table Create View Create Rule Create Procedure Create Default Backup Database Backup Log Six Types of Object Permissions SELECT UPDATE INSERT DELETE DRI EXEC Permissions • 3 Statement in T-SQL • GRANT • REVOKE • DENY • 3 states of Permissions Check Boxes

  7. Fixed Roles db_owner db_accessadmin db_datareader db_datawriter db_ddladmin db_securityadmin db_denydatareader db_denydatawriter db_backupoperator public Assigning Users to Fixed Database Roles sp_addrolemember @rolename=‘db_datareader’ @membername=‘test’ Custom Database Roles Standard Roles Application Role Standard Roles using EM sp_addrole @rolename=‘testrole’ Application Roles using EM sp_setapprole @rolename=‘testrole’ @password = ‘passwd’ Database Roles

  8. Ownership Chains • Understanding Ownership Chains • John creates a Table and grants permission to Jeff. Jeff creates a view and grants permission to Thomas. Thomas has permissions on View but not on the underlying table and therefore, he can not use the view. • Solution: Create objects as a dbo. Use db_ddladmin role to assign users capabilities to create users as dbo

  9. Monitoring Security • Audit Logon Success/Failures and view them in the SQL Server Logs • SQL Profiler to Audit Security related things • Enabling C2 Level Auditing: • Sp_configure ‘Show advanced options’,’1’ • Sp_configure ‘C2 Audit Mode’,’1’

  10. Summary • Practical Guidelines about Implementing Security in SQL Server • Things to consider about Security in Exam 70-228/229

More Related