This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo
schema?
The only way to specify a default schema is to :
- use SQL logins (not Windows)
- run as "sysadmin"
Neither of these is acceptable
Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.
Also, deliberate schema use is better for SQL Server 2005:
- tables in
Data
- other tables in
Archive
, Staging
etc
- code in schemas per client permissions:
Desktop
, WebGUI
etc
Using the dbo schema is so last millenium :-) Links:
To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue:
1.> Right-click your drive, select properties, select security, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this location text box, but rather your computer name):
2.> Click the Check Names button to validate that the account is valid:
3.> Now, add the file permission you need to the SQLSERVERAGENT account. For trouble-shooting purposes, you may want to give Full control and then scale it back later as needed:
That being said, you probably just need to use SQL Server Configuration Manger to re-add the SQL Agent user--according to the comments I saw about msdb and logins. Configuration Manager makes more changes to SQL Server than using the Windows Services applet--so Configuration Manager should always be used to change ANY SQL Service.
This will fix the issue if someone may have changed the account within Windows Services causing the service to fail on startup. You need to reset it within Configuration Manager. Doing so allows Configuration Manager to add to SQL Server the much needed permissions to manage the MSDB database for the Local Service account (NT SERVICE\SQLSERVERAGENT) whereas changing accounts within the Windows services applet does not.
Caveat: Versions SQL Server Express above 2000 do not include a SQL Agent. Certain aspects of it may appear to be there, but its unusable in the Express version of the product.
To begin, open SQL Server Configuration Manager and double-click the SQL Server Agent service in the SQL Server Services. Select the Built-in account radio button and choose Local Service, and click the Apply button. Important : if you already see that this account is selected chose another account and click the Apply Button. Then, change it back to Local Service and click the Apply button to allow Configuration Manager to add the correct MSDB permissions for the SQL Agent service to start. Now, restart SQL Server Agent to reflect this new setting.
Best Answer
If you're using IIS manager for application pools to configure identity for your application and your servers are Windows 2012 or later, you can look into using either a Managed Service Account or a Group Managed Service Account to run your app. Either approach will eliminate the need to deal with passwords yet maintain a secure application login.
The comment provided by sepupic above is spot on though in that you should manage user permissions directly against the database separately, and how that's done will heavily depend upon what level of permissions are needed.
Hopefully this provides an approach to your situation though.