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: Erland Sommarskog on 15 Nov 2009 12:33 Uri Dimant (urid(a)iscar.co.il) writes: > Erland > I think that great feature works since SP2, am I right? Correct. I assume that Chuck is smart enough to have installed the most recent service pack as part of his installation procedure. -- 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: Erland Sommarskog on 15 Nov 2009 12:35 Chuck (no-spam-for-me(a)cfcug-dotcom) writes: > 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. You said that you upgraded the server from SQL 2000 to SQL 2005. In that case, logins should have been brought over. But if you installed a new instance of SQL Server with SQL 2005 on it, and just moved over the database, there may not be a login RPS on the new server instance. In such case, you can create a new login with CREATE LOGIN RPS WITH PASSWORD = 'yourchoicehere' -- 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 20:27 The SQL Server is version 9.0.4053. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CC4BCB989C1CYazorman(a)127.0.0.1... > Uri Dimant (urid(a)iscar.co.il) writes: >> Erland >> I think that great feature works since SP2, am I right? > > > Correct. I assume that Chuck is smart enough to have installed the > most recent service pack as part of his installation procedure. > > > -- > 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 20:45 Erland, I got the following error when I executed the create statement against the database. CREATE LOGIN RPS WITH PASSWORD = 'yourchoicehere' Msg 15025, Level 16, State 1, Line 1 The server principal 'RPS' already exists. ----------------------------------------------------------------------------------- If I try to update it I get this error... USE RPS GO EXEC sp_change_users_login 'update_one', 'RPS', 'RPS', 'password' GO Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108 Terminating this procedure. The User name 'RPS' is absent or invalid. If I run this EXEC sp_change_users_login 'Report' I get no records returned. If I run this SELECT * FROM sys.database_principals I get back 15 rows with RPS being one of them. Any ideas? "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CC4BD1DA5282Yazorman(a)127.0.0.1... > Chuck (no-spam-for-me(a)cfcug-dotcom) writes: >> 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. > > You said that you upgraded the server from SQL 2000 to SQL 2005. In that > case, logins should have been brought over. > > But if you installed a new instance of SQL Server with SQL 2005 on it, > and just moved over the database, there may not be a login RPS on the > new server instance. In such case, you can create a new login with > > CREATE LOGIN RPS WITH PASSWORD = 'yourchoicehere' > > -- > 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: Erland Sommarskog on 16 Nov 2009 04:24
Chuck (no-spam-for-me(a)cfcug-dotcom) writes: > I got the following error when I executed the create statement against the > database. > CREATE LOGIN RPS WITH PASSWORD = 'yourchoicehere' > Msg 15025, Level 16, State 1, Line 1 > > The server principal 'RPS' already exists. Well, I'm sitting on a distance and have to guess what is goin on. > If I try to update it I get this error... > > USE RPS > GO > > EXEC sp_change_users_login 'update_one', 'RPS', 'RPS', 'password' > GO > > Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108 > Terminating this procedure. The User name 'RPS' is absent or invalid. So what if you use ALTER USER as I suggested in an earlier post? > If I run this EXEC sp_change_users_login 'Report' > I get no records returned. > If I run this SELECT * FROM sys.database_principals > I get back 15 rows with RPS being one of them. Can you post the full output from SELECT suser_sname(sid), * FROM yourdb.sys.database_principals -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |