none
MS SQL Server Managment studio

    Question

  • Hi,

    I have a new APS.net 3.5 / SQL2008 account at an internet sevice provider

    I have downloaded SQL 2008 Expres with sql 2008 managment studio.
    I did prefer to develope the database on the server of my provider with SQL managment studio, like I was used to in SQL 2005.
    How ever I get a lot of errors working with managment studio.
    for instance, if I try to open the contectmenu on a node of my database in the left pane I get the message :

    "The execute permision was denied on the object 'xp_instance_regread', database mssqlsystemresource', schema 'sys' The EXECUTE permissinon was denied on the object'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server , error: 229)

    The contect menu will then appear but I can for instance not delete a table from the list. instead I get the same error message.

    But the most annoying is the fact that I can not make database diagrams! if I try to add a new database diagram I first get the message that " This database does not have one or more of the support objects to use database diagramming. Do you wish to create them?

    If I click yes I get a long list with errors, Also telling me that I do not heve EXECUTE permissin for all kinds of objects and that he can not find the 'dbo.sp_upgradediagrams stored procedure.

    Also If I try to use the ASPNET_regSQL tool to add  membership tables to the database, I get the error that I do not have Execute permission on the object 'sp_executesql' 'mssqlsystemresource', schema 'sys'

    I get the same errors if I try to do these things directly in VWD2008.

    I have consulted the help desk of the provider but they tell me that this is normal and that I am attemting to do things on the server witch I do not have permision for, becouse MS tightend up security with SQL Server 2008!

    This is very hard to believe, and I think that the provider did not configure there server as they should have.
    They tell me that I should develope my database local and then use the backup to transfer it to the server.

    Since I am only an enthusiastic amateur I would like the opinion of a professional about this.

    regards

    Rob
    Thursday, April 02, 2009 5:29 PM

Answers

  • Everybody many thanks for your answers.

    The problem is already solved for some time. It was indeed a configuration problem on the server. My hosting provider has corrected the issue. and everything works as expected now.

    Rob

    • Marked as answer by rob warning Tuesday, May 04, 2010 7:08 PM
    Tuesday, May 04, 2010 7:08 PM

All replies

  • Hi Rob,

    It does sound like a permissions issue.  Do you have system admin / dbo permissions on the entire database?  I know many web hosting companies will lock down the permissions of their users.  This is because they often share the same server with multiple accounts, and one malicious user with too much permission could wreak havoc for all users.

    There's a script here that can help you find the permissions on a server.  If you can't execute the script, it's a good indication that you don't have sufficient priviledges.  ;)
    http://blogs.conchango.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx
    Michelle Ufford | SQLFool.com | Please mark solved if I've answered your question :)
    Friday, April 03, 2009 2:49 AM
  • Hello Michelle,

    thanks for your answer.

    I have tryed the script, it runs, and for my name I get the following results:

    principal_name

    principal_type_desc

    class_desc

    object_name

    permission_name

    permission_state_desc

    role_name


    Warning

    SQL_USER

    NULL

    NULL

    NULL

    NULL

    db_owner

    Warning

    SQL_USER

    DATABASE

    NULL

    CONNECT

    GRANT

    NULL


    so I think I am db_owner. if that implicates that I am also Administrator for my own database I don't know.
    But how can you make relations between tables without using database diagrams. And is it normal for a hoisting provider to put this kind of restrictions on the database for there customers?
    Is it common practice to develope the database localy and the use backup to put it on the server?
    Is it not so that when using a backup tool, the tables,relations etc are also created with a script just like the Aspnet_reg tool ? In that case I presume I will get the same error messages.

    regards

    Rob


    Friday, April 03, 2009 8:29 AM
  • I think this error happens when your database is not restored 100%.

    Try to restore the database 100% and all the login username ids will be lined up and your database should work fine.

    Regards,
    Pinal Dave
    http://blog.sqlauthority.com
    Founder - http://blog.sqlauthority.com
    Friday, April 03, 2009 4:29 PM
  • I think I can shed some light on that particular extended procedure.  I've had issues with this procedure recently with my server hardening efforts and maybe my discovery can be of use to you.

    The sys.xp_instance_regread procedure is used by SQL Server to access the host system's registry in order to determine the network/host password policy settings.  Note:  even if you're using Windows Authentication, under the hood the mechanism is still a username and password.

    In order for you to view object properties (as well as other actions that require password policy checking), the public role must have execute permission on that procedure.

    The role can be found under the system extended stored procedures in the master database.

     

    Hope that helps!

    Tuesday, May 04, 2010 6:23 PM
  • Everybody many thanks for your answers.

    The problem is already solved for some time. It was indeed a configuration problem on the server. My hosting provider has corrected the issue. and everything works as expected now.

    Rob

    • Marked as answer by rob warning Tuesday, May 04, 2010 7:08 PM
    Tuesday, May 04, 2010 7:08 PM