Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Roles 2
- Thread starter reena72
- Start date
-
1
- #2
execute this sp and see if it would help you...
sp_helpsrvrolemember
Andel
andelbarroga@hotmail.com
sp_helpsrvrolemember
Andel
andelbarroga@hotmail.com
Hi, will this help you?
SELECT b.name as UserName, a.name as Role
FROM sysusers b, sysusers a, sysmembers m
WHERE a.uid = m.groupuid AND b.uid = m.memberuid
Andel
andelbarroga@hotmail.com
SELECT b.name as UserName, a.name as Role
FROM sysusers b, sysusers a, sysmembers m
WHERE a.uid = m.groupuid AND b.uid = m.memberuid
Andel
andelbarroga@hotmail.com
-
1
- #4
tlbroadbent
MIS
I beleive you'll want to run this sp_helprolemember in each database to get the roles in the database.
sp_helpsrvrolemember will show the members of server roles.
Terry
sp_helpsrvrolemember will show the members of server roles.
Terry
tlbroadbent
MIS
This is similar to the query proposed by Andel but allows you to search by login rather than user. That would help if the login name differs from the user name in the database.
declare @lname varchar(40) /* the login name */
set @lname='loginname'
select
DbRole = cast(g.name As varchar(30)),
MemberName = cast(u.name As varchar(30))
from master..syslogins l
join sysusers u on l.suid=u.suid
join sysmembers m on u.uid=m.memberuid
join sysusers g on m.groupuid=g.uid
where l.name=@lname
and g.issqlrole=1
Terry
declare @lname varchar(40) /* the login name */
set @lname='loginname'
select
DbRole = cast(g.name As varchar(30)),
MemberName = cast(u.name As varchar(30))
from master..syslogins l
join sysusers u on l.suid=u.suid
join sysmembers m on u.uid=m.memberuid
join sysusers g on m.groupuid=g.uid
where l.name=@lname
and g.issqlrole=1
Terry
- Thread starter
- #6
tlbroadbent
MIS
Did you change 'loginname' to a valid login on your SQL Server? Did you run it in one of the databases where that login name exists as a user? Terry
- Thread starter
- #8
tlbroadbent
MIS
My query was not finding logins that were users in a database but had not been assigned a role other than 'Public.' 'Public' is the default role.
This new query will find users in any assigned roles as well as the 'Public' role. It will also indicate if the login is not a user in the database or if the login doesn't exist on the server.
Hope you get better results. Terry
This new query will find users in any assigned roles as well as the 'Public' role. It will also indicate if the login is not a user in the database or if the login doesn't exist on the server.
Hope you get better results. Terry
- Status
Similar threads
- Locked
- Question