Author |
Topic |
NickyJ
Starting Member
46 Posts |
Posted - 2007-04-30 : 12:05:31
|
I've registered a server through EM using the SA login, now when I go to view Server Roles none of the fixed server roles are visible as in sysadmin etc. Also I am unable to add a new login or go to properties of an existing login, as if the SA login has lost its sysadmin rights ??Any advice greatly appreciated |
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-30 : 13:12:43
|
Double check registration properties. Did you see sa in logins? |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2007-05-01 : 05:21:15
|
Yes SA login is there, though when I go to Properties I only see General Tab and no SErver ROles or Database Access tabs ?? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-01 : 10:25:48
|
If you see SA instead of sa, may have problem. Try logon using id with sysadmin rights to double check. |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2007-05-03 : 07:02:45
|
sa is there, sorry should have pointed out the no capitals, however New Login option is greyed out and also if I register the box under my domain account which is anm admin account then again I have no admin rights, however if I log on via Query Analyser as sa or my domain account then I do have admin rights and can add users, so it's just through EM that the permissions appear wrong and all server roles are NOT visible... |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-03 : 09:04:31
|
Is it on client machine or on the server? Tried em from other sql client machines? If works on other machines, just reinstall sql client then apply sp. |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2007-05-03 : 09:56:58
|
Worryingly it's on all clients and on the server, I'm thinking restore Master DB ?? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-03 : 13:05:59
|
No, it's not master db issue. Try reinstall sql client first and apply same sp as server has. |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2007-05-15 : 06:58:07
|
Did re-install client and still experienced the same issue. Have looked into this more and have now discovered that many of the master system tables are empty ?? spt_values is empty and believe this is used to link with sysxlogins table to return role permissions, hence why I feel a master restore may be necessary. Any advice appreciated |
|
|
NickyJ
Starting Member
46 Posts |
Posted - 2007-05-15 : 09:15:33
|
I have re-populated the spt_values table by runbning the u_tables.sql script from SP4 and now my fixed server roles are visible again. My question is though is there a way I can re-populate the other system tables for example spt_provider_types and the others or is the only way via rebuilding the Master database then restoring a master backup from before my problems started so as logins are added. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-15 : 13:10:30
|
I'll go with restoring master db from good backup. |
|
|
|