March 22, 2011 at 5:47 pm
Is there any quick way to find logins/users which have 'sa' level access? I need this for SQL 2000, 2005, 2008.
March 22, 2011 at 11:49 pm
For 2005, 2008 query sys.server_principals, sys.server_role_members and sys.server_permissions, you're looking for anyone a member of the sysadmin role or with control server permissions.
Cannot recall for SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 1:03 am
Hi
You can view the information of all the users in SYSXLOGINS table in Master database
Thnks
March 23, 2011 at 2:04 am
$QLdb@ (3/23/2011)
You can view the information of all the users in SYSXLOGINS table in Master database
sysxlogins contains the information on logins, not users.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 5:13 am
Can you believe I still have my SQL 2000 System Tables Map? @=)
You can get user / login information from the following tables:
sysremotelogins (to verify linked servers, etc.)
syslogins (gets login information and server roles)
sysusers (gets user information and maps to below)
sysmembers
I think you want sysusers mapped to sysmembers and then back to sysusers, but I'm not sure. It's been too long and I no longer have my 2000 install to verify.
Let me know if that doesn't help. I'll check my map again to see what else I can find.
March 23, 2011 at 8:22 am
works in all versions sql2000 - 2008R2
select loginname
from syslogins where sysadmin = 1
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply