Finding roles granted to users and group

Question

Post reply

Finding roles granted to users and group

Sylvain Coutu

Ten Centuries

Points: 1077
More actions
May 11, 2005 at 12:59 pm

#86140

Hi All
                           Type           Role Connect    Role DoImportantThing
myDomain\VIP        Win Group    Granted           Granted
myDomain\NotVIP   Win Group    Granted            Not Granted
myDomain\A
May 16, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

Is this what you are looking for ?

select suser_sname(a.sid), 'Type '= case when a.isntgroup = 1 THEN 'Win Group'

else 'User'

END

, c.name from sysusers a join sysmembers b on a.uid = b.memberuid join sysusers c on b.groupuid = c.uid and suser_sname(a.sid) is not null

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 2

See Finding roles granted to users and group(2) thread for full text ( I had some trouble posting the thread ).

SheilaC SSC-Addicted Points: 429 More actions · Answer 3

I am also having this problem I need to know all users for server logins as well as database users and roles. I did see the article on 7 scripts and I did recieve some information -- however I know that some of my users are NOT sysadmin's but I need to know how to utilize the select statement to help me with this. I have tried:

select suser_sname(sid) from sysxlogins

select name from sysusers

The one from the article

SELECT 'ServerRole' = A.name, 'MemberName' =

B.name FROM master.dbo.spt_values A, master.dbo.sysxlogins B WHERE A.low = 0 AND A.type = 'SRV' AND B.srvid IS NULL

AND A.number & B.xstatus = A.number

This is very helpful but I need to know the user, login, and the role for each database I have just have not reached that point yet with the sys tables

Any help please....

Sheila

Sheila Conlon (getting back)

SheilaC SSC-Addicted Points: 429 More actions · Answer 4

okay this is what I have found!! use

exec sp_helplogins

this will give you server logins, database users, and permissions!

Sheila Conlon (getting back)