SQL Server 2000 Logins issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server 2000 Logins issue

Author

Topic

jsimmons
Starting Member

14 Posts

Posted - 2005-11-01 : 10:52:38

To All,

I have an issue in which the logins for the 'BUILTIN\Administrators' and 'sa' account seem to be "locked out". Some of the functionality is gone (i.e. via EM, do not have the ability to create maintenance plans; at the logins area under 'Security' in EM, do not have the ability to change the passwords for either account.) Have no problems accessing the server, therefore it seems to be SQL-related.

Can anyone help with a specific fix/resolution?

Thanks!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-01 : 11:09:51

Are you the DBA?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsimmons
Starting Member

14 Posts

Posted - 2005-11-01 : 11:11:46

Yes...my bad. Should have indicated that....

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-11-01 : 13:40:51

People use SQL EM?

My thought is that your EM registration is not using the username you think it is. If it is setup for NT Auth then perhaps someone has removed the builtin admins group and your NT login had always been setup on the SQL Server without admin rights. This way if you are a domain admin you had admin rights in SQL Server via the builtin group. Then it was removed and you still can login but have no rights. I'm sure there are other scenerios in which this could have happend. One other example that comes to mind is if you were a local admin and someone removed you from that group. The domain users may be setup on the SQL Server but surely they would not have admin rights. So the same thing would happen here. You were a server admin previously and now you are not.

Please post the SQL QA results of this:


SELECT SYSTEM_USER, USER

select name,  
	case when sysadmin = 1 then 'YES' else 'No' end SysAdmin,
	case when SecurityAdmin = 1 then 'YES' else 'No' end SecurityAdmin,
	case when ServerAdmin = 1 then 'YES' else 'No' end ServerAdmin
from master..syslogins
Where Name = system_user

Here is something additional to look at, but I wouldn't post this data:


select distinct R.Name, USER_NAME(U.UID) 
from sysmembers M
	inner join (select * from sysusers where issqlrole = 1) R on (M.GroupUID = R.UID)
	inner join (select * from sysusers where issqlrole = 0) U on (M.MemberUID = U.UID)
order by 2

SELECT PRIVILEGE_TYPE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, Grantee
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
Order by 2

SELECT o.name, USER_NAME(grantee) 
FROM syspermissions p 
INNER JOIN sysobjects o ON p.id = o.id
INNER JOIN sysusers u ON p.grantee = u.uid
WHERE o.type = 'P'
Order by 2

SELECT o.name, USER_NAME(grantee) 
FROM syspermissions p 
INNER JOIN sysobjects o ON p.id = o.id
INNER JOIN sysusers u ON p.grantee = u.uid
WHERE o.type = 'FN'
Order by 2

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources