Link to home
Create AccountLog in
Avatar of K_man
K_man

asked on

Server Administrator account doesn't have sysadmin (sa) rights to SQL Server that is set on 'trusted connection'

I -think- I might have changed the SA password to something other than the default and the password used by the server admin user.

When I log in as administrator to the server and try to do sa type things on SQL Server I get "only members of the SystemAdministor role can access this feature"

I think I know roughly what the password was changed to, but I don't know how to attempt to log on as SA using osql I get an error saying that I have to be associated with a trusted connection.

How can I get this back to working?

Help would be hugely appreciated

-K
Avatar of chapmandew
chapmandew
Flag of United States of America image

Do you, or anyone else you know, have admin rights on the database instance?  I assume that you do seeing as you changed the sa password.  This should do it for you (if you can run it)


alter login sa with password = 'newpassword', check_policy = OFF
Avatar of K_man
K_man

ASKER

I'll give that a go when a guy gets back, but in the meantime is there no way I can get somewhere and throw passwords at the SA account ? is there no way to try and log in as SA ?

K
There is no automatic way that I know of...in fact, depending on the policy, too many wrong attempts may lock the account.
Avatar of K_man

ASKER

Hi again, I had another look at it and the Builtin/Administrators account is missing from the login list, I'm assuming this is a huge problem yes ?

I was assuming up to this point that all of this was because of something I did, but I don't know what woul dmake me remove that account.

A while ago (6 months) the IIS install on the server (SBS 2003) went bad and had to be redone, it had its hooks into every thing and it took a microsoft support chap about 10 hours to get it all running again.

Could this possibly have something do with it ? (liability is an issue :)

To get the administrators back would I need to change the authentication mode to SQL server and then log in as SA ?

also if I change the authentication mode from Windows to SQL woudl I be able to switch it back after if things don't get better ?

-K
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of K_man

ASKER

we ended up reinstalling SQL server for the one the initial problem was with , but I used the query you supplied on something else a couple of days later ! thanks fo the help there.