Aaron Lowe

So now that we have the login and the user set up, how do we actually assign permissions? 

I prefer to assign permissions to roles and add users to roles which mirrors the user/groups of windows, so going down that path we have to create a role (going under Security –> Roles):

CREATE ROLE [TestRole]

Now comes the part that isn’t very intuitive.  To associate a user with a role you have to go back to the Solution Explorer and actually create a generic user script file (see here), you can call it whatever you want and place it wherever you want in the project, however if you reverse engineer the database it will be called <ProjectName>.rolememberships.sql under the schema objects folder:

EXECUTE sp_addrolemember @rolename = N'TestRole', @membername = N'TestUser';

Now we have the role defined and the users as part of the role, we can start adding permissions, this is where you’ll have loads of fun provided you enjoy manually editing xml documents.  Again in Solution Explorer under Properties you’ll see a Database.sqlpermissions.sql file:

In there you’ll see some xml with examples:

<?xml version="1.0" encoding="utf-8"?>
<?XML:NAMESPACE PREFIX = [default] urn:Microsoft.VisualStudio.Data.Schema.Permissions NS = "urn:Microsoft.VisualStudio.Data.Schema.Permissions" />
  

So as you can see while it’s not that intuitive it is pretty straight forward.

So how do we manage permissions in a Database Project?  Well the logins and users are pretty straight forward, however sadly the grants are not as intuitive as we’d like (unless you find manually editing xml documents intuitive), but the next version is supposed to be gui driven.  However right now I hope you enjoy editing xml.

So first we take the Server project and create the login for the user (if don’t know the difference between Logins and Users go read K. Brian Kelley’s post here, I’ll wait).  If you are in Schema view (and if not, why not?) make sure to go under Server Level Objects –> Security –> Logins, not the Security –> Users path, as you want a Server Login, not a master db User.

You’ll also notice in the Add new item dialog that there is a different template for each:

• Login (Windows Auth)
• Login (Sql Server)

At this point you might also be tempted in creation of the login to assign a default database, avoid that temptation.  When you assign a default database, that user database must already exist, however when you deploy the server project (i.e., the master database) there’s a good chance the user database won’t exist yet, I promise we’ll take care of it later though.

CREATE LOGIN TestLogin WITH PASSWORD = 'P@ssw0rd1'

So now that you have a server login in the server project we need to create a database user (remember to reference the server project in the database project).  This time you can go under the Security –> Users path.

CREATE USER [TestUser]
    FOR LOGIN [TestLogin]
    WITH DEFAULT_SCHEMA = dbo;

Now we have a login and a user created, lastly we set the default database in the database project post-Deployment script:

ALTER LOGIN [TestLogin] WITH DEFAULT_DATABASE=[$(DatabaseName)]

As you see I’m using the variable of the database name since I can change the database name for each deployment:

Taking a look at pertinent parts of the deployment sql scripts we have (remember this is in SQLCMD mode):

/*
Deployment script for master -LocalServer.sql
*/
:setvar DatabaseName "master"
GO
USE [$(DatabaseName)]
GO
PRINT N'Creating TestLogin...';
GO
CREATE LOGIN [TestLogin]
    WITH PASSWORD = N'P@ssw0rd1', DEFAULT_DATABASE = master;
GO
------------------------------
/*
Deployment script for AdventureWorks - AdventureWorks.sql
*/
:setvar DatabaseName "AdventureWorks"
GO
CREATE USER [TestUser] FOR LOGIN [TestLogin];
GO
ALTER LOGIN [TestLogin] WITH DEFAULT_DATABASE=[$(DatabaseName)]
GO

Lately I’ve been utilizing schemas more frequently in my databases, however the first time I did this in VSDBPro was a pain.  The reason for this was I didn’t know how to do it, so hopefully this post will save someone else the struggles I went through.

First I want to highlight the differences between Solution Explorer and Schema View.  Solution Explorer is the physical file structure, it doesn’t know about relationships other than the folder structure:

Then there is the schema view which does understand the relationships within the database and models very closely the Management Studio layout:

As you would imagine there are pros and cons to both.  For example if you have invalid syntax for defining a table, you won’t even see it in schema view, you’ll only see it in solution explorer.  However if you have a foreign key relationship that points to a missing column you’ll see it in schema view with the warning symbol.  Also the schema view has appropriate icons for different object types.

However back to the issue of schema management.  I’m very particular about naming conventions and file placement so not trusting the tool (sorry Gert) I figured I new better and would just go into solution explorer and create the new schema myself.  No problem:

And then I tried to create objects under that scheme however the folder structure wasn’t there!!  What a pain, so I went out to Windows Explorer copied the file structure from an existing schema, removing all the objects and added them into the project via Solution explorer, all the time wondering about the person who thought this was a good idea.

However lo and behold had I just switched over to schema view which understands what a schema is and what it can have, I would have been fine.

What’s more is that when I created my first table, it automatically not only created the table sql file, it created the entire folder structure, seen below back in solution explorer:

So while we’re all used to utilizing Solution Explorer for our projects, for Database projects please be aware and use Schema view unless you have to deal with the actual files themselves.