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.
