November 24, 2005 at 7:08 am
I wonder if someone can assist with the following situation?
I have a development PC and a server PC. I wanted to transfer a database between development and server. I detached the database from the development PC, copied the files across to the server, then attached them.
Unfortunately I then had a problem with security in so far that the login/user for the database didn't work out. No problem thinks I, I will delete the user and login and recreate them.
Unfortunately in trying to delete the user from the database the drop fails with the following message:
"The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)"
Problem is that I can't seem to find out exactly what this user owns - it is probably hidden somewhere in the system tables.
Anyone have a clue how I can delete such users from the database?
A
November 25, 2005 at 3:00 am
Would it not be easier to just remove the login and join the user to a new login.
Otherwise you will have to delete or move all objects.
The following statement shows all objects, type and parent object if that is there.
select o.name, o.type, object_name(o.parent_obj) as parent
from sysobjects o join sysusers u on u.uid=o.uid
where u.name='username'
order by parent, o.name
Look in the BOL under sysobjects for the meaning of the different type codes.
Joachim.
November 28, 2005 at 3:56 am
Changing the schema owner is not a problem:
ALTER AUTHORIZATION ON SCHEMA::schemaname TO newusername
December 1, 2005 at 8:39 am
Try to execute sp_CHANGE_USERS_LOGIN 'AUTO_FIX', 'someusername' (look in BOL for more info). The problem is that the sid on the two servers are different which is a security violation and hence your inability to use the account.
Hope that helps,
Tim Januario
December 1, 2005 at 12:47 pm
Detach DB Fileà Copy File à Attach DB File
This option is always available, but this is not recommended/advisable way of
moving database, there is high risk associated with that and that is for any
reason if you cannot attach the DB properly you are loosing your Database
and that’s end of story.
Better option is take full backup of database and copy backup file to development
Server and Restore DB over there, in that case you are always keeping your original database intact.
From BOL you find
The following example modifies the schema HumanResources by transferring the table Address from schema Person into the schema.
USE AdventureWorks;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
December 8, 2005 at 2:42 am
In "Object Explorer" (MS SQL Management Studio) Expand the [databasename] / Security.
Click on Schemas.
In summary window, determine which Schema(s) are owned by the user and either change the owner or remove the Scheme(s).
The user can then be deleted.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply