Trying to get the Windows username in SQL and as the application uses SQL Server Auth I cant use SUSER_NAME so I am trying to write an CLR function to achieve this.
my CLR function is as follows
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlString UserNameUDF()
{
// Put your code here
//string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
string userName = Environment.UserName.ToString();
return new SqlString (userName);
}
But after creating the assembly and function in SQL when I run it I get the following error
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "udfWindowsUserName":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Environment.get_UserName()
at UserDefinedFunctions.UserNameUDF()
Can anyone help?
Thanks,
Gavin,