SQLTeam.com | Weblogs | Forums

Renamed server, user rights messed up


#1

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


#2

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


#3

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.


#4

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


#5

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.


#6

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


#7

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.