MS SQL Server, Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp

Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0

Author	Message
Chris Crow #1 / 2	Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0 SQL Server 2000 SP2 or 3? Windows 2000 Server - SP1 Problem: Creating a new ROLE for any database on the server either through the GUI or by SQL Script will result in this error: Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0 Error converting data type varchar to int. Example: exec sp_addrole 'chris' Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0 Error converting data type varchar to int. As a result of this an attempt to apply Service Pack 3 for SQL Server 2000 failed during the running of 80SP1-Tools.SQL An error occurred during the execution of Script #1 of 7 which upgrades the internal tables of the SQL Server. Error message "Error during the installation of 80sp1-tools.sql" SQL Server Version prior to Upgrade Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1) SQL Server Version after failed Upgrade Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1) Contents of "80SP3-Tools.out" which is generated by the tools that perform the upgrade to the SQL Server. Updating database objects, executing 80SP3-TOOLS.SQL Started at Mar 9 2003 3:29PM DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Creating procedure sp_MSobjectprivs... Creating extended stored procedure xp_adsirequest... Creating procedure sp_ActiveDirectory_Obj... Creating extended stored procedure xp_GetAdminGroupName... Creating procedure sp_ActiveDirectory_SCP... Creating procedure sp_sqlagent_has_server_access... Creating procedure sp_verify_subsystem... Creating procedure sp_get_sqlagent_properties... Creating procedure sp_sqlagent_get_perf_counters... Creating procedure sp_post_msx_operation Creating procedure sp_delete_targetserver Creating procedure sp_msx_enlist... Creating procedure sp_enlist_tsx Creating stored procedure sp_enlist_tsx Creating procedure sp_add_jobstep_internal... Creating procedure sp_add_jobstep... Creating procedure sp_update_jobstep... Creating extended stored procedure xp_sqlagent_msx_account Creating extended stored procedure xp_SetSQLSecurity Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0 Error converting data type varchar to int. Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1 There is no such user or group 'RepositoryUser'. Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1 There is no such user or group 'RepositoryUser'. and lots more of those last lines.... Can anyone help with this weird problem? -- Chris Crowe
Thu, 25 Aug 2005 11:13:51 GMT

Chris Crow #2 / 2	Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0 I found it - the sp_addRole SP had been modified and had some other code in it. Replaced with a normal copy of the sp_addRole and all works well again. Chris -- Quote: > SQL Server 2000 SP2 or 3? > Windows 2000 Server - SP1 > Problem: > Creating a new ROLE for any database on the server either through the GUI or > by SQL Script will result in this error: > Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0 > Error converting data type varchar to int. > Example: > exec sp_addrole 'chris' > Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0 > Error converting data type varchar to int. > As a result of this an attempt to apply Service Pack 3 for SQL Server 2000 > failed during the running of 80SP1-Tools.SQL > An error occurred during the execution of Script #1 of 7 which upgrades the > internal tables of the SQL Server. > Error message "Error during the installation of 80sp1-tools.sql" > SQL Server Version prior to Upgrade > Microsoft SQL Server 2000 - 8.00.534 (Intel X86) > Nov 19 2001 13:23:50 > Copyright (c) 1988-2000 Microsoft Corporation > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1) > SQL Server Version after failed Upgrade > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) > Dec 17 2002 14:22:05 > Copyright (c) 1988-2003 Microsoft Corporation > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1) > Contents of "80SP3-Tools.out" which is generated by the tools that perform > the upgrade to the SQL Server. > Updating database objects, executing 80SP3-TOOLS.SQL > Started at Mar 9 2003 3:29PM > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > Configuration option 'allow updates' changed from 0 to 1. Run the > RECONFIGURE statement to install. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > Creating procedure sp_MSobjectprivs... > Creating extended stored procedure xp_adsirequest... > Creating procedure sp_ActiveDirectory_Obj... > Creating extended stored procedure xp_GetAdminGroupName... > Creating procedure sp_ActiveDirectory_SCP... > Creating procedure sp_sqlagent_has_server_access... > Creating procedure sp_verify_subsystem... > Creating procedure sp_get_sqlagent_properties... > Creating procedure sp_sqlagent_get_perf_counters... > Creating procedure sp_post_msx_operation > Creating procedure sp_delete_targetserver > Creating procedure sp_msx_enlist... > Creating procedure sp_enlist_tsx > Creating stored procedure sp_enlist_tsx > Creating procedure sp_add_jobstep_internal... > Creating procedure sp_add_jobstep... > Creating procedure sp_update_jobstep... > Creating extended stored procedure xp_sqlagent_msx_account > Creating extended stored procedure xp_SetSQLSecurity > Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0 > Error converting data type varchar to int. > Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1 > There is no such user or group 'RepositoryUser'. > Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1 > There is no such user or group 'RepositoryUser'. > and lots more of those last lines.... > Can anyone help with this weird problem? > -- > Chris Crowe
Thu, 25 Aug 2005 11:25:51 GMT

Page 1 of 1

[ 2 post ]