Re: drop a database user. assign objects to dbo.
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 20 Sep 2007 22:18:15 +0000 (UTC)
light_wt (lightwt@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
we are on sql2005.
your explanation is great, but, a bit over my head. can you point me to
some detail step-by-step doing it using SSMS?
Should all objects in the database be owned by dbo?
I would expect that they really are, because you would not be able to
drop his user, if he owned objects.
You can find objects that are not owned by dbo with this select:
SELECT s.name + '.' + o.name, owner = dp.name
FROM sys.schemas s
JOIN sys.objects o ON s.schema_id = o.schema_id
JOIN sys.database_principals dp ON o.principal_id = dp.principal_id
WHERE o.principal_id <> 1
You can use this command
ALTER AUTHORIZATION ON object TO dbo
If there are loads of objects, you could use the SELECT above to generate
the commands.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- References:
- Re: drop a database user. assign objects to dbo.
- From: Kalen Delaney
- Re: drop a database user. assign objects to dbo.
- Prev by Date: Re: SQL Logins vs Windows Authentication
- Next by Date: Backup permission
- Previous by thread: Re: drop a database user. assign objects to dbo.
- Next by thread: Backup permission
- Index(es):
Relevant Pages
|
