Vista and SQL Express issues

  • Thread starter Thread starter mmcd79
  • Start date Start date
M

mmcd79

Can someone point me in the right direction here?

I have Vista with SQL Express installed. I can run the SQL Express
Management console (without running as administrator), and because of
my rights in SQL I can access the system tables fine.

I created a new database and I put the DB/log files in a different
directory (i.e. D:\MSSQL). I can NOT access this database unless I
start the SQL Express Management console with the "run as
administrator" privilege.

Furthermore, I can't even edit the files in D:\MSSQL with any program
unless I "run as administrator". How do I grant myself access to a
newly created folder so I can access the files there without "run as
administrator" right?

I understand why this option is here (for my protection), but I don't
understand why Vista thinks DB files located in C:\Program Files
\Microsoft SQL Server\MSSQL\Data is "SAFE" and allows me access
without "run as administrator" right, but a newly created directory is
"NOT SAFE" and requires me to have this right.

I can't see a feasible way to make this work UAC enabled.
 
Correction.. I misidentified some information in my original post.

I can edit data fine under d:\mssql\data, i.e. text documents. I
remember what I meant to say. When I created the DB's and told the
SQL management console to change their location during creation to
this directory, I could not do it unless I ran SQL management console
as administrator.

To further clarify... I'm looking in my D:\mssql\data directory right
now. If I create a text file in there and look at the properties of
this file.. I can click the Security tab and it lists the permissions
fine. But if I check the properties of the MDF (database) file
located in this same directory, and click the security tab.. I get the
message: "you must be an administrative user with permission to view
this object's security properties" with a button to continue, in which
case I get prompted by UAC to run as administrator.

I proceeded by running as administrator, and set my permissions to
inherit from parent folder, and now I can see the permissions on the
MDF file like I could with the text file. But my problems still exist
with SQL Management Console.

Unless I run the SQL management console as administrator, I cannot
expect my DB, however I can expand the system databases with no
problem. WTF is going on?

The reason I ask, is because I just made a .NET application (on vista)
that reads data from this database. It cannot query the DB unless I
run the app as administrator. I shouldn't have to do this when the
account I'm using IS AN ADMINISTRATOR, especially on the Database.
I'm just missing something stupid here. Please help!
 
Correction.. I misidentified some information in my original post.

I can edit data fine under d:\mssql\data, i.e. text documents. I
remember what I meant to say. When I created the DB's and told the
SQL management console to change their location during creation to
this directory, I could not do it unless I ran SQL management console
as administrator.

To further clarify... I'm looking in my D:\mssql\data directory right
now. If I create a text file in there and look at the properties of
this file.. I can click the Security tab and it lists the permissions
fine. But if I check the properties of the MDF (database) file
located in this same directory, and click the security tab.. I get the
message: "you must be an administrative user with permission to view
this object's security properties" with a button to continue, in which
case I get prompted by UAC to run as administrator.

I proceeded by running as administrator, and set my permissions to
inherit from parent folder, and now I can see the permissions on the
MDF file like I could with the text file. But my problems still exist
with SQL Management Console.

Unless I run the SQL management console as administrator, I cannot
expect my DB, however I can expand the system databases with no
problem. WTF is going on?
Click to expand...

I suggest that you go look at the accounts on Master.MDF and LDF files and
match the accounts on the MDF and LDF files in question. You need NETWORK
SERVICE, SQLServer2005MSSQLUser blah blah whatever and
machine-name/Administrator accounts with full access for the accounts.
NETWORK SERVICES replaces the SYSTEM account.
The reason I ask, is because I just made a .NET application (on vista)
that reads data from this database. It cannot query the DB unless I
run the app as administrator. I shouldn't have to do this when the
account I'm using IS AN ADMINISTRATOR, especially on the Database.
I'm just missing something stupid here. Please help!
Click to expand...

http://blogs.msdn.com/nikhiln/archi...-to-make-an-application-elevate-in-vista.aspx

This NG is not a MS SQL Server or a .NET NG. I suggest that if you need
further assistance that you post to MS.Public NG(s) concerning the problems
so you can get help.
 
Thanks, I'll give your suggestions a try. I did happen to see a SQL
based user (i.e. SQLServer2005MSSQLUser$)that I've never seen before.

As far as the NG, I originally thought this was a problem was directly
related Vista's new security model, and still indeed think that to be
the case, so this NG should be perfectly fine for my request.
Granted, it may be possible to get better help from a SQL specific NG,
but a generic public NG? I doubt that. Thanks for the suggestion
however.
 
Thanks, I'll give your suggestions a try. I did happen to see a SQL
based user (i.e. SQLServer2005MSSQLUser$)that I've never seen before.

As far as the NG, I originally thought this was a problem was directly
related Vista's new security model, and still indeed think that to be
the case, so this NG should be perfectly fine for my request.
Granted, it may be possible to get better help from a SQL specific NG,
but a generic public NG? I doubt that. Thanks for the suggestion
however.
Click to expand...

When I say generic public NG I mean, like MS.Public.dotnet (csharp, vb or
general), with people that use SQL Server as programmer's.

I can tell you that I have MS SQL Server 2005 Express and VS 2005 Express on
this Vista machine. I don't have any of the problems you're having.

I did download the Pubs and Author MDF and LDF files I needed for MCTS
70-528 training I am doing. I was getting access denied when I was trying to
attach the files to SQL server. I found out the accounts on the files needed
to do the attach. I put them there, the ones I talked about, and I went on
about my business.

I think you have an accounts permission issue not only with SQL server but
also with a .NET application trying to access the database, which are the
MDF and LDF files.

You have account permission issue, not really related Vista per say.
 
I hope I didn't come off too strong, my apologies if it came off that
way. I really appreciate you even responding.

This is definitely permissions related, I just don't know for sure.
You mention you are not having problems, but I'm wondering if you
actually created your databases yourself (or via installer for the
downloaded dbs). I happened to "restore from backup", and I think my
databases still have the permissions from the original database, and
that may be one area for my problems. I'm by no means a SQL DBA, so
I'm probably just overlooking something simple.

I'll post to a SQL group to see if I can get further assistance.
Thanks!
 
Back
Top