Yeah…I referenced the Wizard of Oz.
But that doesn’t take away from the fact you’re going to need to have a good handle on granting permissions using both users and groups. While you need to know how to grant those permissions using the GUI, more often than not you’ll be asked about granting those permissions via T-SQL. In the real world you seldom have to grant a single permission, it always seems to hit your desk in large blocks of permissions needing to be granted. I prefer to grant all my permissions to groups, then manage users into and out of groups. But that doesn’t always work. Let’s cover a few scenarios you might face in the real world and see how you can set the permissions using T-SQL.
Scenario 1
You have a group of database developers who will need to view, create, edit and drop views in your database. But nothing else. How can you pull this off with the least amount of code?
My Solution
I’d create a group called Developers, grant the permissions, then add the users to the group.
1: USE AdventureWorks;
2: --create the role
3: CREATE ROLE Developers;
4:
5: --grant the permissions
6: GRANT VIEW DEFINITION TO Developers;
7: GRANT CREATE VIEW TO Developers;
8: --in order to alter or drop a view, you have to have alter SCHEMA permissions
9: GRANT ALTER ON SCHEMA ::dbo TO Developers;
10:
11: --add users to the role
12: EXEC sp_addrolemember 'Developers','user1'
13: ---add more users to Developers
14:
15:
16:
Scenario 2
A business users calls up demanding access to run a lookup against production. You really don’t want to allow that user to do anything more than This one lookup. How do you grant this permission?
My Solution
Ask for a copy of the script the user wants to run. Wrap the query in a view. Then grant SELECT permission to the view to the user. That way they get their lookup and nothing more.
1: GRANT SELECT ON OBJECT ::viewName TO user1;
Scenario 3
You’ve just finished setting up a new SQL server for your developers to use. They’ve just set up their IIS test server. When they try to run any queries from the IIS Server against the test server they receive:
Error: 18452 Login failed for user ‘user1’. The user is not associated with a trusted SQL Server connection.
What’s wrong and how do you fix it?
My Solution
When you set up the server, you only enabled Windows logins. Normally that’s how I set them up. But in this test case, it seems that the developers will be testing using SQL users. That’s perfectly valid.
You need to enable Mixed Mode Authentication. This will let you connect to the new development server using either a Windows login or a SQL login.
In order to enable mixed mode, you need to open SSMS and connect to the SQL Server instance. In the Object Explorer, right click on the server in question and choose Properties. Once the window opens click on the Security option. Choose “SQL Server and Windows Authentication mode” and click OK.
In order to read the change, you’ll have to restart the MSSQL service on the new server. You’ll need to use Services.msc for that one.
From your windows start bar, simply click Start and type in “Services.msc” and hit enter. Find the entry for your SQL Server instance, highlight it and then hit Restart.
Now your developers can connect to your instance!
Summary
These are just three scenarios you can face on the 70-432. If you don’t feel completely comfortable with them, do a bit more experimentation and reading. You’ll be ready in no time! If you have any questions about these problems, or with the 70-432 in general, please let me know. I’m here to help.