How to create, alter or remove the permission of a user inside a database or on a SQL server instance?

Problem: How to create, alter or remove the permission of a user inside a database or on a SQL server instance?

Solution:  ROLE

Adds or removes members to or from a database role, or changes the name of a user-defined database role. 

To know more about a database roles please go through this article. what are different type of Roles

Syntax:

ALTER ROLE  role_name 

{ 

       ADD MEMBER database_principal 

    |  DROP MEMBER database_principal 

    |  WITH NAME = new_name 

}  [;] 

Example:

In this example, I would like to show we can create a role and assign a role to user.

-- create a role

CREATE ROLE Test; 

As you can see from the below image the role test has been created in the database roles.

-- select the roles to see wether it has been created or not.

select * from sys.database_principals where type='R' and is_fixed_role=0

-- add member to the role

ALTER ROLE Test ADD MEMBER TestUser; 

--query to see the roles and members are added.

select u.name as Rolename,u1.name as membername from sys.database_role_members m

INNER JOIN sys.database_principals u

ON m.role_principal_id=u.principal_id

INNER JOIN sys.database_principals u1

ON m.member_principal_id=u1.principal_id

where u.name='Test'

Results:

Rolename	membername
Test	TestUser

Example 2: How to drop the member and alter the role.

-- drop member to the role

ALTER ROLE Test DROP MEMBER TestUser; 

--alter role name

ALTER ROLE Test with name =test1