change service account

Question

change service account

sqlfriend

SSC Guru

Points: 52794
More actions
March 30, 2011 at 3:55 pm

#235972

We have a sql server 2008 production box that has a service account that the same as dev box.
We would like to change it to a different account.
But the problem is we know there is aother server is using the same account to access one database on this server. so we would still want to leave the old account login there for a while for its access to the data.
I know I can change the service account from configuration manager, and it will automtically assign access to the new account to sysadmin role,but I see it will also disable the old service account.
see http://msdn.microsoft.com/en-us/library/ms345578.aspx
But we do want to keep the old login for other server's access. How can we make this work? to enable the login?
Also I found strange in this article, it says it will give the account sysadmin server role. But I read a lot before that we should give least privillages to the sql service account , why is this contradict?
Thanks
Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

annasql (3/30/2011)
But we do want to keep the old login for other server's access. How can we make this work? to enable the login?

After changing the service account, create a new login for the old service account.

Also I found strange in this article, it says it will give the account sysadmin server role. But I read a lot before that we should give least privillages to the sql service account , why is this contradict?

I think the "least privileges" is referred to the Windows privileges, not the SQL privileges. AFAIK, the service account must be granted the sysadmin role.

-- Gianluca Sartori

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thank you,

Is there a way I can do a query or somewhere in management studio, I can know the service account is a sysadmin role?

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

This query will return all your sysadmin users:

SELECT SP.name, type_desc

FROM master.sys.server_principals AS SP

INNER JOIN master.sys.server_role_members SRM

ON SP.principal_id = SRM.member_principal_id

WHERE role_principal_id = (

SELECT principal_id

FROM master.sys.server_principals

WHERE name = 'sysadmin'

)

AND is_disabled = 0

-- Gianluca Sartori

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

I just found out we have a group in the login list called NT service\SQLServerAgent. It has sysamin role in it.

Then I become more confused. A couple of days ago we run an ETL from another server using the same service account to access data on this server.

What we did is we specifically add this service account to login and give it rights of data reader.

But the ETL failed, then we add dbowner rights to this service account, then run it again, this time it succeed.

My question now is , because it is also service account, so it is already a sysadmin role, so it should have worked, why after we add dbower it worked.

Thanks

Perry Whittle SSC Guru Points: 234065 More actions · Answer 5

annasql (3/30/2011)
but I see it will also disable the old service account.
see http://msdn.microsoft.com/en-us/library/ms345578.aspx
But we do want to keep the old login for other server's access. How can we make this work? to enable the login?

the login is only disabled inside the sql server instance, it does not affect it at domain level so if you are using the same account for another sql server instance on another server it will be fine

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

But we use that the same service account on another server, and in the ETL run on that server will use this account to access data on this server.

Perry Whittle SSC Guru Points: 234065 More actions · Answer 7

annasql (3/31/2011)
But we use that the same service account on another server,

yes and that wil be fine, the service account is only disabled for use inside the sql server instance where you cahnge the account info. It does NOT affect other instances that use the same service account

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

But we specifically add the account to the login on this server and make it dbowner for the other server to access data on this server. but if it is disabled, then it cannot access the data.