I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
