To add a member to a database role in SQL Server, you use the ALTER ROLE statement. The syntax for adding a member involves specifying the role that you want to modify and the user (or database principal) that you want to add to that role.
Here's the basic syntax:
ALTER ROLE role_name ADD MEMBER member_name;
- role_name: The name of the role to which you want to add a member.
- member_name: The name of the user, login, or database principal you want to add to the role.
Example
Suppose you have a database role named db_datareader, and you want to add a user named JohnDoe to that role. The statement would look like this:
ALTER ROLE db_datareader ADD MEMBER JohnDoe;
Notes
1. You must have the ALTER permission on the role to execute this statement.
2. If the specified member is already a member of the role, SQL Server will not raise an error; it simply won't take any action.
3. You can execute this statement in the context of the relevant database where the role exists.
Additional Example
If you want to add multiple members to a role, you can run multiple ALTER ROLE statements:
ALTER ROLE db_datawriter ADD MEMBER JaneDoe;
ALTER ROLE db_datawriter ADD MEMBER MikeSmith;
Checking Role Membership
To check if a member has been added successfully to the role, you can query the system views or catalog views, like this:
SELECT
dp.name AS MemberName,
dp.type_desc AS MemberType
FROM
sys.database_role_members drm
JOIN
sys.database_principals dp ON drm.member_principal_id = dp.principal_id
WHERE
drm.role_principal_id = USER_ID('db_datareader'); -- Specify your role name here
This will give you a list of members in the specified role.