Post reply

Error when creating a database diagram

  • April 22, 2019 at 3:55 am

    #3508614

    When I create a new database diagram, I get this error:

     

     

    What does this mean and how can I resolve this?

  • April 22, 2019 at 6:52 am

    #3511711

    Run this and try it again:

    use master;

    go

    ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2016 TO [ComputerName\InstanceName];

  • April 23, 2019 at 2:55 am

    #3520767

    Here is what I tried:

    use master;

    go

    ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2016CTP3 TO [DESKTOP-8J18OSS\SQLEXPRESS2017];

    In the Object Explorer at the very top level it says DESKTOP-8J18OSS\SQLEXPRESS2017 which is where I got that part from.

    Here is the error message:

    Msg 15151, Level 16, State 1, Line 5
    Cannot find the principal 'DESKTOP-8J18OSS\SQLEXPRESS2017', because it does not exist or you do not have permission.

    Any idea what I'm doing wrong?  I am very much at the beginner's level.

     

    By the way, how did you know I was using the AdventureWorks database or were you using that as an example?

     

     

  • April 23, 2019 at 12:46 pm

    #3524159

    Please note that database diagrams are deprecated, and are not accessible in SSMS 18.

    https://docs.microsoft.com/en-gb/sql/ssms/release-notes-ssms?view=sql-server-2017#deprecated-features-1

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • May 22, 2019 at 4:25 am

    #3645969

    I tried again and now I get the message "Incorrect syntax near '\'

    Any suggestions?

  • May 22, 2019 at 4:27 am

    #3645970

    If the database diagrams are deprecated how else would it be possible to see the connections/joins between various tables?  Surely there is a way to see how all tables are related to each other.  How could you do that?

  • May 23, 2019 at 10:18 am

    #3646450

    What is the owner of the database. There if often problem with owner and the scripts for creating the structure for diagrams. Diagrams works fine, and even if the are listed as deprecated, they will live a long time.

    Also take a look at:

  • May 25, 2019 at 5:11 am

    #3647207

    Thank you for the links you sent me.  I looked at those which led to one link after another.  Eventually I was reading about system views, system tables, etc. which led me down a long path with many questions constantly popping up.  But it was a good exercise.

    About the database owner, recently I changed my computer name so that might have something to do with the error I am getting.

    How can I determine the name of the owner of the database?

  • May 27, 2019 at 9:48 am

    #3647483

    How can I determine the name of the owner of the database?

    select [Database] = [name], [Owner] = suser_sname(owner_sid)
    from sys.databases;

    Should list the databases and owners.

  • May 27, 2019 at 6:21 pm

    #3647617

    Ok, I ran a query against "sys.databases" using suser_sname ( ) and got the name of the db owner.  Recently I changed the name of my computer to a name that is easier to read, but here is what I discovered.

    Apparently SQL uses your computer name as the db owner.   The name of the db owner that was returned was my previous computer name.

    I went into the database properties.  The owner field was blank.  So after changing my computer name, apparently SQL doesn't automatically update the new computer name in the db owner field.

    So I typed in the new computer name (db owner) into that field and the object was not recognized.  I clicked the ellipsis next to the "owner" field then I clicked the 'Browse' button.  Under the list of 'Matching objects' I didn't see my current computer name listed (but I still saw the previous computer name listed).

    At this point, I need to know how can I get SQL to recognize my current computer name (current db owner)?

  • May 28, 2019 at 7:22 am

    #3647728

    At this point, I need to know how can I get SQL to recognize my current computer name (current db owner)?

    The current user is:

    select CurrentUser = suser_sname();

    It should work to activate the diagrams if the database owner and current user is the same.

    Note, in an production environment the database owner should be set to sa. To set the database owner with code:

    alter authorization on database::ReplaceThisWithYourDatabaseName to ReplaceThisWithValidUser;

    The database can't have active and running query's.

  • May 29, 2019 at 10:24 pm

    #3648438

    If you renamed your PC, you really want to clean that up in SQL Server or you will hit odd issues.

    You can see the name is sys.servers - server id 0 is your computer. There are a couple of builtin procedures you can use for the rename. So you would just use something like:

    sp_dropserver 'DESKTOP-8J18OSS\SQLEXPRESS2017'
    GO
    sp_addserver 'YourNewComputerName\SQLEXPRESS2017', local
    GO

    Make sure to specify local on the add so that it updates the right one, with server id 0. You need to restart the services after doing the rename.

    Sue

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply