I reciently renamed one of my SQL test servers and joined it to a domain, I then found out that my user account to login to SQL Management Studio & the back up job permissions have got a little messed up. The 'sa' account sill logs in with full privs.
Its not the end of the world as its only a test box but i'm quite keen to learn from this. Any easy way to fix the user account permissions?
is your domain W2KR2-DB-Server? no, so W2KR2-DB-Server\Administrator is not a windows AD User. It sounds like a local user on the machine only. What you should do is add your actual Windows user name or another option is, create a role and add your Windows user name in that role.
Correct, its not a Windows AD User, the original user before it joined it to the domain was 'W2KR2-DB-Server\Administrator'.
Anyhow... Using the 'sa' sql user & password I have deleted the user 'W2KR2-DB-Server\Administrator'.
I have also created the user to match my AD user 'DOMAIN\Administrator'. I have given the new user 'sysadmin' under 'Server Roles'.
I logged in to SQL MS with the new user 'DOMAIN\Administrator' using windows authentication and I can execute queries but seem to be having problem with creating a backup job. I'll restart the sever shortly and post back with errors if its still not working....
OK, so with SQL Server Management Studio running locally on the DB Server I can log in with the 'DOMAIN\Administrator'. For the most part every thing looks good, however, if I create a job (backup job for instance) although the job is physically created it fails with permission errors.
If I create the job whilst logged in with the 'sa' account the the job is created & runs perfectly...