Logins, Roles and Credentials

1. Logins, Roles and Credentials Lesson 14

2. Skills Matrix

3. SQL Server Logins • A SQL Server key—a login—gives your users access to SQL Server as a whole, not to the resources (such as databases) inside. • If you’re a member of the sysadmin or securityadmin fixed server, you can create one of two types of logins: • Standard logins • Windows logins

6. Standard Login • Only clients with a Windows account can make trusted connections to SQL Server (where SQL Server trusts Windows to validate the user’s password). • If the user (such as a Macintosh or Linux client) for whom you’re creating a login can’t make a trusted connection, you must create a standard login for him or her.

7. Windows Login • A Windows login can be mapped to one of the following: • A single user • A Windows group that an administrator has created • A Windows built-in group (for example, Administrators)

8. Items Common to All Logins • When users first log in to SQL Server, they connect to the default database. • In all types of logins, you can grant database access at create time. • If you create a Windows login using sp_grantlogin, you can’t set the default database or language. • In addition, you can add users to a fixed server role at the time you create them • You do this on the Server Roles tab in SQL Server Management Studio.

9. Database User Accounts • In much the same way, you need to give users access to databases once they have logged in to SQL Server. • You do so by creating database user accounts and then assigning permissions to those user accounts. • Once this process completes, your SQL Server users also have more than one key: one for the front door (the login) and one for each file cabinet (database) to which they need access.

10. Database User Accounts • You may have noticed that two user accounts already exist in your databases when they are first created: • DBO • Guest • Members of the sysadmin fixed server role automatically become the database owner (DBO) user in every database on the system.

11. Roles • Roles are analogous to groups in the operating system. • A user can be added to a role to inherit the permissions of the role. • In this manner you can simplify your administrative load: create a role and design the necessary security protocol. • Users can then be added or removed easily maintaining your security plan.

13. Fixed Server Roles • Bulkadmin • Dbcreator • Diskadmin • Processadmin • Public • Securityadmin • Serveradmin • Setupadmin • sysadmin:

14. SysAdmin • Members of the sysadmin role have the authority to perform any task in SQL Server. • SQL Server automatically makes operating system BUILTIN\Administrators a member of the sysadmin server role, giving SQL Server administrative rights to all of your Windows administrators.

15. Database Roles • Although granting permissions to single users proves useful from time to time, it’s better, faster, and easier to apply permissions via roles. • You have three types of database roles to consider: • Fixed • Custom • Application

16. Fixed Database Roles • Fixed database roles have permissions already applied; that is, you need only add users to these roles and the users inherit the associated permissions

17. Fixed Database Roles • db_accessadmin • db_backupoperator • db_datareader • db_datawriter • db_ddladmin • db_denydatareader • db_denydatawriter • db_owner • db_securityadmin • public

18. Custom Database Roles • Fixed database roles cover many—but not all—of the situations that require permissions to be assigned to users. • When you create this new role, you assign permissions to it and then assign users to the role • The users inherit whatever permissions you assign to that role. • You can make your custom database roles members of other database roles. This refers to nesting roles.

19. Application Roles • The application role—grants you a great deal of authority over which applications can be used to work with the data in your databases. • To enable the application role, the application executes the sp_setapprole stored procedure (which writes to the application at design time). • Once the application role has been enabled, SQL Server no longer sees users as themselves. • it sees them as the application and grants them application role permissions.

20. MSDB Roles • Additional roles of value are found in the MSDB system database. • DatabaseMailUserRole • db_ssisadmin • db_ssisltduser • db_ssisoperator • dc_admin • dc_operator • dc_proxy • PolicyAdministratorRole

21. MSDB Roles • RSExecRole • ServerGroupAdministratorRole • ServerGroupReaderRole • SQLAgentOperatorRole • SQLAgentReaderRole • SQLAgentUserRole • TargetServersRole

22. Ownership Chain • In the physical world, people own objects, with which they can do as they please, including lending or giving them to others. • SQL Server understands this concept of ownership. When users create an object, they own that object and can do whatever they want with it. • This works well, until you consider ownership chains. • An object on loan still belongs to the owner; the person who borrows it must ask the owner for permission before allowing another person to use it. • Acting without such permission describes a broken ownership chain.

23. Credentials • A credential record contains the authentication information (credentials) required to connect to a resource outside SQL Server. • SQL Server uses this information internally. • Most credentials contain a Windows user name and password.

24. Summary • This lesson examined the processes of creating and managing logins, groups, and users. • You learned how to create a Standard login and a Windows user or group login using SQL Server Management Studio or T-SQL, and you learned when to use each type. • If you have a well-designed security plan that incorporates growth, managing your user base can be a nearly painless task.

25. Summary • To limit administrative access to SQL Server at the server level, you learned you can add users to a fixed server role. • To limit access in a specific database, you can add users to a database role; and if one of the fixed database roles isn’t to your liking, you can create your own. • You can even go as far as to limit access to specific applications by creating an application role.

26. Summary • To limit administrative access to SQL Server at the server level, you learned you can add users to a fixed server role. • To limit access in a specific database, you can add users to a database role; and if one of the fixed database roles isn’t to your liking, you can create your own. • You can even go as far as to limit access to specific applications by creating an application role.

27. Summary • You then learned about chains of ownership. These are created when you grant permissions to others on objects you own. • Adding more users who create dependent objects creates broken ownership chains, which can become complex and tricky to manage. • You learned how to predict the permissions available to users at different locations within these ownership chains. • You also learned that to avoid the broken ownership chains, you can add your users to either the db_owner database role or the db_ddladmin database role and have your users create objects as the DBO.

28. Summary • You can grant permissions to database users, as well as database roles. • When you add users to a role, they inherit the permissions of the role, including the public role (to which everyone belongs). • The only exception occurs when a user has been denied permission, because Deny takes precedence over any other right, no matter the level at which the permission was granted.

29. Summary • And finally you learned you can access resources outside of SQL Server by creating credentials.

30. Summary for Certification Examination • Understand roles. Be familiar with the various fixed server and database roles and what they can be used for in the real world. • You also need to know when to create a custom database role instead of using the built-in roles. • A good example happens when you need to allow users to insert, update, and select on a table but not to delete. • No built-in role allows this, so you would need a custom role.