AaronLowe.net

VSDBPro – Permissions part 2

Aaron Lowe — Mon, 12 Oct 2009 09:00:00 -0600

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.

VSDBPro – Permissions part 1, Logins and Users

Aaron Lowe — Thu, 08 Oct 2009 07:00:00 -0600

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:

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

VSDBPro – multiple schemas

Aaron Lowe — Tue, 06 Oct 2009 10:00:00 -0600

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.

VSDBPro project dependency

Aaron Lowe — Fri, 02 Oct 2009 08:00:00 -0600

So I’ve been using Visual Studio for Database Professionals (aka VSDBPro, aka DataDude) consistently this year for primarily new database development, both for Data Warehouses and OLTP Databases. This will be the first in what hopefully will become a series of posts about Database projects.

In Database Professional there is are two types of projects, the database project and the database server project. The database project would be the user database while the database server project would be the master database. Neither one is specific to a particular server or instance, it’s a Visual Studio project which can be deployed on a single or multiple instances as you need.

You can also create dependencies between projects so you can have a database project which had a dependency on a database server project. Why would you do this you ask? The most straight forward example would be the login to user relationship. You could have a database server project which creates your logins, and a database project which then creates the users in that user database mapped back to the logins in the server project.

You can reference a database project one of three ways:

Project within the solution
.schema file (DB projects don’t create .dll files)
Server and database variables

The first two are pretty straight forward as you would just select a project or a .schema from your workspace, so let’s look at how you would utilize variables for reference. First here’s the screen to create a dependency:

Easy enough, just enter the variables and you would have:

You’ll notice in the variables the characters %(), this is what tells the Project that these are variables, and I didn’t enter the variables this way, I just entered ServerName and when I tabbed out of the filed the $() were automatically added. So now you can see the variables in your command variables dialog (under project properties). The first two are created with every project and you’ll see since I specified a value up above, the value is already used, instead of needing to set it at deployment time.

So now that we have the variables, how does this actually get used when we go to deployment? I’m glad you asked, on deployment a .sql file is created that utilized sqlcmd mode. So the output at deployment time time will be:

Notice that there is no value for the DefaultDataPath, as I never did set it at deployment time. So there you have it, you can now parameterize your project dependencies!

ISNUMERIC – maybe…

Aaron Lowe — Wed, 30 Sep 2009 10:00:00 -0600

So the other day I was working on a T-SQL query that I was using to take data from generic data types and insert it into a strongly typed table. One of the columns was moving from an nvarchar(255) to an decimal data type. As some of the rows had character data in it, I wrote something like this:

CASE ISNUMERIC(Col1)
    WHEN 1 THEN CAST(Col1 as decimal(9,4))
    ELSE NULL
END as Col1

However when I ran the query, I received this error:

Error converting data type nvarchar to numeric.

It took me awhile to figure this one out as you can see I”m using ISNUMERIC to check before I convert it to a number, seems pretty straight forward, so what’s the bid deal?

The big deal is that I needed to read the manual…according to the ISNUMERIC function in BOL:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.

It turned out I had a row that had a value of a single decimal point and no numbers which caused the problem. So remember, understand what the functions you use actually do, not what you think they do and test!

Welcome back

Aaron Lowe — Mon, 28 Sep 2009 10:00:00 -0600

Being that I have been involved in a project since the first of the year that pretty much drained me week-to-week, I sadly wasn’t able to keep up with my blog. However now that I’ve moved onto to a new project I plan on getting back to posting blogs again.

I continue to be very grateful to the SQL community for its hard work and support (I loved 24 Hours of PASS and the impromptu associated ustream - 24 hours of LaRock) and hopefully I can give back to the community a portion of what you all have given me.

SSIS Blog Post Thank you

Aaron Lowe — Mon, 26 Jan 2009 20:29:41 -0600

Traditionally I have used SSIS for OLTP data movements, however lately I’ve been loading a Data Warehouse which has slightly different requirements. I just wanted to drop a quick note to highlight the great resources that already exist out there (and thanks to Dan English for pointing me to most of them). These have been very helpful and I recommend them if you are using SSIS to load a Data Warehouse. And to the authors - thanks for the posts, they have been very helpful!

Deprecate, Depreciate, Discontinue Deciphered

Aaron Lowe — Wed, 14 Jan 2009 12:41:39 -0600

Don’t you just love alliteration? :)

I’ve now dealt with few migrations from SQL 2000 to SQL 2005; SQL 2005 to SQL 2008 and SQL 2000 to SQL 2008. However there still seems to be a lot of confusion around what is supported on which version and whether something is deprecated, depreciated or discontinued. So first off, let’s get some definitions:

Deprecate – To express earnest disapproval of
Depreciate – To decline in value
Discontinue – To put to an end; stop; terminate

Let’s start with the third – Discontinued. These features have actually been removed completely (See Notification Services and * join queries). This will cause an upgrade to break and should be addressed prior to migration.

In the context of SQL Server, Deprecate and Depreciate essentially are the same thing – Features are Deprecated (i.e., we’re encouraged not to use them) because those features were Depreciated (Microsoft has said that they will be removed and/or replaced in future version), however they technically will still work. These are features such as DTS, where SQL 2005 and SQL 2008 can still run them, however no features are being added and it will be discontinued in a future version.

So when upgrading you should be aware that the deprecated code/features can still be used and won’t break the upgrade, but there’s a newer way to accomplish what you need to do and you should migrate to doing it that way because (1) it will be supported going forward and (2) it’s usually a better, more efficient process.

	Deprecated Features	Discontinued Features
SQL Server 2005	http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx	http://msdn.microsoft.com/en-us/library/ms144262(SQL.90).aspx
SQL Server 2008	http://msdn.microsoft.com/en-us/library/ms143729.aspx	http://msdn.microsoft.com/en-us/library/ms144262.aspx

Tools to use for Upgrading:

SQL 2005 Upgrade Advisor - http://www.microsoft.com/downloads/details.aspx?familyid=1470e86b-7e05-4322-a677-95ab44f12d75&displaylang=en
SQL 2008 Upgrade Advisor - http://www.microsoft.com/downloads/details.aspx?FamilyId=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en
SQL Server Upgrade Assistant - http://www.scalabilityexperts.com/default.asp?action=article&ID=43

SQL Quiz: Toughest Challenge

Aaron Lowe — Thu, 11 Dec 2008 12:58:04 -0600

Chris Shaw posted a second SQL Quiz where he asks: "What are the largest challenges that you have faced in your career and how did you overcome those?”

When I was working at the University I was the primary DBA on the system for the student and Employee ID card. This card was swiped for access to dorms, parking, buildings etc as well as it was hooked up to a student accounts for cafeteria, laundry, etc and the numbers were used for websites access. There was a server at each of the three main campus throughout the state and the data was replicated via transactional replication. Being it was a university it was a Database Application that had to have all the business logic in the database as we had to open it up to any platform or data access technology that the individual campuses, departments wanted to use. However if the server was down or unavailable for any reason there were certain applications that didn’t cache data like the cafeteria services. Our service window which allowed downtime was Sunday mornings from 2 am to 4 am. This was run on Windows NT 4.0/SQL 7.0. We eventually migrating to new machines running Windows 2003/SQL 2000 and then upgraded to SQL 2005. Shortly after the upgrade to 2005 we went live with a new version which included utilization of service broker and clr and encryption. So in a little over a year we did a platform migration a platform upgrade and an application upgrade without missing any of target dates or times or affecting out users. Our core technical team was 4 people that was managing the entire process including communication to clients. Documenting and scripting each step along with running multiple tests and involving our clients in the tests as well to set everyone’s expectations made the whole thing a success.

I don’t think I’ll be shocking anyone by saying that in the consulting world sometimes you go onto projects in areas or technologies that you aren’t necessarily an expert in and therefore are sometimes learning on the job. For example I knew DTS fairly well, however when I went on my first gig that was teaching and doing SSIS I wasn’t extremely knowledgeable about it. However I crammed and learned about SSIS and the client was extremely happy with what I brought to the table. So sometimes when I’m billed out as an expert, knowing how to get up to speed and being able to is just as important a skill to have. And twitter has made that just more easier and fun!

Anyway that’s the two that really come to mind.

Tagging Dan English

SQL Quiz

Aaron Lowe — Thu, 11 Dec 2008 12:19:20 -0600

Evidently while I was on vacation I missed Chris Shaw ask for us to share our mistakes, however now that I see it, thought I’d jump in.

My first full time job I was employed as a System Administrator. I soon realized the purpose of this generic term was because I was the only technical guy on site supporting a company’s sales office of around 20 users and as a result I had to maintain NT 4, SQL 6.5, Exchange 4.0, Win ‘95, Office ‘97, Goldmine, Veritas Backup, etc. This office also housed the primary dns information and our website and exchange. The primary office which was around 60 people at the time was in another country with a time difference of 6 hours.

Anyway I had weekly calls with the senior sys admin who was telecommuting from his house (1 timezone away) and one of the items that had been on the to do list for awhile was upgrade the Exchange server from 4.0 to 5.5. Not liking things on my to do list for very long I kept asking about it and was told it was no big deal and when we get some time we will do it. Well being about a month out of college and wanting to make a good impression I realized I had some time one late afternoon and figured as it was no big deal, I’ll just pop in the CD and go for it. I mean it’s an upgrade right, not like a new install where I’ll have to configure a bunch of stuff.

Well suffice to say the upgrade actually crashed partway through and NT 4.0’s Dr. Watson faithfully kicked off. Not really understanding anything about anything I rebooted and tried it again at which point I received the same result. So I remembered being told we has backup tapes and I should be able to restore if there’s a problem, so I popped in the backup tape and starting restoring. Once the restore was done I tried to access my email and still no luck. Time to call someone, but as it was no big deal I wasn’t even worried at this time.

Well calling the senior sys admin they gave me a little better perspective of how big the deal was. And they were even gracious enough to inform me that I really shouldn’t have done the upgrade by myself and that the backups I had just restored weren’t of the Exchange server, they were of the file server so not only did was not have Email working for the entire company, we had also just lost the sale’s work for the entire day.

It was at this point I immediately starting doing multiple things at once. I started sweating. I started feeling sick to my stomach. I started worrying about my job, and subsequently my bills (I had just gotten my first mortgage earlier that month). I started trying to figure out what I could do to solve the problem.

The next 26 hours went by like a blur, but I can say that I ended up doing those four things a lot. I got on the phone with MS Support (paid-for incident). Finally we got Exchange Server running after using a low-level utility which investigated every record in the Exchange data store and if that record wasn’t perfect it was deleted. We went from about 3GB data store to about 650 MB data store. I quickly wrote up a post-mortem (although I didn’t know it was called a post-mortem at the time) and sent it out. I actually shared an office with the COO and co-owner of the company at the time due to space restrictions and I remember hitting the send button on my email and booking it out of the room on an ”errand”. However after a discussion of the post-mortem with management they ended up using this as a learning opportunity for me and I ended up continuing to work there for another 2 years or so.

While I have made mistakes since then, none have every been to this level and therefore I usually keep a nice cool head on my shoulders in the face of problems. :)