Renamed server, user rights messed up

Afternoon all,

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?

OS Win Sever 2008R2 & SQL Sever 2008 R2.

Thanks

Dave

Please define my user account? what kind of user is this?

When you say you're user account, is that in the form of "domain1\user"? If so, accounts from the old domain won't be able to log in.

1 Like

Sorry guys I've been a bit vague there.

Server was originally called 'W2KR2-DB-Server'
Server is now called 'DB-Server.DOMAIN'

I was using windows authentication in SQL Management Studio, so my username was 'W2KR2-DB-Server\Administrator'.

I now logon to the Windows Server with 'DOMAIN\Administrator'.

From SQL Management Studio do i delete the login for 'W2KR2-DB-Server\Administrator' and do I manually create a user 'DOMAIN\Administrator' ?

Thanks

Dave

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....

Thanks

Dave

Morning all,

I'm sure I've messed something up here.

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...

Any idea what I've missed?

Thanks

Dave

image

No errors in event viewer.