 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / May 2006SQL 2005 Express-Database does not have a valid owner?
I am having some difficulty creating database diagrams in SQL Server Express 2005. My design machine is running Windows XP Pro. This machine is a member of a Windows 2000 domain that I will call MYDOMAIN. I am logged on the design machine as MYDOMAIN\MyUserAccount. This account has local administrator rights on the design machine. Having said all that, here's a description of my problem: I have created a database (MyDatabase) and I would like to create a database diagram. I have checked to make sure that MYDOMAIN\MyUserAccount is the database owner. However, when I click on MyDatabase/Database Diagrams in SQL Server Management Studio Express, I receive the following error message (following a fairly lengthy wait): --------------------------------- Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first user the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the diagram support object. --------------------------------- As I said, I have ensured that my user account is the database owner. I have not run the ALTER AUTHORIZATION statement (because I don't yet know what, if anything, this will do considering the fact that I am already designated as database owner). Going further through the database properties (under Permissions/View Server Permissions), I receive the following error message: --------------------------------- Cannot show requested dialog. Additional Information: Could not load file or assembly 'file:///C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SQLManagerUi.dll' or one of its dependencies. The system cannot find the file specified. (mscorlib) --------------------------------- I don't know whether this error is related to the first, however it seemed relevant to me in that it pertains to permissions. Hopefully this is enough information to work with. I'd appreciate any help you can offer to resolve this problem. Thanks! What does it return? select owner_sid,SYSTEM_USER from sys.databases where owner_sid=SUSER_SID () >I am having some difficulty creating database diagrams in SQL Server >Express 2005. [quoted text clipped - 43 lines] > > Thanks! owner_sid=0x010500000000000515000000FD77B15607E9942DF89FB47458040000 [No Column Name]=MYDOMAIN\MyUserName > What does it return? > select owner_sid,SYSTEM_USER from sys.databases [quoted text clipped - 48 lines] >> >> Thanks! Are youe in as MYDOMAIN\MyUserName? That means that the database's owner is MYDOMAIN\MyUserName > owner_sid=0x010500000000000515000000FD77B15607E9942DF89FB47458040000 > [No Column Name]=MYDOMAIN\MyUserName [quoted text clipped - 51 lines] >>> >>> Thanks! Yes, I understand. This is what I wrote in my original post. Of course I've replaced my actual domain name and user name for these posts but it is coming up in all cases with my correct domain name and user name as the database owner. If you'll endulge a rant for a moment, I'm really disappointed in SQL Server 2005 Express so far. This is supposed to be a tool for novices to get their feet wet with SQL Server and it is so bloody complex and counterintuitive (at least when it comes to security) that I can't imagine anyone wanting to use it after a few days of this. I've been working with Access since 1992 and I've never had as many problems as I've encountered with SS2K5E. From this security issue to the complexity of moving design databases to a hosted environment, this product has cost me more time than any I've used in my whole career. Having said that, it does have it's good qualities. It's just hard to appreciate them when faced with problems like this every day that don't seem to have a sensible solution. > Are youe in as MYDOMAIN\MyUserName? > [quoted text clipped - 56 lines] >>>> >>>> Thanks! I digressed into a rant in my last reply to this post...so I thought I'd try again - and this time try to stay on topic. As I mentioned in my first post, I am the database owner and this most recent test confirmed that. So, if I am the database owner, why can't I create database diagrams? When I try, I get the error message I mentioned in my first post. Has anyone else encountered this problem and solved it. I have to admit I'm at a loss and unable to continue until I figure this out. Thanks! > Are youe in as MYDOMAIN\MyUserName? > [quoted text clipped - 56 lines] >>>> >>>> Thanks! You have to change the database owner to a valid login account, e.g. SA: ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA]; Also sp_changedbowner will do the trick, see http://msdn2.microsoft.com/en-us/library/ms178630.aspx  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx >I am having some difficulty creating database diagrams in SQL Server >Express 2005. [quoted text clipped - 43 lines] > > Thanks! Can you help me understand the reasoning a bit better? My database server is set up to use Windows Authentication. I am already logged on as 'MYDOMAIN\MyUserAccount' and this account is (according to SQL Server) already the owner of the database. Am I missing something? I don't want to use SQL Server Authentication (which I would have to use if I create the 'SA' account as you described). I appreciate the help. Thanks! > You have to change the database owner to a valid login account, e.g. SA: > [quoted text clipped - 50 lines] >> >> Thanks! You Windows account is probably sysadmin on the instance. As such, it is mapped to the 'dbo' user in any database. But 'dbo' is mapped to the original account that created the database. By changing the 'authorization' on the database, you restore the 'dbo' to be mapped to a valid account. By doing this you are not forced to use SQL authentication, this procedure does no affect your login in any way. You already have a number of databases owned by 'sa' in your instance anyway: master, model, msdb, tempb. You could just as well use ALTER AUTHORIZATION ON DATABASE::<dbname> TO [MYDOMAIN\MyUserAccount] if you feel like. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx > Can you help me understand the reasoning a bit better? My database server > is set up to use Windows Authentication. I am already logged on as [quoted text clipped - 60 lines] >>> >>> Thanks! If this database was imported from somewhere else, the MYDOMAIN\MyUserAccount may own the database, but may not be a valid logon to the SQL Server. Check the Security - Logins to see whats in there. My guess is that you'll find something is strange in that configuration. Thanks for the reply. I didn't import the database. In fact I created it on the same computer I'm using right now. However, when I went to Security - Logins, I found that there was no entry for MYDOMAIN\MyUserAccount, and when I tried to add it, there was no option but to add local users. I double checked just now and found that MYDOMAIN\MyUserAccount is an administrator on this machine. Since I created this database on this machine and SQL Server recognized me long enough to assign ownership of the database to me, I can't understand why it isn't recognizing me now. Just so I can keep working, I've changed ownership to 'SA' as someone recommended earlier, but I really want to be able to use Windows Authentication in this database and I still don't think I'm any closer to being able to do that. I've read everything I can get my hands on about SQL Server security and I can't find where (or if) I'm going wrong. I'm still in the market for a solution to this problem if anyone has any other ideas. Thanks! > If this database was imported from somewhere else, the > MYDOMAIN\MyUserAccount may own the database, but may not be a valid > logon to the SQL Server. Check the Security - Logins to see whats in > there. My guess is that you'll find something is strange in that > configuration. What account is running your SQL Server service? Logged in as 'MYDOMAIN\MyUserAccount', can you run this test script and tell me if it works or if you get an error: CREATE DATABASE [testdb]; GO USE [testdb]; GO EXECUTE AS USER = 'dbo'; GO REVERT; GO SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx > Thanks for the reply. I didn't import the database. In fact I created it > on the same computer I'm using right now. [quoted text clipped - 22 lines] >> there. My guess is that you'll find something is strange in that >> configuration. The account running my SQL Server Service is 'NETWORK SERVICE' Here is the error message I received (note that I have changed the name of the domain and the user account): Msg 15404, Level 16, State 19, Line 2 Could not obtain information about Windows NT group/user 'MYDOMAIN\MyUserAccount', error code 0x54b. I'm curious, was this an error in the way I installed SQL Server? Thanks again for your help? > What account is running your SQL Server service? > [quoted text clipped - 39 lines] >>> there. My guess is that you'll find something is strange in that >>> configuration. Error 0x54b is: (Win32) 0x54b (1355) - The specified domain either does not exist or could not be contacted. This means that the account running your SQL service cannot contact the domain controller for the domain 'MYDOMAIN' to get the account information for 'MYDOMAIN\MyUserAccount'. Usually, NETWORK SERVICE should be able to do this, so it should not be an error in the instalation. An often case when this error happens is when working on a machine that is joined to the domain but not currently connected to the domain (like when I take my work laptop and work on it from home). In this case you can log into the SQL Server using your cached Windows credentials, but SQL Server cannot use this cached credentials to verify the account info when you try things like CREATE LOGIN ... FROM WINDOWS or EXECUTE AS. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. HTH, ~ Remus Rusanu SQL Service Broker http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx > The account running my SQL Server Service is 'NETWORK SERVICE' > [quoted text clipped - 51 lines] >>>> there. My guess is that you'll find something is strange in that >>>> configuration. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.