The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P31: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 132 Chapter 4 Managing Security a user s access thus posing a significant security risk. Make sure that you are familiar with what access has been granted to that role or what that permission enables a user to do. If the user does not need to do sysadmin tasks the user should not be added to the sysadmin group. The same goes for the database-level roles if they only need to select data from a table the db_datareader should be sufficient. Not every user needs to be at the db_owner permission level. If a user is granted permissions beyond the scope of their specific need this principle has been violated. Test Day Tip Be familiar with the Principle of Least Privilege and how that applies to granting permissions to SQL Server resources. Users Users are database-level principals that are created in order to access resources within a database. Database Users are typically mapped to a login certificate or asymmetric key. After you have created a login to SQL Server you create a database user in order to provide permission to a database and the objects contained within. In order to more easily tie users back to logins it is recommended that the Log-in and User names match. To aid in managing the organization of users permissions users can be added to any one or more of the available database roles or to new roles that you create. Users can be added to a database using the SQL Server Management Studio or by syntax such as CREATE USER. user_name FOR FROM LOGIN login_name CERTIFICATE cert_name ASYMMETRIC KEY asym_key_name WITHOUT LOGIN WITH DEFAULT_SCHEMA schema_name See Figure . Managing Security Chapter 4 133 Exam Warning TMake sure that you understand the difference between a User and Login and their permissions scope. Figure Database Users Microsoft SQL Server Management Studio Fie Edit View Project Debug Tools Windw J. New Query lJ J Object Explorer Connect jj n Y jt . Jj 3 j AdventureWorks2008 a Pj Database Diagrams SI Ë3 Tables a L3 Views a i 3 Synonyms æ Pa .
