June 2, 2008 at 8:18 am
Is it possible to find out the System name of/for a login? Eg: If I want to look for the system names who have the 'sa' role or 'xyz' role.Is there any system table that shows such details? Thanks!
June 2, 2008 at 9:49 am
Take a look at the view dbo.syslogins in the master database. This should give you what you need (at least for server roles - for database roles, look at dbo.sysusers table in whatever database)
"Got no time for the jibba jabba!"
-B.A. Baracus
June 2, 2008 at 10:07 am
syslogins is a quick check for the sysadmin column or some of the other roles.
If you want a procedure, try sp_helpsrvrolemember (http://msdn.microsoft.com/en-us/library/aa933412(SQL.80).aspx)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 2, 2008 at 10:33 am
I agree that syslogin helps in providing the key answers but not the system/host name that has a specific role attached to it.
June 2, 2008 at 12:32 pm
Steve's advice tells you how to determine who is a member of a particular role. If you have servers connecting in directly, you'll see their logins in Domain\ComputerName$. If you list the role members, it should be easy to determine which ones are then computer accounts.
K. Brian Kelley
@kbriankelley
June 2, 2008 at 12:57 pm
How about these commands? May them help.
sp_helpuser
sp_helprotect NULL, 'userName' --List permissions for specific user
sp_helprotect @name = 'objName' --List user names for a specific object
sp_helprotect NULL, NULL, NULL, OS -- list permissions for statements (S) and objects (O).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply