List of Roles for current user

[Coppermill]

Is there anyway of finding a list of roles that the current logged in users is a member of?

 

[vongrunt]

Yup - see sp_helpuser in Books Offline.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Coppermill]

Yes this returns all the Usernames and Groups, but I need to know which one the current logged in user is, not all of them.

 

[vongrunt]

To get current user: SELECT USER

To get roles for specified user: exec sp_helpuser 'blah'

To combine both... I'm not sure does sp_helpuser accept variables. If true, fine. Otherwise use dynamic SQL or:

create table #t (UserName nvarchar(128), GroupName nvarchar(128), LoginName nvarchar(128), DefDBName nvarchar(128), UserID smallint, SID smallint)
insert into #t exec sp_helpuser
select * from #t where UserName = user
drop table #t

There are probably simpler ways to do it... but I'm still on my first morning coffee

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[JamesLean]

For some reason it won't let you pass USER straight in to the SP but you can do this:

DECLARE @user sysname
SET @user = USER

EXEC sp_helpuser @user

--James

 

[Coppermill]

All works okay, but I've not added users to the SQL Server database, I've added the Users Groups and then the Groups to the SQL Server Roles. Therefore the Username is no good.

I've created this routine, it stores the results in a table tblDomainGroups.

--drop table rolemember
create table #roles_users (UserName nvarchar(50), GroupName nvarchar(50), LoginName nvarchar(50), DefDBName nvarchar(50), UserID Int, SID varbinary(50))
insert into #roles_users
  EXEC sp_helpuser


DECLARE @username nvarchar(50) 
DECLARE @GroupName nvarchar(50)
DECLARE @MyCursor CURSOR   

SET @MyCursor = CURSOR FAST_FORWARD 
FOR 
Select LoginName, UserName
  From #roles_users 
   
OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @username, @GroupName

   WHILE @@FETCH_STATUS = 0 
   BEGIN 
--      PRINT @RoleName
	IF IS_MEMBER(@username) = 1

	BEGIN

		insert into  tblDomainGroups
			SELECT sUser_sName(), @username, @GroupName, 'None'

	END
      FETCH NEXT FROM @MyCursor 
      INTO @username, @GroupName
   END 

CLOSE @MyCursor 

DEALLOCATE @MyCursor

 

[Coppermill]

The only problem I am having is that when you make changes to the ActiveDirectory it takes quite sometime for it to be enabled at the SQL Server end, sometime 30 minutes.

 

[Catadmin]

Ah, the wonders of replication! (hack/cough).

So, if I understand you correctly, you want it to give you the current AD username/group instead of what SQL Server thinks is the current username/group?

If so, you're SOL. You'll have to wait until the changes get replicated to SQL Server. You could check with your network/Windows admin about lowering the replication time to 15 minutes, but you'd still have to wait.

The only place that maps the User role info is in the SQL Server tables. I don't believe it copies up to AD in any way shape or form. MS was either indulging in laziness or security when they designed this. Take your pick. @=)



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Coppermill]

Many thanks for all your help, as I now have a working module