Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. And Another SQL Server Question (sorry 'bout that, Chief...)

And Another SQL Server Question (sorry 'bout that, Chief...)

Scheduled Pinned Locked Moved Database
databasequestioncsharpsql-servervisual-studio
7 Posts 4 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R Offline
    R Offline
    Roger Wright
    wrote on last edited by
    #1

    While doing a little maintenance on a database that keeps track of reclosers (devices used in electrical substations), I tried adding a field to a table using the Management Studio on the server. No go - I don't have permission, and have no idea where to change that. So I switched to the client, opened Visual Studio, connected to the same database on the server, and proceeded to add new fields without a problem. In both instances I was logged in as the same domain user, with admin priveleges. It's not intuitively obvious to me why a remote user using Visual Studio has access priveleges greater than an administrator on the local server using the management tool designed for SQL Server. Can someone explain this odd behavior? I really think - especially once I complete an app to use this data - that this situation should be reversed, but I have no idea how to do so. Thanks! :-D

    "A Journey of a Thousand Rest Stops Begins with a Single Movement"

    V L 2 Replies Last reply
    0
    • R Roger Wright

      While doing a little maintenance on a database that keeps track of reclosers (devices used in electrical substations), I tried adding a field to a table using the Management Studio on the server. No go - I don't have permission, and have no idea where to change that. So I switched to the client, opened Visual Studio, connected to the same database on the server, and proceeded to add new fields without a problem. In both instances I was logged in as the same domain user, with admin priveleges. It's not intuitively obvious to me why a remote user using Visual Studio has access priveleges greater than an administrator on the local server using the management tool designed for SQL Server. Can someone explain this odd behavior? I really think - especially once I complete an app to use this data - that this situation should be reversed, but I have no idea how to do so. Thanks! :-D

      "A Journey of a Thousand Rest Stops Begins with a Single Movement"

      V Offline
      V Offline
      Vimalsoft Pty Ltd
      wrote on last edited by
      #2

      i understood the first part of your question but got lost in the last question. First Question when you logged into SQl management Studio did you use Windows Authentication to log in ? if yes, your user did not have permission. log in as a "sa" user or a user that has sysadmin permission in that sql instance and add the domain user and give him appropriate permissions and you will be able to add the field there. Your Resolution was to go the VS route which is not a good idea in most cases.the Reaons that you were able to add the field was that someone with permissions once created that and now it is always there it does not even ask you for username and passowrds, you just use it. i used to do that too. The last part of your question i did not understand

      It's not intuitively obvious to me why a remote user using Visual Studio has access priveleges greater than an administrator on the local server using the management tool designed for SQL Server. Can someone explain this odd behavior? I really think - especially once I complete an app to use this data - that this situation should be reversed, but I have no idea how to do so.

      Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

      R 1 Reply Last reply
      0
      • R Roger Wright

        While doing a little maintenance on a database that keeps track of reclosers (devices used in electrical substations), I tried adding a field to a table using the Management Studio on the server. No go - I don't have permission, and have no idea where to change that. So I switched to the client, opened Visual Studio, connected to the same database on the server, and proceeded to add new fields without a problem. In both instances I was logged in as the same domain user, with admin priveleges. It's not intuitively obvious to me why a remote user using Visual Studio has access priveleges greater than an administrator on the local server using the management tool designed for SQL Server. Can someone explain this odd behavior? I really think - especially once I complete an app to use this data - that this situation should be reversed, but I have no idea how to do so. Thanks! :-D

        "A Journey of a Thousand Rest Stops Begins with a Single Movement"

        L Offline
        L Offline
        Lost User
        wrote on last edited by
        #3

        If you are very sure that you used the same credentials in both instances, then it could be a setting in SSMS that prevents changes to tables that require the table to be recreated. Click Tools --> Options Under Designers --> Table and Database designers, there is an option "Prevent saving changes that require table re-creation". Ensure that this option is unchecked. It is checked by default.

        R C 2 Replies Last reply
        0
        • L Lost User

          If you are very sure that you used the same credentials in both instances, then it could be a setting in SSMS that prevents changes to tables that require the table to be recreated. Click Tools --> Options Under Designers --> Table and Database designers, there is an option "Prevent saving changes that require table re-creation". Ensure that this option is unchecked. It is checked by default.

          R Offline
          R Offline
          Roger Wright
          wrote on last edited by
          #4

          Thanks! :-D I generally don't mess with default settings until I'm familiar with the product, and that's one I haven't changed.

          "A Journey of a Thousand Rest Stops Begins with a Single Movement"

          1 Reply Last reply
          0
          • V Vimalsoft Pty Ltd

            i understood the first part of your question but got lost in the last question. First Question when you logged into SQl management Studio did you use Windows Authentication to log in ? if yes, your user did not have permission. log in as a "sa" user or a user that has sysadmin permission in that sql instance and add the domain user and give him appropriate permissions and you will be able to add the field there. Your Resolution was to go the VS route which is not a good idea in most cases.the Reaons that you were able to add the field was that someone with permissions once created that and now it is always there it does not even ask you for username and passowrds, you just use it. i used to do that too. The last part of your question i did not understand

            It's not intuitively obvious to me why a remote user using Visual Studio has access priveleges greater than an administrator on the local server using the management tool designed for SQL Server. Can someone explain this odd behavior? I really think - especially once I complete an app to use this data - that this situation should be reversed, but I have no idea how to do so.

            Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

            R Offline
            R Offline
            Roger Wright
            wrote on last edited by
            #5

            Thanks, but I think Shameel has hit the nail on the head. :)

            "A Journey of a Thousand Rest Stops Begins with a Single Movement"

            V 1 Reply Last reply
            0
            • R Roger Wright

              Thanks, but I think Shameel has hit the nail on the head. :)

              "A Journey of a Thousand Rest Stops Begins with a Single Movement"

              V Offline
              V Offline
              Vimalsoft Pty Ltd
              wrote on last edited by
              #6

              Cool :)

              Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

              1 Reply Last reply
              0
              • L Lost User

                If you are very sure that you used the same credentials in both instances, then it could be a setting in SSMS that prevents changes to tables that require the table to be recreated. Click Tools --> Options Under Designers --> Table and Database designers, there is an option "Prevent saving changes that require table re-creation". Ensure that this option is unchecked. It is checked by default.

                C Offline
                C Offline
                Corporal Agarn
                wrote on last edited by
                #7

                Had the same problem today. I am getting into 2008 from 2000 and did not know where to find that setting. Thanks Shameel by the way I just noticed the Get Smart quote :laugh: djj

                1 Reply Last reply
                0
                Reply
                • Reply as topic
                Log in to reply
                • Oldest to Newest
                • Newest to Oldest
                • Most Votes

                • Login
                • Don't have an account? Register
                • Login or register to search.
                • First post
                  Last post
                0
                • Categories
                • Recent
                • Tags
                • Popular
                • World
                • Users
                • Groups