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.
Click Finish and insert a new table at cell B3.
This table allows managing logins, passwords, and usernames. Just change the data and click Save.
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.
The ribbon Query List allows changing query objects using a single worksheet for multiple tasks:
The Users table shows users that you can manage. Usually, business users have no permissions to change.
This table allows checking and changing user membership in roles. Just set 1 or clear the value in the desired cell.
This table allows changing principal permissions. Set the HasAny value to 1 to see the actual permissions only.
Only database administrators can change permissions. Business users must have CREATE TABLE permissions.
This is a working table for business users. We learn it below. Set HasAny to 1 to see the actual permissions only.
The table contains actual permissions for our 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.
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.