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 may manage permissions related to your applications.

And, the best news, you may 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 may 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 may 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 may 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:

You may 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.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK