Access to MSSQLServer 2008 R2 but not rights

[pschulz]

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

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;
            }

[Arjay]

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.

[VictorN]

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

[pschulz]

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.

[Arjay]

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.

[Arjay]

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.

[pschulz]

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

[Arjay]

"...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?

[pschulz]

@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

[Arjay]

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.

[Arjay]

In answer to the redundant code question, if you use using block your above code reduces to:

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.

[pschulz]

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.

[Arjay]

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.

[pschulz]

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.

[pschulz]

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.