Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

Retrieve the version of SQL Server from within a user defined function

EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.

I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?

have you tried

select serverproperty('ProductVersion') as character_value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can use @.@.Version within a user defined function. I tested the following code in both SQL 2000 and SQL 2005. Hope this helps.

Alter Function dbo.VersionNumber()

Returns int

As

Begin

Declare @.Temp VarChar(1000)

Declare @.Output Int

Select @.Temp = @.@.Version

Set @.Temp = Replace(Left(@.Temp, CharIndex('-', @.Temp)-1), 'Microsoft SQL Server', '')

If IsNumeric(@.Temp) =1

Set @.Output = Convert(int, @.Temp)

Else

Set @.Output = 0

Return @.Output

End

go

Select dbo.VersionNumber()

|||

serverproperty works very nicely, thanks.

I had tried @.@.VERSION, which worked, but not pleasant. Now changed to serverproperty