File Ownership failing for Domain Accounts

I am having a consistent problem on several instances of SQL 2005 setting
the ownership of database files to a windows user. This is not a login
problem but a file ownership problem. Here are the steps:
1. Right-click on any user database, e.g. AdventureWorks and select
"Properties
2. Select the "Files" page from the right-hand list
3. Click the elipsis "..." button to the far right of the "Owner:" label
4. Click the "Browse..." button in the pop-up "Select Database Owner" window
5. Select any login that maps to a windows or domain account except for
members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
6. Click "OK" on the Browse pop-up and then on the Select Database Owner
pop-up
7. Click "OK" on the "Database Properties" dialog to return to the main SQL
Server Management Studio application
8. Repeat steps 1 & 2
9. Notice that the "Owner:" field is now blank
This problem is occuring on multiple instances of SQL Server 2005 at both
SP1 and SP2 patch levels. All of the instances are set to mixed security
mode. All of the windows accounts I have tried are in the local server's
Adminstrators group. Setting ownership to a pure SQL account or a member of
the local [NT AUTHORITY] group works just fine. I have even attempted to
set
the permissions manually using ALTER AUTHORIZATION and the sp_changedbowner
proc. Nothing seems to work and I never get an error message or log.The procedure you describe changes the database owner and is unrelated to
file ownership. IMHO, the functionality is misplaced in the SSMS GUI; it
should be under the 'general' instead of 'files'.
I can't seem to reproduce the problem under SP2. Have you tried closing and
re-opening the object explorer? Is the correct owner reported using the
methods below?
EXEC sp_helpdb
SELECT
name AS database_name,
SUSER_SNAME(owner_sid) AS database_owner
FROM sys.databases
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Kevin
I'm also could not reproduce the problerm under SP'a'.
Have you tried Dan's suggestions?
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Thanks for the helpful suggestion. The SQL you provided does return the
values I expect after changing ownership through the "Files" page in the
database properties dialog. How strange it is then that the Owner field
remains empty even after closing and reopening SQL Management Studio. Also,
I am unable to install diagram support. Any attempt to create a diagram
yields the all too common and annoying "...this database does not have a
valid owner..." error message.
Any idea why one method of discovering ownership returns the correct value
but another method does not? This is very bizarre.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:9074E028-7EAE-4012-B0B5-F24DC8F67206@.microsoft.com...
> The procedure you describe changes the database owner and is unrelated to
> file ownership. IMHO, the functionality is misplaced in the SSMS GUI; it
> should be under the 'general' instead of 'files'.
> I can't seem to reproduce the problem under SP2. Have you tried closing
> and re-opening the object explorer? Is the correct owner reported using
> the methods below?
> EXEC sp_helpdb
> SELECT
> name AS database_name,
> SUSER_SNAME(owner_sid) AS database_owner
> FROM sys.databases
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
> news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||I think I found the root cause of the problem. It looks like the admin has
setup just about every instance of SQL Server I can access to start under a
local machine account. This is preventing lookups against Active Directory.
"Kevin D. White" <kevinDwhite@.newsgroup.nospam> wrote in message
news:ekLZFoAaHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I am having a consistent problem on several instances of SQL 2005 setting
>the ownership of database files to a windows user. This is not a login
>problem but a file ownership problem. Here are the steps:
> 1. Right-click on any user database, e.g. AdventureWorks and select
> "Properties
> 2. Select the "Files" page from the right-hand list
> 3. Click the elipsis "..." button to the far right of the "Owner:" label
> 4. Click the "Browse..." button in the pop-up "Select Database Owner"
> window
> 5. Select any login that maps to a windows or domain account except for
> members of the [NT AUTHORITY] group, e.g. [COMPANYDOMAIN\MyName]
> 6. Click "OK" on the Browse pop-up and then on the Select Database Owner
> pop-up
> 7. Click "OK" on the "Database Properties" dialog to return to the main
> SQL Server Management Studio application
> 8. Repeat steps 1 & 2
> 9. Notice that the "Owner:" field is now blank
> This problem is occuring on multiple instances of SQL Server 2005 at both
> SP1 and SP2 patch levels. All of the instances are set to mixed security
> mode. All of the windows accounts I have tried are in the local server's
> Adminstrators group. Setting ownership to a pure SQL account or a member
> of the local [NT AUTHORITY] group works just fine. I have even attempt
ed
> to set the permissions manually using ALTER AUTHORIZATION and the
> sp_changedbowner proc. Nothing seems to work and I never get an error
> message or log.
>|||Hello Kevin,
Thank you for sharing your experience on resolving the issue. It shall
benefit the community.
Also, I have forwarded this issue to the product team and they may want to
check if this behavior is normal or there is any further improment in UI so
that users may get more hint on this kind of problems.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Good to hear. The thing that *really* tripped me up is the fact the applying
ownership to a domain account through the UI never returned an error or
warning. Combine the lack of an error with the frankly misleading error
message when I attempt to create a diagram and the whole thing becomes this
vicious circle. The diagram support error tells me to set a valid owner, I
go and set what appears to be a valid owner, diagram support returns the
same message..etc.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:45EqxuraHHA.4088@.TK2MSFTNGHUB02.phx.gbl...
> Hello Kevin,
> Thank you for sharing your experience on resolving the issue. It shall
> benefit the community.
> Also, I have forwarded this issue to the product team and they may want to
> check if this behavior is normal or there is any further improment in UI
> so
> that users may get more hint on this kind of problems.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Kevin,
Thank you for your additional feedback and please rest this has also been
forwarded to the right channel. Your feedback is always valuable for our
product improvement!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.