lørdag den 22. oktober 2011

Find owner of Fulltext catalog

Once in a while I would get this error when dropping SQL logins

The database principal owns a fulltext catalog in the database, and cannot be dropped.

Changing the owner is easy enough, but I’m doing this by scripts and needed a way to find the owner of the fulltext catalog. Took a while but finally managed to get the result with

select cat.fulltext_catalog_id, cat.name 
from sys.fulltext_catalogs as cat
inner join sys.database_principals as dp2
on cat.principal_id = dp2.principal_id


Perfect, so lets script that, and we get something like


$SQLLogin = 'soadmin_SOTEST2'
$sqlserver = 'sql01.int.wingu.dk'
$database = 'SO7_SO7SOTEST2'

$sql = "select cat.fulltext_catalog_id, cat.name from sys.fulltext_catalogs as cat "
$sql = ($sql + " inner join sys.database_principals as dp2 on cat.principal_id = dp2.principal_id ")
$sql = ($sql + "where dp2.name = '$SQLLogin'")

$rs = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $database -Query $sql
foreach($row in $rs){
$sql = ("ALTER AUTHORIZATION ON Fulltext Catalog::[" + $row.Name + "] TO dbo")
Invoke
-Sqlcmd -ServerInstance $sqlserver -Database $database -Query $sql
}

Ingen kommentarer:

Send en kommentar