June 26, 2014 at 10:36 am
Does a dba who had SQL admin access require any NTFS rights to perform things like db backups and/or restores?
Also any other suggestions for any rights that might be required of a dba? Without providing Administrator access to the server.
June 26, 2014 at 11:19 am
Probably not much needed at all most of the time. You do need access to the file system to look at the backup files and any other load or processing files. Other than that, most of the time, no.
But...
Others are likely to disagree.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 26, 2014 at 11:35 am
Honestly the DBA should have admin access to the server to have maximum effectiveness.
If you don't, and in particular if you don't have direct directory access/control, be extraordinarily careful before detaching a database. Detach now changes the security settings on the SQL files, and you have not be able to access them if you don't have direct authority.
Likewise, I'm not sure how you'd easily and efficiently move a db from one drive to another if you can't directly copy the files. Instead of OFFLINE the db and copy the files, you'd likely be forced to backup and restore, which will take considerably longer, getting worse the larger the db file(s) are.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 26, 2014 at 11:36 am
the question sounds a little bit like a turf war...don't want the dba to have full access to the box?
the answer is it makes my job substantially harder if i don't have local admin access. zero NTFS permissions? I doubt he could accomplish much.
assuming the DBA does not have login permissions for remote desktop, nor full access to the disk, consider this:
you have to grant permissions to the dba for some shares, no doubt about it in my opinion..maybe not the whole drive, but certainly locations related to backups, logs, ssis packages, SSAS abf files, and locations where SSIS packages reside on disk, and destination folders that SSIS packages might use if they process files. all those locations might need to be accessed , and not via the GUI/account running SQL services.
so you set up those shares correctly, and most of the time, the limited DBA would be able to get the job done. he might bitch and whine, might call you at midnight because he needs a backup that was copied somewhere and needs to be copied so he can access it, but most likely, after setting up permissions to SQL related fodlers, he'd be ok.
if the SQL Service goes down, so he cannot access via SSMS , and he cannot remote desktop to the OS to diagnose service issues, that will fall in your lap instead of the DBA's, so keep that in mind.
things like WMI queries via apps, powershell, or a monitoring instnance to query the health of the box and services might not be possible, so his ability to administer and monitor remotely would be degraded as well.
you won't make any friends doing it that way, but yeah, i can see it being done.
At my shop, I have full local admin access to each SQL Server I administer, but that's like 30 servers out of hundred of servers or workstations. might just be my shop, though. I'm under the impression it's common for the DBA to have admin on boxes he administers, but not all boxes on the network.
Lowell
June 26, 2014 at 11:43 am
Yes I think the commands in SQL run execute as the service account log on as (system) so I think backup and restore would not require any additional NTFS rights.
In a split duty environment you have to do things like control administrator access. Some things a dba may not be able to do like maybe one with administrator access. Anyways not going to explain it all just looking for insight to this.
Any recommendations in being able to keep the dba from filling up the disk? I have seen mention of quotas but this runs as the system account which is also what the DB is going to run as when growth has to happen.
June 26, 2014 at 11:47 am
I forgot about PowerShell. You need to enable permissions to that in addition to giving them the access to the locations of the backups. I'd also enable remoting for PowerShell. But I successfully administered servers for years without admin rights. There's adjustments that have to be made, but you can deal with them as you hit them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 26, 2014 at 12:25 pm
Also, be sure the relevant non-DBAs properly enable IFI and other local/policy/Windows permissions that are needed for SQL accounts, proxy accounts, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 26, 2014 at 2:26 pm
Dba has the ability to control the mssql services. They have the ability to rdp but have standard user rights once on the system. They have SA level access once in SQL.
I don't believe they need it but I gave them full NTFS permissions to the sql data folder and the backup location.
I will have to research the other items. Again thank you and I think this is valuable information.
Again if the business is telling me they are not to have admin access because of segregation of duties then I have to work to get them most of what they need to be able to do and not provide admin access.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply