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:
- Create a user
- GRANT and REVOKE a simple permission
- 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.
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.
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:
If you are on Facebook come and join our Study Group here.