Authorization To Groups In SQL Server

In this article, I will provide a practical explanation of how to Authorization to Groups in SQL Server.

SQL Server offers us more than one opportunity in terms of security.

SQL Server has options such as SQL Server Authentication and Windows Authentication.

In addition to these, you can add clients, groups in your domain or local.

We mentioned that we grant authorization to groups we define in Windows or domain, thanks to these groups, when someone is added to that group, we will not need to grant authorization via SQL because every authorization we grant to this group will also affect those in that group.

For example, we have a group called “sqlusergroup”.

Let’s define authorization on SQL for this group.

First, we call New Login on Management Studio.

In the window that opens, click on the search section to open a new window.

On this window, click on “Object Types” and on the screen that appears, select the “Groups” section and click on the OK button to continue.

If you do not select this, the groups will not be listed.

We type the name of our group in the empty field, click the “Check Names” button and continue by clicking the OK button.

In the “User Mapping” section, we define db_owner permissions on the desired AdventureWorks2014 database.

In this example, we defined our authorization for the group named “sqlusergroup” in the “AdventureWorks2014” database, but authorization can be defined for more than one database or authorization can be defined at the instance level.