Learn about the role concept in SQL Server

In any database management system, security and security are always top priority.And with SQL Server, if we take advantage of the many advantages of this application, those who manage the system administration will greatly reduce the burden as well as the pressure on the job. For the following tutorial, we will show you some basic information as well as how to use and manage the SQL Server role.

The first step to take in the entire data security process for users is to clearly identify which accounts will have access, view or edit data. For example, department heads can view employees' salary accounts, while higher-level managers will have the right to view and edit, while employees can only view their own accounts. .

Next, you need to specify which account will be granted the right to modify or change the database. Therefore, depending on the system model, the size of the company and organization, the workload of the administrator will also increase, besides, the knowledge and experience of each user is different, therefore, it is much harder to ensure the minimum level of safety for all employees. Specifically, in the next section of the article, we will discuss the concept of the role and relationship with Windows Group , how to add or deny access rights for one or more user accounts during the time. operation time.

In essence, the role is part of the tiered security model:

- Login security: perform the process of connecting to the server

- Database security: receive access to the database

- Database object: receive access to each object and separate data throughout the system

First, the user must proceed to log in to the server by entering a password, after the connection process is complete, access to the archived databases will be done through account assignment. . And when the administrator has completed assigning permissions to these accounts, they will not be able to access other unauthorized data areas.

However, the biggest advantage of using the role is the effectiveness of the management process. Imagine that, if 1000 employees need to view or edit their personal data as soon as possible, then the system administrator only selects the available Windows Group, and then assigns it. Entire access to the corresponding SQL Server role - instead of having to manually edit 1000 accounts in turn manually. To better understand this situation, the Windows Group contains all user accounts with corresponding access to the network on Windows, and the SQL Server roles will depend entirely on the relevant components. Therefore, we only need to assign the demand according to the database of SQL Server and the corresponding account on Windows.

Server roles are usually monitored and managed by Database Administrator - DBA and are applied to the entire server, not just for each individual component. By default, these roles are set to public for all accounts, and all accounts after adding to SQL Server will automatically be assigned a public role.

Creating a database is for administrators only, but you need to keep in mind some of the following general rules when creating tables:

- db_owner: all users have full - access rights

- db_accessadmin: user has the right to manage the Windows Group and login SQL Server account

- db_datareader: user can read all data

- db_datawriter: users have the right to add, delete or edit data in the table

- db_ddladmin: users can use dynamic files - link library (DLL)

- db_securityadmin: users can edit role roles and manage other management and authorization levels

- db_bckupoperator: users can back up the database

- db_denydatareader: user cannot view data in table

- db_denydatawriter: users cannot view, change or delete data in the table

With fixed roles, they are applied across the entire model of the system, with a number of common considerations as follows:

- SysAdmin : all users can perform operations on the server

- ServerAdmin: all users can set and customize options on the server

- SetupAdmin: all users can manage connected servers, SQL Server operations and options

- Security Admin: all users can manage components related to security and security

- ProcessAdmin: All users can turn off or pause any process that works on SQL Server

- DbCreator: all users can create, change, delete or restore databases

- DiskAdmin: all users can manage SQL Server files

- BulkAdmin: all users can perform many different insert commands

Next, we will use SQL Server Enterprise Manager to assign Windows groups and databases Pubs (database template comes with SQL Server 2000). The first step is to create a Group Guest login account, through which the user can access SQL Server:

- Start Enterprise Manager, open the security folder

- Right-click the Logins item, select New Login from the menu displayed, the New Login window will display as shown below. Note that in this case, Windows Authentication is already selected, and we will use Windows Authentication Mode , as opposed to SQL Server Mixed Mode (Mixed Mode includes Windows Security and SQL Server's available security model. , and Windows Authentication is always recommended to be used with SQL Server):

Learn about the role concept in SQL Server Picture 1

- Click the button next to the Name box to display the SQL Server Login Properties - New Login, select the corresponding Windows group (here, Guests ), click the Add button then click OK to close the SQL Server Login Properties window - New Login :

Learn about the role concept in SQL Server Picture 2

- On the General tab, select the Pubs database from the Database list, the default option is Master, but you should not assign access to this database, because it will directly interfere with the installation process of SQL. Server.

- Select to continue Database Access card to see information about database available on the system
To continue, we must choose the default database before SQL Server activates the database role. Select Pubs (as shown below), the Public role section will automatically be assigned. At this point, we can select and assign any roles, besides, the Guest group is already listed in the Column:

Learn about the role concept in SQL Server Picture 3

- Click OK to complete this process, Enterprise Manager will display the new login section in the right window, and so far all members of the Windows Guests group can login to SQL Server:

Learn about the role concept in SQL Server Picture 4

- Another way to assign the same role as above, in Enterprise Manager select Pubs , double-click Roles> db_accessadmin to display the Role Properties window :

- Click the Add button to display the list of users and groups, select Guest as shown below:

Learn about the role concept in SQL Server Picture 5

- Database Role Properties window will be displayed after adding Guest login section to db_accessadmin role :

Learn about the role concept in SQL Server Picture 6

- Click OK to complete this process.

At this point, we've finished the process of adding Windows Guest groups to SQL Server and Guest groups to the Pubs database and the db_accessadmin role. The Guest group members have inherited the entire permissions in the db_accessadmin role (listed above) inside the Pubs database.

Next, we will create a new database role. Specifically, the roles are predefined, for example db_accessadmin is not complete, but we can still create a new role. For example, we will create a new role called Purchasing :

- Go back to the Enterprise Manager dashboard , right-click anywhere in the right window, select New Database Role from the menu displayed. Enter the name Purchasing in the Name section, then click the Add button. Note that at this point the Permissions button is still in the disabled state.

- Click the Add button to assign the user or group account to the role section. In this case, the Guest group (shown above).

Learn about the role concept in SQL Server Picture 7
We just created a new role called Purchasing in the Pubs database

- Click OK to close this window.

After creating a new role - Purchasing for database Pubs , we have to create a user account that will be assigned to the role of Purchasing (via the Guest group). Also here, let's set up a few simple permissions for this Purchasing role:

- Re-open the Purchasing role you just created in the previous step in the Enterprise Manager window , and this time the Permissions button will work

- Click the Permissions button to open the Database Role Properties control panel . Here, you can set the corresponding decentralization level for each object here.

Learn about the role concept in SQL Server Picture 8

- For example, if you check the Delete column as shown in the authors line, all members of this role can delete the data records in Authors table.

- If you pay close attention, you can see that selecting the Authors table also activates the Columns function button. Click this Columns button to display the Column Permissions table . In this table, we can limit the number of hits in a given column.

Servers role servers so far, as a general rule, most administrators will not proceed to assign any user accounts to the fixed server role, as they are only used for administration. Database and Administrator account. But besides, we can still assign user accounts to the server role in two ways: using Enterprise Manager or Query Analyzer. For example, to assign a Guest account to the Server Creators server role via Enterprise Manager, you need to do the following:

- Expand the Security Folder section

- Double-click Server Roles> Database Creators

- If you want to select and assign group Windows to the role section, click the Add button to open the Add Members window

- Select the Windows Guest group then click OK to close this window, assign the group Guest to the Server Role Properties table :

Learn about the role concept in SQL Server Picture 9

Note that at this point both tabs of this table are viewable, open the General tab to assign the login to the role, or use the Permissions tab to select the corresponding SQL statement that this server role can actually execute. exam. Then, click OK to complete this process.

If you want to use Query Analyzer , type the command structure below, with the name parameter representing the user and role as one of the components listed above:

Exec sp_addserverrolemember name, role

For example, the following statement will allow us to assign the Martin Reid record to the SysAdmin role:

Exec sp_addserverrolemember 'Martin Reid', 'SysAdmin'

One of the most important components of SQL Server is the system stored procedure - capable of supporting users in managing various aspects of the server. The full list of system stored procedures can be found here. Good luck!