Greg Larsen continues his series and shows how user-defined roles roles can control SQL Server security.
When developing an application that accesses SQL Server, you need to set up a security model, so each user gets the access they need based on their job duties. Some applications require the same access for all users, while other application might require different security access rights based on the role a user has in the organization. The best practice for providing user access is to use role-based security. SQL Server provides user-defined Server and Database roles for defining security rules for different user security needs. This article will discuss how to use user-defined server and database roles to provide secure access to SQL Server resources.
What is Role-Based Security?
Role-based security is the concept of providing each application user access to the SQL Server resources they need by being a member of a role. A role is an object in SQL Server that contains members, much like a Windows group contains members. When a user is a member of a role, they inherit the permissions associated with the role.
When role-based security is used, the actual access permissions to SQL Server resources are granted to a role and not a specific user. Role-based security reduces the amount of administration work needed to grant and manage security when multiple application users require the same access to SQL Server resources. Once a role has been set up, and the appropriate permissions have been granted, it is just a simple matter of adding users to the role to provide them with the same security access. Without using roles, an administrator would need to grant the same permissions to each user, thus causing additional administration work. There is also the possibility of making an error, resulting in some users getting the wrong set of permissions.
Since SQL Server 2012, Microsoft has provided two different types of user-defined roles: Server and Database. The user-defined server roles provide security access to server resources. In contrast, user-defined database roles provide access to database resources.
#sql server security #sql prompt #sql
