Msg 15138, Level 16, State 1, Line 4 The database principal owns a schema in the database, and cannot be dropped.

Question

Hi, I am new at SQL server security and migration from SQL2000 to SQL2008 R2. We have migrated our databases (6) from SQL2000 SP3 to SQL2008 R2 on a new server. I do have a problem with the user logins to create it to the new server. I have done the  user logins as per kb246133, but can still not login to the databases. I am alos getting an error when I try to create the user, using the output from KB243133

(Msg 15433, Level 16, State 1, Line 139 Supplied parameter sid is in use.

Msg 15010, Level 16, State 1, Line 155 The database 'dclubar_mac_live' does not exist. Supply a valid database name. To see available databases, use sys.databases.)

Each one of the 6 databases carries its own login which is created when access to the different databases is selected in the master security.  When I am trying to select the databases for the user in the master security, I also get an error -  

Msg 15023, Level 16, State 1, Line 1 User, group, or role 'mgr' already exists in the current database.

I cannot drop the user as this is linked to to operation of the database and programs.  I would therefore be grateful if someone can give me some advise on how to link the users on the new server and databases.

Thanks, Elize

Answer 1

Your accounts by the sounds of things are SQL Server accounts and not Windows Accounts. So with the migration of the databases if you performed a backup and restore to do the migration as well as other steps the accounts will exist in the databases but not at the server level. You have then gone and created the accounts at the server level but they will have different sids.

Have a look at this article on finding and resolving orphaned users. - http://msdn.microsoft.com/en-us/library/ms175475.aspx

I hope this helps.

---

Warwick Rudd
MCT MCITP SQL Server 2008 Admin
My Blog
-------------------------------------------------------
Please mark as Answered if I have answered your question
Please vote if this was useful
-------------------------------------------------------

Answer 2

Hi, I am new at SQL server security and migration from SQL2000 to SQL2008 R2. We have migrated our databases (6) from SQL2000 SP3 to SQL2008 R2 on a new server. I do have a problem with the user logins to create it to the new server. I have done the  user logins as per kb246133, but can still not login to the databases. I am alos getting an error when I try to create the user, using the output from KB243133

(Msg 15433, Level 16, State 1, Line 139 Supplied parameter sid is in use.

Msg 15010, Level 16, State 1, Line 155 The database 'dclubar_mac_live' does not exist. Supply a valid database name. To see available databases, use sys.databases.)

Each one of the 6 databases carries its own login which is created when access to the different databases is selected in the master security.  When I am trying to select the databases for the user in the master security, I also get an error -  

Msg 15023, Level 16, State 1, Line 1 User, group, or role 'mgr' already exists in the current database.

I cannot drop the user as this is linked to to operation of the database and programs.  I would therefore be grateful if someone can give me some advise on how to link the users on the new server and databases.

Thanks, Elize

Answer 3

This is a duplicate of - http://social.technet.microsoft.com/Forums/en-US/sqltools/thread/1f0ac6dc-f41e-4e08-9bfb-7241c1fd3a7b

 

---

Warwick Rudd
MCT MCITP SQL Server 2008 Admin
My Blog
-------------------------------------------------------
Please mark as Answered if I have answered your question
Please vote if this was useful
-------------------------------------------------------

Answer 4

Good Morning Warwick, Thanks for the help so far. I did the run the scripts for the orphaned login (mgr). it has up date the access to the different databases as well as the roles to the database. However, the SID id are still different for the 2 servers and the Status is still 0 and the Roles NULL. The GID is also different, e.g.

new server dclub (SQL2008 R2)
name uid uid status name sid roles createdate updatedate altuid password gid environ hasdbaccess islogin isntname isntgroup isntuser issqluser isaliased issqlrole isapprole
mgr 5 5 0 mgr 0xD05A47AAA423DA4BB5566A94B3C4AA38 NULL 2001-03-13 22:17:23.810 2009-05-05 09:11:46.833 NULL NULL 0 NULL 1 1 0 0 0 1 0 0 0
Old server live dclub (SQL2000 SP3)
mgr 5 5 2 mgr 0xD3869901F24C9F4EB631DC3C121C7202 0x010001 2001-03-13 22:17:23.810 2009-05-05 09:11:46.833 0 NULL 16400 NULL 1 1 0 0 0 1 0 0 0
As a result, I can still not loginto the database - error on login " Connect Failed"

Thanks, Elize

Answer 5

Elize if you still have access to your old environment you could look at this link to look at extracting your logins - http://www.sqlservercentral.com/scripts/Transfer+Logins+Administration/69245/

I am a little surprised that when you used - http://support.microsoft.com/kb/246133 it is giving you issues with the matching of these accunts back up.

The other option that you could also look at is in your 2008 R2 instance is Create an SSIS package and point back at your 2000 instance , selecting the transfer logins task.

What type of account are you trying to connect with ? a Windows Account or a SQL Server account. If it is a windows account has it changed domains ?

I am assuming that your account has appropriate access and can connect to all of the 6 databases. When you ran the check for orphan user scripts what results did you get ? how many orphaned users were determined ? Is the login you are particularly interested in in the list ? what results did you get when you performed the resolve orphaned logins ?

 

I hope this helps

---

Warwick Rudd
MCT MCITP SQL Server 2008 Admin
My Blog
-------------------------------------------------------
Please mark as Answered if I have answered your question
Please vote if this was useful
-------------------------------------------------------

Answer 6

Thank you Warwick, this has helped me and I was able to migrate the user logins.

Regards,

Elize