upgraded from MSSQL 2000 to MSSQL 2005 now database's users have
in [Server Configuration]
|
Prev: KB955706 Error Code 0x2B2F
Next: SQL 2008 Installation Error - There was an error generating the XML document. Error code 0x84B10001.
From: Chuck on 14 Nov 2009 22:16 Hello. I updated the MSSQL 2000 server to MSSQL 2005 server now the database's users have no login names. I found a way to list orphaned users sp_change_users_login 'Report' but it only listed dbo and there are actually five listed with MSSQL Server Management Studio Express. I tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS' is absent or invalid. Any ideas?
From: Sylvain Lafontaine on 15 Nov 2009 00:05 Try with Auto_Fix instead; see the example at the end of the following article: http://msdn.microsoft.com/en-us/library/ms174378.aspx -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please) Independent consultant and remote programming for Access and SQL-Server (French) "Chuck" <no-spam-for-me(a)cfcug-dotcom> wrote in message news:uiqSMIaZKHA.5544(a)TK2MSFTNGP02.phx.gbl... > Hello. > > I updated the MSSQL 2000 server to MSSQL 2005 server now the database's > users have no > login names. I found a way to list orphaned users sp_change_users_login > 'Report' but it only listed dbo and there are actually five listed with > MSSQL Server Management Studio Express. I tried this sp_change_users_login > 'update_one', 'RPS', 'RPS' but I get the following error the user name > 'RPS' > is absent or invalid. > > Any ideas? > >
From: Erland Sommarskog on 15 Nov 2009 05:50 Chuck (no-spam-for-me(a)cfcug-dotcom) writes: > I updated the MSSQL 2000 server to MSSQL 2005 server now the database's > users have no login names. I found a way to list orphaned users > sp_change_users_login 'Report' but it only listed dbo and there are > actually five listed with MSSQL Server Management Studio Express. I > tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get > the following error the user name 'RPS' is absent or invalid. ALTER USER RPS WITH LOGIN = RPS The again, judging from the error message, there is no user RPS in the database. You can view all users in the database with SELECT * FROM sys.database_principals -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Uri Dimant on 15 Nov 2009 11:10 Erland I think that great feature works since SP2, am I right? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CC4786BE14B2Yazorman(a)127.0.0.1... > Chuck (no-spam-for-me(a)cfcug-dotcom) writes: >> I updated the MSSQL 2000 server to MSSQL 2005 server now the database's >> users have no login names. I found a way to list orphaned users >> sp_change_users_login 'Report' but it only listed dbo and there are >> actually five listed with MSSQL Server Management Studio Express. I >> tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get >> the following error the user name 'RPS' is absent or invalid. > > ALTER USER RPS WITH LOGIN = RPS > > The again, judging from the error message, there is no user RPS in the > database. > > You can view all users in the database with > > SELECT * FROM sys.database_principals > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Chuck on 15 Nov 2009 10:15
Erland, The user RPS was listed when I ran the below listed query you suggested. SELECT * FROM sys.database_principals It has a sid and owning_principal_id of NULL. Its has a type_desc of SQL_User. I am currently using the Windows Authentication method of logging into the local server. Is there a way to autogenerate the missing sid for the user? I tried adding the login name but it will not allow it. TIA "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CC4786BE14B2Yazorman(a)127.0.0.1... > Chuck (no-spam-for-me(a)cfcug-dotcom) writes: >> I updated the MSSQL 2000 server to MSSQL 2005 server now the database's >> users have no login names. I found a way to list orphaned users >> sp_change_users_login 'Report' but it only listed dbo and there are >> actually five listed with MSSQL Server Management Studio Express. I >> tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get >> the following error the user name 'RPS' is absent or invalid. > > ALTER USER RPS WITH LOGIN = RPS > > The again, judging from the error message, there is no user RPS in the > database. > > You can view all users in the database with > > SELECT * FROM sys.database_principals > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > |