Essential SQL Server Roles Flashcards
Explore key concepts of SQL Server roles and permissions with these engaging flashcards.
What is a database role?
A database role specifies a group of database users that can access the same objects of the database.
Who can be members of a database role?
Members can be Windows groups, user accounts, SQL Server logins, or other roles.
What are the two types of predefined roles in SQL Server?
The two types are fixed server roles and fixed database roles.
What are fixed server roles?
Fixed server roles are defined at the server level and exist outside of databases.
Which system procedures are used to manage fixed server roles?
The procedures are sp_addsrvrolemember and sp_dropsrvrolemember.
Can you add or remove fixed server roles?
No, you cannot add or remove fixed server roles.
How can you view permissions for fixed server roles?
You can view permissions using the sp_srvrolepermission system procedure.
What are fixed database roles?
Fixed database roles are defined at the database level and exist in each database.
What are application roles?
Application roles enforce security for a particular application, allowing it to handle user authentication.
Do application roles have members?
No, application roles do not have members.
What is required to activate an application role?
A password is required to activate an application role.
What is the syntax to create an application role?
USE sample
GO
CREATE APPLICATION ROLE weekly_reports WITH PASSWORD =’x1y2z3w4’, DEFAULT_SCHEMA =my_schema
How do you activate an application role after a connection is started?
You must execute the sp_setapprole system procedure.
What is the syntax for sp_setapprole?
sp_setapprole [@rolename =] ‘role’, [@password =] ‘password’, [,@encrypt =] ‘encrypt_style’
What are user-defined database roles?
User-defined roles are applied when a group of users needs to perform common activities and no applicable Windows group exists.
What is the syntax to create a user-defined role?
CREATE ROLE role_name [AUTHORIZATION owner_name]
Which system procedures are used to manage user-defined roles?
The procedures are sp_addrolemember, sp_droprolemember, and sp_helprole.
