how to know which access having user in PaaS sql server database.

Ramu Kilaru 20 Reputation points
2024-04-23T11:37:49.75+00:00

Hi all,

I would like to know about, how to find out user roles in PaaS sql server database and how to create group and add members to group from Azure portal.

could you please some body help me on this question?

Thanks

Ramu.

Microsoft Partner Center
Microsoft Partner Center
A Microsoft website for partners that provides access to product support, a partner community, and other partner services.
886 questions
0 comments No comments
{count} votes

Accepted answer
  1. Marcin Policht 13,175 Reputation points MVP
    2024-04-23T11:42:45.3366667+00:00

    Finding User Roles in Azure SQL Database:

    1. Sign in to Azure Portal: Go to the Azure portal and sign in with your Azure account.
    2. Navigate to Azure SQL Database: In the Azure portal, navigate to the Azure SQL Database instance for which you want to view user roles.
    3. View Server-Level Roles: Under the "Settings" section of your SQL Database instance, select "Roles" to view the server-level roles. Here, you can see the built-in server-level roles like sysadmin, db_owner, db_securityadmin, etc.
    4. View Database-Level Roles: Navigate to the specific database within your SQL Database instance. Under the "Settings" section of the database, select "Roles" to view the database-level roles like db_datareader, db_datawriter, db_owner, etc.
    5. View Users and Their Roles: Within the roles section, you can see the list of users and their associated roles. This will include both server-level and database-level roles.

    Creating a Group and Adding Members to the Group:

    1. Navigate to Azure Active Directory: In the Azure portal, navigate to "Azure Active Directory" from the left-hand menu.
    2. Create a Group: Under "Manage," select "Groups" and then click on "+ New group" to create a new group. Provide a name and description for the group, and specify the group's membership type (e.g., security group or Microsoft 365 group).
    3. Add Members to the Group: After creating the group, navigate to the group's page and select "Members" from the left-hand menu. Click on "+ Add members" to add users or other groups to the group. You can search for users or groups by name and add them to the group.
    4. Assign Group to Roles: Once the group is created and members are added, you can assign the group to roles within Azure SQL Database. Navigate back to your SQL Database instance, and within the "Settings" section, select "Active Directory admin." Here, you can assign the group as the Azure AD admin for the SQL Database instance or specific databases. This will allow members of the group to access the databases with the permissions associated with their Azure AD roles. Finding User Roles in Azure SQL Database:
      1. Sign in to Azure Portal: Go to the Azure portal and sign in with your Azure account.
      2. Navigate to Azure SQL Database: In the Azure portal, navigate to the Azure SQL Database instance for which you want to view user roles.
      3. View Server-Level Roles: Under the "Settings" section of your SQL Database instance, select "Roles" to view the server-level roles. Here, you can see the built-in server-level roles like sysadmin, db_owner, db_securityadmin, etc.
      4. View Database-Level Roles: Navigate to the specific database within your SQL Database instance. Under the "Settings" section of the database, select "Roles" to view the database-level roles like db_datareader, db_datawriter, db_owner, etc.
      5. View Users and Their Roles: Within the roles section, you can see the list of users and their associated roles. This will include both server-level and database-level roles.
      Creating a Group and Adding Members to the Group:
      1. Navigate to Azure Active Directory: In the Azure portal, navigate to "Azure Active Directory" from the left-hand menu.
      2. Create a Group: Under "Manage," select "Groups" and then click on "+ New group" to create a new group. Provide a name and description for the group, and specify the group's membership type (e.g., security group or Microsoft 365 group).
      3. Add Members to the Group: After creating the group, navigate to the group's page and select "Members" from the left-hand menu. Click on "+ Add members" to add users or other groups to the group. You can search for users or groups by name and add them to the group.
      4. Assign Group to Roles: Once the group is created and members are added, you can assign the group to roles within Azure SQL Database. Navigate back to your SQL Database instance, and within the "Settings" section, select "Active Directory admin." Here, you can assign the group as the Azure AD admin for the SQL Database instance or specific databases. This will allow members of the group to access the databases with the permissions associated with their Azure AD roles.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Ramu Kilaru 20 Reputation points
    2024-04-23T16:07:02.1233333+00:00

    Hi Marcin Policht

    Thank for sharing useful information and I have another question also .. I want to know list of user of PaaS DB from Azure portal without selecting groups because we have many groups and I could not see External groups (I think created externally) within the list could you please let me how did it happened.

    Thanks

    Ramu

    0 comments No comments