CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Access to MSSQLServer 2008 R2 but not rights

  1. #1
    Join Date
    Aug 2015
    Posts
    59

    Question Access to MSSQLServer 2008 R2 but not rights

    I have access to my MsSQL-Server 2008 if this varaible is 'strServerName=MyServerName\SQLEXPRESS'
    but I dont have rights to create a database. "no rights within the master database".
    But have rights to create if strServerName = MyServerName
    Code:
    public static bool IsServerOnline(string strServerName)
            {
                string strCnn = ("data source='" + strServerName + "';integrated security=SSPI");
                SqlConnection server = null;
    
                try
                {
                    server = new SqlConnection(strCnn);
                    server.Open();
                    return true;
                }
    
                catch (SqlException ex)
                {
                    //ClsMsg.message(ex.Message);
                    return false;
                }
    
                catch (Exception ex)
                {
                    //throw new Exception(ex.Message);
                    return false;
                }
    
                finally
                {
                    server.Close();
                    if (server is IDisposable)
                    {
                        (server as IDisposable).Dispose();
                    }
    
                    if ((server != null))
                        server = null;
                }

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    So give yourself sysadmin login rights to the database [instance].

    Also, check out using a using block (that leverages IDisposable) - most of the code you have written above is unnecessary.

  3. #3
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,758

    Re: Access to MSSQLServer 2008 R2 but not rights

    Quote Originally Posted by pschulz View Post
    I have access to my MsSQL-Server 2008 if this varaible is 'strServerName=MyServerName\SQLEXPRESS'
    but I dont have rights to create a database. "no rights within the master database".
    But have rights to create if strServerName = MyServerName
    It seems to me that you are trying to access two different SQL Server instances: the default instance of SQL EXPRESS (the name: 'MyServerName\SQLEXPRESS') nad the default instance of some SQL Server non-Express edition (the name: 'MyServerName')
    So you have no rights within the master database of SQL Express instance but do have the same rights in non-Express instance
    Last edited by VictorN; November 8th, 2016 at 05:40 PM. Reason: typo
    Victor Nijegorodov

  4. #4
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    well, thats possible, but how?
    I installed the Mssql2008 R2 and then VS2012 and let it install the sqlexpress(not hardly sure, if it comes from there), but the msql-server-managment-studio, should show me the two ones, or not?
    And finally if the other one is just the sqlexpress, why i dont have rights, cant remember using the sqlexpress in earlyer times that I ever set rights.

  5. #5
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    Quote Originally Posted by pschulz View Post
    well, thats possible, but how?
    Does it matter? Check the logins under security in sql management studio for the server and instance you are interested in. Double-click your login (domain login or sa), click on Server Roles and make sure public and sysadmin are checked. If you have user mappings selected for the login, be sure that the selected databases have the sysadmin role.

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    Quote Originally Posted by pschulz View Post
    but the msql-server-managment-studio, should show me the two ones, or not?
    It shows the instance you are connected to. It won't show other instances (or servers) if you aren't connected to them.

  7. #7
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    "...if you aren't connected to them. "
    sounds like i can deside anywhere, to which one I'm connected?

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    Quote Originally Posted by pschulz View Post
    "...if you aren't connected to them. "
    sounds like i can deside anywhere, to which one I'm connected?
    I'm confused. Have you actually used SQL Server Management Studio?

  9. #9
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    @Arjay
    your first answer(number 2 this thread) I havnt seen that day. Which parts of that code is unessasary?

    I use a code, let the app see whats available. On those system I get two options, this:
    'strServerName=MyServerName\SQLEXPRESS'
    and this:
    strServerName = 'MyServerName'

    but managment studio, shows me just one option, its the: 'MyServerName'.
    Makes me wonder.

    And I repeat, if I used the SQLExpress version, anywhere in the past, I never had to set any rights, so long I used 'integrated security'

    But, I no longer want to suck up the forum for this trouble, at least its enough to know, there is the second one, the SQLExpress Version, coming from VS2012

  10. #10
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    SQL Server Management Studio is a separate app from Visual Studio. When you open it, you are prompted for the sql server (and instance). Once opened, you can also connect to additional databases using 'connect' inside the sql manager view.

    It will only show databases that you are connected to.

  11. #11
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    In answer to the redundant code question, if you use using block your above code reduces to:
    Code:
    public static IsServerOnline (string serverName)
    {
      var strCnn = String.Format ("'data source={0}';integrated security=SSPI", serverName);
    
      try
      {
        using (var cn = new SqlConnection(strCnn))
        {
          cn.Open();
          return true;
        }
      }
      catch (Exception ex)
      {
          // report exception
      }
      return false;
    }
    The using block automatically calls IDisposable of the object declared within the using block when the code leaves the scope of the using block.

    In the case of a SqlConnection object, IDisposable appropriately calls the Close method.

  12. #12
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    Thanks for answering, but as I wrote, managment studio shows me only the server, not the sqlexpress.
    I do not really know why you mention "you can also connect to additional databases using".

    The way of 'using', I know it, but cant take it allways.

  13. #13
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,316

    Re: Access to MSSQLServer 2008 R2 but not rights

    Quote Originally Posted by pschulz View Post
    Thanks for answering, but as I wrote, managment studio shows me only the server, not the sqlexpress.
    I do not really know why you mention "you can also connect to additional databases using".

    The way of 'using', I know it, but cant take it allways.
    Sql Management Studio will show you the databases you connect to - even sqlexpress, but in order to see it, you have to enter the sql server name and the instance. I mentioned the "you can also connect to additional databases using..." because you can connect to additional databases from inside SQL Management studio. I incorrectly said "SQL Manager View". It's actually the "Object Explorer" window. There is a "connect" drop down list that displays the same dialog that allows you to select a database when you open the management studio. Then multiple databases can be displayed. Maybe you know all this, but it didn't seem so based on what you said in post #4. At any rate, just trying to help.

  14. #14
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    I havnt criticize you, I really havnt seen why you wrote it, coudnt see it. Always thinking reading my phrases to see whats left. Maybe its this.
    There are three dropdown lists, one is ‘Servername’ (start management studio, and then there is this dialog). Open this list there is nothing else. Click on search gives me ‘Lokal Server, ‘NetworkServer’, but finally nothing else.
    (The connect button, under Object-Explorer opens this dialog with this three dropdownlists)
    Thats why I was wondering, many times in the past, having two different names from the code, but just one from the management studio.

  15. #15
    Join Date
    Aug 2015
    Posts
    59

    Re: Access to MSSQLServer 2008 R2 but not rights

    some to add,
    one day to another, I did not have access anymore(standardkonto or admin, both ways are blocked(might be norton)).
    I had to deinstall the mssql-server, all whats left within the control panel, but did not touch the server 2012(which has come with the vs2012 installation ). Now after reinstall the server 2008 r2(same thing as before), there is just one instanz, the other on has gone.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)