Exam 70-764 Study Notes – Configure data access and permissions – Users and Roles

If you are wanting to share resources and knowledge with others please join our closed study group on Facebook.

Create and maintain users

  • Database users need to have access to the data they need, but be kept away from everything else.
  • Make sure you know how to create users and change the permissions and other options with user logins
  • Loginless users what are they and why would you have them?
  • fn_my_permissions and how to use to check and object or databases access.

Manage database object permissions

Users need to be given the permission they require to execute the tasks required of them. Have a read through this MS article – Getting started with database engine permissions.

Grants vs Revokes

When a user is issued with a Grant and a Revoke the Grant is the overiding permission.
Deny overides Grant.

USE EXECUTE AS USER to test a users permissions

Example of GRANT and REVOKE

Note: You will need a datbase called DBA to run the below simple example on. This example will show you how to:

  1. Create a user
  2. GRANT and REVOKE a simple permission
  3. Test it out using the EXECUTE AS USER command
--Create the tables and columns used
USE DBA;  
GO 
 
 
CREATE  TABLE CC_Information (id INT, FullName VARCHAR(100), CreditCard VARCHAR(24) );
 
INSERT INTO CC_Information
VALUES ( 1,'Rob McGeorge','1234-5678-8765-4321' )
        ,(2,'Don Perignon','5678-1234-4321-8765' )
 
-- Check the data has loaded
SELECT * FROM CC_Information

-- Create a user
USE [master]
GO
CREATE LOGIN [TEMPEmployee] WITH PASSWORD=N'iamat3mp', DEFAULT_DATABASE=[DBA], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBA]
GO
CREATE USER [TEMPEmployee] FOR LOGIN [TEMPEmployee]
GO

 -- Now try before permission has been granted
 EXECUTE AS USER = 'TEMPEmployee'

 -- Check the data has loaded
SELECT * FROM CC_Information

REVERT 

-- Now grant SELECT permission

GRANT SELECT ON CC_Information to TEMPEmployee

 -- Now try that permission has been granted
 EXECUTE AS USER = 'TEMPEmployee'

 -- Check the data
SELECT * FROM CC_Information

REVERT

REVOKE SELECT ON CC_Information to TEMPEmployee

 -- Now try that permission has been revoked
 EXECUTE AS USER = 'TEMPEmployee'

 -- Check the data 
SELECT * FROM CC_Information

Other tips for your study

Take some time to understand orphaned users. These are a frequent issue in production environments, particularly when doing migrations/upgrades.

There is a stored procedure you can run which fixes the SID mismatch which is called sp_change_users_login.

Contained databases is something you don’t see out there much in the wild, however the study book I am using hints that there could be a question about them in the exam so it is a good idea to familiarize yourself with what exactly they are and how they work.

Have a read through Microsofts article on these. MS article on Contained Databases.

Create and maintain custom roles

You can create database roles and then add users to those roles. This is a good way to allow a user to do something, without giving them specific permissions.

There are three types of roles that can be implemented.

Application roles

Ref: MS Application Roles
These are a datbase principal which enables an application to run with permissions similar to a user.
Any DB that has guest disabled will not be accessible by an application role in another database.

User defined database roles

These are database roles that you can create similar to the fixed database roles that already exist. Yet with your own custom permissions.

User defined Server roles

These were made available from SQL 2012 and enable you to create your own custom server level roles similar to the 9 built in roles that already exist.

Other Resources

If you are interested in purchasing a book for the exam check out Exam Ref 70-764 Administering a SQL Database Infrastructure by Victor Isakov:

It is not a large book and there are a couple of issues with it worth noting.

Does not come with online resources (you have to type in any examples yourself)
Some of the reviewers on Amazon found some typos and other errors. I haven’t myself but read a few reviews that mentioned that.

Despite those minor issues, I found it to be the best book available for this exam:

Exam Ref 70-764 Administering a SQL Database Infrastructure

If you are on Facebook come and join our Study Group here.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply