70-432: Permissions and Users and Roles, Oh my!

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;
 5: --grant the permissions
 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;
 11: --add users to the role
 12: EXEC sp_addrolemember 'Developers','user1'
 13: ---add more users to Developers

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.

mixedmodeYou 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.

restartFrom 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!





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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *