Limiting Database Permissions for DLM Dashboard

Steve Jones, 2016-02-18 (first published: 2016-02-11)

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.

2016-02-04 17_32_21-New notification

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.

2016-02-04 17_08_18-Movies & TV

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.

2016-02-04 17_09_02-Movies & TV

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

2016-02-04 17_09_23-Start

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.

Filed under: Blog Tagged: DLM Dashboard, Redgate, security, syndicated

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads