September 4, 2019 at 9:36 pm
Removing the SA rights and granting developers to db_owner role on Dev servers. What roles would be needed to do all of the following, also which are appropriate for a Dev to have and reasonable to request from a DBA.
Following is the questions and answers from me, do you agree with me answers?
Creating and running SQL Server Agent Jobs and viewing logs – SQLAgentReader role
Tracing to troubleshoot deadlocks - ? I am not sure we can grant specific role to tracing or troubleshoot deadlocks
Enabling things like Sql Server Service Broker, or other server level settings – Not sure if there is a specific role, Should be done by DBA
Taking /restoring from backups – this sometimes involved security manipulations i.e. restore a prod backup, but with dev user accounts – Should be done by DBA or database level permission(db_backupoperator) or db_create role.
Thanks in Advance!
September 5, 2019 at 5:05 pm
It depends.
What are the "rules" in your organization? Here, the devs are admins in the dev environment. They can do it all. Most of the time, I have "signoff" on anything new being done. There is a good relationship with the devs and the DBA (me!)
As for restoring backups, do you currently allow production backups to be restored to dev? That's a very big issue. We do not allow it in any way shape or form. On the very occasional time when this may be needed to diagnose an issue, a process needs to be followed that requires approvals, and data scrubbing.
Nobody in the organization has access to production backups except for about 10 administrators. The devs have no idea where they may be kept!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 5, 2019 at 7:13 pm
Thanks. Do you have any comments on other items?
September 5, 2019 at 7:17 pm
Keep in mind you can also create a role for the developers and grant whatever permissions to that role. Deadlocks depends on what it means to troubleshoot - it doesn't have to be trace. You can use extended events or just check the system_health session. Or you can log the deadlocks. Changing server settings can impact other things on the server. And other databases on that instance which the developers may not work with or have much knowledge about. So it depends on the development and the instance. For restores, you can look at creating a job, stored procedure or whatever process to handle all the things that need to be addressed and allow the develops to fire off that job or stored procedure. And that really depends on what the requirements are for the restore - especially if it involves data scrubbing. So as Michael already said, you get that wonderful reply of "it depends".
Sue
September 5, 2019 at 8:14 pm
Removing the SA rights and granting developers to db_owner role on Dev servers. What roles would be needed to do all of the following, also which are appropriate for a Dev to have and reasonable to request from a DBA.
Are you sure you want to do this in DEV? The one thing you want to prevent is the DBA becoming a bottleneck for development. For example, the dev environment is for experimentation, trying new things, breaking things and making sure that they can be fixed.
Creating and running SQL Server Agent Jobs and viewing logs – SQLAgentReader role
Pretty straightforward. But SQLAgentReader does not allow you to create or modify jobs, only jobs that they own
Tracing to troubleshoot deadlocks - ? I am not sure we can grant specific role to tracing or troubleshoot deadlocks
You can grant permissions to run traces, extended events, and query store. But, unless they know what they are looking for, that may be of little use. Install Bret Ozar's blitz procedures. That's probably a good starting point for a dev to start troubleshooting
Enabling things like Sql Server Service Broker, or other server level settings – Not sure if there is a specific role, Should be done by DBA
Service Broker is more of an "architectural thing" to me. If they want to use it, then they probably should ask for it to be enabled. How it's used is probably up to them, but i suggest you be very involved with that. It can quickly run amok! Been there, done that!
Taking /restoring from backups – this sometimes involved security manipulations i.e. restore a prod backup, but with dev user accounts – Should be done by DBA or database level permission(db_backupoperator) or db_create role.
Your backups should already be automated. And, in dev, they should be in full recovery with frequent log backups. Why? Did you ever have a developer run a delete query or truncate table and need it back ASAP because any development is halted until the system is back? I have. It's a lot of money lost when developers are working to re-build an environment
Restores are another issue. I would certainly allow them to restore a dev backup, and I would suggest that they leverage snapshots also. As Sue said, you could write a proc (or re-use one that already exists) to give them this ability.
As for restores from production, if they can currently do that then you have far more issues than this post can cover. I would stop that immediately.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 6, 2019 at 2:43 pm
Thanks Michael and Sue! I really appreciate for all your inputs.
So you are suggesting it's okay to be granted the following in DEV servers?
SQLAgentUserRole
SQLAgentOperatorRole
permissions to run traces, extended events and query store
allow them to restore a backup in Dev
allow to check the system_health session
did I miss anything?
September 6, 2019 at 2:58 pm
Hoping someone can help me understand something that popped up on my pc this morning:
C:\windows\system32>REM Find out if the user is logging on from home:
C:\windows\system32>set : find /i "remote=true" 1>NUL
C:\windows\system32>if NOT errorlevel 1 goto RAS
C:\windows\system32>REM Microsoft Systems Management Server (start)
C:\windows\system32>REM SMS Build 1493
C:\windows\system32>rem call "\\Dc-hq-02\netlogon\logon.bat"\. .\smsls
C:\windows\system32>REM Microsoft Systems Management Server (end)
Running Super Logon Script....................***
The network connection could not be found.
More help is available by typing NET HELPMSG 2250.
System error 53 has occurred.
The network path is invalid for \\kangaroo\public.
Enter the user name for 'kangaroo' :
Does this mean I have tracking/monitoring software on my computer? I really appreciate any insight someone can provide.
September 6, 2019 at 3:01 pm
Hoping someone can help me understand something that popped up on my pc this morning:
C:\windows\system32>REM Find out if the user is logging on from home:
C:\windows\system32>set : find /i "remote=true" 1>NUL
C:\windows\system32>if NOT errorlevel 1 goto RAS
C:\windows\system32>REM Microsoft Systems Management Server (start)
C:\windows\system32>REM SMS Build 1493
C:\windows\system32>rem call "\\Dc-hq-02\netlogon\logon.bat"\. .\smsls
C:\windows\system32>REM Microsoft Systems Management Server (end)
Running Super Logon Script....................***
The network connection could not be found.
More help is available by typing NET HELPMSG 2250.
System error 53 has occurred.
The network path is invalid for \\kangaroo\public.
Enter the user name for 'kangaroo' :
Does this mean I have tracking/monitoring software on my computer? I really appreciate any insight someone can provide.
No, it means you have hijacked this thread that is about a subject completely unrelated to the subject in this thread.
Please post to a new thread. You will have a better chance of getting an answer.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 6, 2019 at 3:05 pm
Michael, this is really doesn't make sense to me, why does someone put something unrelated question to the subject in this thread instead of posting new thread? This is what i put and it get's overlooked due to the above unrelated question in this thread.
Thanks Michael and Sue! I really appreciate for all your inputs.
So you are suggesting it's okay to be granted the following in DEV servers?
SQLAgentUserRole
SQLAgentOperatorRole
permissions to run traces, extended events and query store
allow them to restore a backup in Dev
allow to check the system_health session
did I miss anything?
September 6, 2019 at 3:08 pm
I'm sorry- moronic, I know. I couldn't find the link for a new post.
September 6, 2019 at 3:28 pm
Michael, this is really doesn't make sense to me, why does someone put something unrelated question to the subject in this thread instead of posting new thread? This is what i put and it get's overlooked due to the above unrelated question in this thread.
Thanks Michael and Sue! I really appreciate for all your inputs.
So you are suggesting it's okay to be granted the following in DEV servers?
SQLAgentUserRole
SQLAgentOperatorRole
permissions to run traces, extended events and query store
allow them to restore a backup in Dev
allow to check the system_health session
did I miss anything?
We can certainly say yes or no to what you are planning. But really, it's up to you to decide what works for your organization.
This appears to be ok, again, assuming that this is what will work.
The VERY generic rule of thumb is lock it down completely, and grant what is needed as it's needed. Usually that's not practical. Also, think about your workload. If most of these things are now going to your list things to do, will you be able to handle the extra workload?
Honestly, it sound to me like you are in a situation where you need to educate, not legislate.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 6, 2019 at 6:50 pm
Thanks a lot!
On side note, just checking to see if you have came across with this any chance. I agree it has to be different thread but just checking to see if you think of anything else?
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 'An error occurred while receiving data: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
Database mirroring is inactive for database 'DBName'. This is an informational message only. No user action is required.
Could be server load, but I'd lean towards network issues. Do you agree with this? If yes, other than fixing the network issue is anything else we can do on our side?
Thanks again for your time!
September 6, 2019 at 7:12 pm
Is this mirroring, or an availability group?
Run the validate cluster utility if it's an AG. It will tell you to raise(or lower!, I can't remember) one of the timeout settings.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 6, 2019 at 7:21 pm
It is mirroring not AG. But do you think these errors are because of Network issues right? The reason i am asking is,it cause application issues, so i am trying to identify this network issue is the cause for mirroring and Application as well.
September 6, 2019 at 7:39 pm
After looking into the logs on DR server, it started with login failed for the application account. Next
Database mirroring is active with database 'DB Name' as the mirror copy
After approx 40 mins then below error is noticed...
Database mirroring connection error 4 'An error occurred while receiving data: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply