Security policy may include any of these answers as one of the gateways for approving requests for elevated privileges. Ultimately though, the DBA is the designated protector of the data and as such should be the final say-so for whether or not the permissions request is necessary and justifiable.
LocalService and NetworkService both have higher privileges than SQL Server needs. LocalService has highly privileged permissions on the local server, and NetworkService has permissions by default on the network. Both of these accounts are also easily impersonated by the mere fact that someone that gains access to the server can configure something to run as those accounts as their passwords are unknown.
A user domain account should not be used because it is tied to a person who could leave the company. Additionally, and more importantly security-wise, the account has a permission that non-user domain accounts do not need: interactive login. Interactive login means that a person can log in to the machine using that account. The account should not be tied to a user to prevent someone from using it to gain access to the server.
The SQL Server Agent service requires sysadmin permissions within SQL Server though depending on how you use the Agent service, you may not actually end up needing it. Some Agent activities do require it though.
Many people assume that the SQL Server service needs sysadmin as well, but it actually requires no permissions whatsoever within SQL Server. The confusion is born out of the fact that many people use the same service account for both services and the SQL Server service ends up with sysadmin service. However, the security best practice regarding service accounts is to use different accounts for the two services. If you follow this best practice, you should only grant permissions for the SQL Server Agent service.
Using the non-default port came about as a best practice in part because there were attacks more than a decade ago that attacked port 1433. The attacks were not very well done and also depended on the sa password being left blank. Modern-day attacks do not hard code the attack vectors and instead scan for an open port.
Using the non-default port may slow down modern day hackers, trojans, and worms for a matter of seconds. Zero-day vulnerabilities are previously unreported vulnerabilities and any modern-day attack will only be slowed momentarily by the port not being the default.
The key take-away here is that not using the default port provides only very little protection from an attack, and it is important to use firewalls and other protections as well. This is not a solution on it's own, but it may be part of your overall solution.
Creating a second account with elevated permissions for the user would not be a best practice because you are not able to prevent that user from using it when they should not.
Granting execute on the procedure to the user would not work in most cases as system procedures that require sysadmin permissions generally have hard coded permissions checks within them to validate that the caller is a member of sysadmin.
Adding someone as a db_owner to a system database would not be a recommended best practice for any reason and also would not allow the user to execute a procedure that requires sysadmin permissions.
The recommended approach would be to create a procedure that executes as a sysadmin user or sa and grant the user permissions to execute that procedure.
If non-production does not have current data, this is easy to solve by restoring a copy of the production database.
Simply saying that someone authorized the access does not address the needs for the access to be necessary nor justified. Just authorized.
As stated in the request, the hardware merely makes API calls. It is not difficult at all to whip up some code to just make some API calls. This is not necessary.
PCI compliancy will be a show stopper for a lot of permissions request, but as long as all relevant policies are followed, you can still be compliant by granting this access request. In this case, it is necessary because there is not a sufficient non-production environment, and it is justified because someone has to have this access to fix the error and because it is temporary only for this specific reason.
The Trustworthy database property opens a number of security holes within SQL Server and most scenarios where people use Trustworthy can be handled in a more secure fashion such as using signed stored procedures and signed CLR modules.
The cross-database ownership chaining setting allows permissions to be granted to users who in other databases without them being granted permissions in that database and there are more secure ways to handle this such as stored procedures that execute as a specific login.
OLE Automation procedures allow the calling user to execute code directly against the Operating System as the SQL Server service account. This would allow a non-admin person to have unfettered access to the OS.
CLR enabled server configuration allows the use of CLR on databases. Although there a lots of cases where using CLR is safe and makes perfect sense to use, but until and unless it is specifically needed, the setting should remain disabled.
The correct answer is none of these.
The documented permissions required within SQL Server for the SQL Server Agent service is sysadmin, but no permissions are required within SQL Server for the SQL Server service.
All of the other statements are true. This is why it is a best practice to only give sysadmin permissions to those who really need it.
Windows integrated security requires that users who use the application or run the report have access to the database. However, if the users have no need to access the database outside of the reports or the application, they should not be granted permissions to access the database at all. SSRS should use a stored service account and the application should use an application service account. Access to the reports should be handled via SSRS permissions and access to the application should be handled by the application.
Distribution groups cannot be added to SQL Server, only security groups can. As a best practice users should be granted access by adding them to the appropriate security group.
A member of the db_owner group (database owner) has full permissions within the scope of the database including modifying database properties and setting, granting any and all database permissions, and managing users. A database owner has the ability to completely destroy a database.
Luckily, one key security setting that for the database that requires sysadmin permissions to set is Trustworthy.
These potentially harmful permissions is why it is a best practice to not grant db_owner permissions unless it is really required.
Share your Results :
Share your Results :
Share your Results :
Share your Results :
Please share this quiz to view your results.