Voice of the DBA

Tag: Redgate

Limiting Database Permissions for DLM Dashboard
I was talking with some of our support people recently about permissions on DLM Dashboard. A client was having issues, and we weren’t sure what was wrong. As a result, I decided to dig in a bit and see how limited I could be with permissions for the login/user that is used to track changes.

My first step was to create a new login in SQL Server, giving the public server role and then granting very limited permissions in master and the Redgate database. Those permissions were:
- master – VIEW ANY DEFINITION
- master – execute on dbo.RG_SQLLighthouse_ReadEvents
- RedGate – SELECT ON SQLLighthouse.DDL_Events
That’s a nice, limited set of permissions. You do need sysadmin for setup, but after that, you can set these permissions for the user that you’ve configured in DLM Dashboard. The permissins are documented on the DLM Dashboard documentation site.

In my case, I have a login/user, DLMDashUser, configured in the tool.

I then went to add a new database on my local instance. However since this login isn’t mapped to a user, nor has any high server privileges, I got an error.

To fix this, I connected to my instance and modified the user. Scripting is a better way to do this, and in my case, I used this script:

USE Puzzles
GO
CREATE USER DLMDashUser FROM LOGIN DLMDashUser;
GO
GRANT SELECT ON sys.sql_expression_dependencies TO DLMDashUser
GO

This grants the necessary permissions to a new user in this database. You can save this script, which is especially handy for production systems where we don’t want monitoring tools to have elevated permissions.

Now when I go to add the database, I click add and it works.

And I can then see the database in my monitoring dashboard.

The principle of least privilege should apply everywhere, certainly in production, but also in development. If you limit permissions in development, you might cause a few headaches, but you’ll understand the issues and solve them early on. More importantly, if you have security flaws, they aren’t in production systems where data is exposed.

SQL Server security isn’t that hard, but it can be cumbersome. Set it up properly in development, keep your scripts (even from the GUI), and then use those scripts for your production systems.

NOTE: Typically I’d create a role for this system, which is perhaps what I should do. Having a role like this would make switching users in DLM Dashboard at some point much easier.

CREATE ROLE Monitoring
GO

GRANT SELECT ON sys.sql_expression_dependencies TO Monitoring

GO
ALTER ROLE Monitoring ADD MEMBER DLMDashUser

In fact, I just changed to use this role, and added the role to the other databases so that my dev system is propery set up.
February 11, 2016
Is Your Deployment Process Perfect?

I’m sure your deployments aren’t perfect. I’ve had many successful ones, but I have always found some flaws in my systems over time. I’ve also found that the less flexibility I allow, the more likely things are to succeed.

However less flexibility isn’t what most companies or clients want. In fact, they want the opposite. I know most developers want this as well, without constraints on what they can do.

I think Redgate has been working to make things easier and more reliable for most companies. Certainly we have work to do and there are places to improve our products, but our DLM work on various products is really designed to reduce, or eliminate, silly mistakes that cause issues. We’ve tried to help companies set up a process that limits regression issues.

I saw this short video from Redgate, where the lead Data Anlyst at Yorkshire Water talks about some of the improvements in their development process come about by implementing a DLM process. Over a couple of years, they’ve gotten many of their projects into a Continuous Delivery stack and that’s just the way they do things.

Yorkshire Water

The time savings are the big issues. There is less copying of production data around, more confidence in development, and as Shaun notes, time is money.

If you think you want to build a more repeatable, reliable database development process, I think we give you a few options. The traditional, work in SSMS approach with SQL Source Control, or our Ready Roll work in Visual Studio. I’m particularly excited about Ready Roll as that’s how I’ve done a lot of development in the past.

And, of course, I think our DLM Automation suite, allowing you to deploy changes to a CI environment and build deployment packages works well.

January 6, 2016
The Database is the Cornerstone
I saw a case study video the other day from Redgate Software. It was a look at one of the upper management on Fitness First, a fitness/gym company that has facilities in dozens of countries. It’s a good, short summary of the benefits they’ve seen from implementing DLM and ALM, with the DevOps Guys.

It’s worth a watch, and I especially like that this manager understands and gets that the database is the cornerstone of better software deployment. That’s his quote. Without being able to be flexible and agile with the database, it hampers your flexibility.

Fitness First at SQL in the City

I also appreciate that they do regression testing in an automated fashion. It helps them reduce the number of bugs and speeds their ability to deploy. They also have moved to curated sets of test data to allow them to test known cases and bugs they’ve seen.

They also “run at speed”. They meet the business needs, they can work quicker to meet client demands. That’s important.

Fitness First also did a presentation at SQL in the City. I sat in and was impressed, especially with some of the work from the DevOps Guys. It’s long, 40min, but interesting.

Fitness First Case Study

I wasn’t sure what to think when it started, but by the end, I think that this is a good view of how DevOps can help. It probably is more valuable to you if you have a backlog of work, but even if you want to develop and deploy at a pedestrian pace, having a DLM/ALM setup can build your confidence and reduce bugs.

There are a few other short videos that showcase what some of our clients think of DLM and automated database deployment:
December 22, 2015
Git Pull in SQL Source Control

I wrote recently on Git Push in SQL Source Control (SOC), and now wanted to add the other side of the story, pull. This process is even simpler.

I’ll be working with a new database, one that has two client systems set up already with Git support. I know I haven’t tackled that part of SOC and Git, but I’ll do that in a new post.

In this case, I’ve got a repository on Github for my Redgate and SQL in the City demos. It’s a sample database, but I’ve been making multiple changes to the database across time, using Version Control as a way of continuity, and then being able to rollback changes or see history.

I’ve got a connection in a VM to a second development machine. When I launch my VM, and SSMS, I go to the second SOC tab, the one that is labled “Get Latest.” You can see the image below, and notice that there are two buttons in the upper left. There is the familiar “Apply changes to database,” which has been the way that we pull changes from TFS and SVN, but now there is a new “Pull from remote respository.” This is the one specific to Git.

In this case, I’ve refreshed my database previously in sync with the local Git repo. Therefore the button is grayed out. However, I don’t know if there are remote changes until I click the other button.

I do that and get the familiar four step dialog from SOC that checks for changes and then compares them to the local database. However what you don’t see is that a “git pull” is issued first, pulling changes from the remote repo. In my case, I had one change I’d made in London, actually, and hadn’t synced with my VM.

This is the familiar SOC actions now where I see the changes and can use the “Apply changes to database” to compile this code in my local database.

In some sense, this is less exciting then push, but it’s nice to be able to do this in the client.

I’ll write some more on the workflow as I develop changes in the coming posts, which are going to be aimed at building a small database in Azure SQL database and moving changes from my local system to Git for tracking, and then to Azure for production use.

November 12, 2015