Chapter 18. Permission Management

Chapter 18. Permission Management

Managing permissions is an important part in multi-user environment.

Your IT staff manages permissions at the server and database levels. This is their tasks.

As a business user, you can manage permissions related to your applications.

And, the best news, you can do this for SQL Server in Excel using the SaveToDB add-in application.

This application is a good example, also, of what you and your developers can do with the SaveToDB add-in.

SQL Server Management Application

Create the permissions worksheet and run Data Connection Wizard. Connect to your database.

At the following step, select SQL Server Management in the Select Query List field,

leave Enable Query List on the ribbon checked, and select Logins.

SQL Server Permission Management in Data Connection Wizard

Click Finish and insert a new table at cell B3.

 

Logins

This table allows managing logins, passwords, and usernames. Just change the data and click Save.

Excel worksheet to manage database logins

Usually, business users see the personal login only and cannot change any value.

The Actions menu contains useful actions and helpful links for database administrators.

Context actions in permission management

Query List

The ribbon Query List allows changing query objects using a single worksheet for multiple tasks:

The query list allows selecting an object of the permission management

Users

The Users table shows users that you can manage. Usually, business users have no permissions to change.

Excel worksheet to manage database users

Roles

This table allows checking and changing user membership in roles. Just set 1 or clear the value in the desired cell.

Excel worksheet to manage database roles

Principal Permissions

This table allows changing principal permissions. Set the HasAny value to 1 to see the actual permissions only.

Excel worksheet to manage principal permissions

Database Permissions

Only database administrators can change permissions. Business users must have CREATE TABLE permissions.

Excel worksheet to manage database permissions

Object Permissions

This is a working table for business users. We learn it below. Set HasAny to 1 to see the actual permissions only.

Excel worksheet to manage database object permissions

The table contains actual permissions for our application:

Actual object permissions of the created Excel application

Suffix legend: s - by parent schema, r - by parent role, sr - by parent schema and role, + - WITH GRANT OPTION

We see the following data under Alex's credentials:

  • The database contains the Alex_Team role and users: Alex, Lora, and Nick.
  • The database contains the dbo69 schema and tables: Companies, EventHandlers, Payments, TableFormats.
  • Alex has the CONTROL permission WITH GRANT OPTION on the dbo69 schema and schema tables.
  • Alex_Team and its members have read and write permissions on the dbo69 schema and schema tables.

You can change permissions using first letters: G - GRANT, D - DENY, R - REVOKE

In the following example, Alex denies the INSERT permission for the EventHandlers table directly, and the UPDATE and DELETE permission indirectly, using the role level.

Try to change database object permissions using G, D, and R letters

In any case, if you have this task, you will find this app user-friendly and learn required actions quickly.

Do not afraid and go ahead.