permission problems running SQLExpress under terminal server

Hi all,
I've installed SQLExpress under terminal server, and everything worked
great.
Except, when the terminal server users don't have admin access, my program
crashes.
I noticed when I used Microsoft SQL Server Management Studio express on the
non admin accounts, I couldn't view the tables.
I went to the standard folder C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data
and windows denied me acess to that folder.
My question is, How can I let the users with non admin access run my program
without having them access to the data folder.
Also, is there a quick fix for my problem above to get it working? giving
admin access to everyone isn't an option.
p.s I've installed sqlexpress in Authentication Method = "Windows
Authentication"
p.s.s I've research on user instances, but felt that it didn't solve my
problem. Every user must access the same centralised data.
cheers
AstroHi
The programm connects to SQL Server via a login that you defined on the
server. So non admin users are running the programm in context of the login
the application connects to. You mentioned that program is crashed, what is
the error?
Okay, I see you set up SQL Server with Windows Authentication mode only.
Create a group on the machine where SQL Server run and add those users tio
the group. It is up to you wether or not not the users are member of syadmin
server role.
<astroboyfusion@.yahoo.com.au> wrote in message
news:46806b20$0$46400$c30e37c6@.pit-reader.telstra.net...
> Hi all,
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
> I noticed when I used Microsoft SQL Server Management Studio express on
> the
> non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program
> without having them access to the data folder.
> Also, is there a quick fix for my problem above to get it working? giving
> admin access to everyone isn't an option.
>
> p.s I've installed sqlexpress in Authentication Method = "Windows
> Authentication"
> p.s.s I've research on user instances, but felt that it didn't solve my
> problem. Every user must access the same centralised data.
> cheers
> Astro
>
>|||(astroboyfusion@.yahoo.com.au) writes:
> I've installed SQLExpress under terminal server, and everything worked
> great.
> Except, when the terminal server users don't have admin access, my program
> crashes.
And the error message is?

> I noticed when I used Microsoft SQL Server Management Studio express on
> the non admin accounts, I couldn't view the tables.
> I went to the standard folder C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data
> and windows denied me acess to that folder.
> My question is, How can I let the users with non admin access run my
> program without having them access to the data folder.
It appears that you implemented your program without thinking or knowing
about security. Well, here are the bad news: you need to learn that now.
Exactly what you need to do, depends on what your application is up to.
If all you do is to access tables, it's only a matter of granting
table permissions. Of course, had you used stored procedure, all you
would need would be to grant rights to run the procedure. But if your
application also bulk-load files etc, you may need heftier stuff.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thankyou Uri and Erland.
The way I solved the error 229 for now was changing the permission under
Security\Login\BuiltIn\Users
under "Server Roles" page and checked sysadmin.
-- Original Message --
From: "Erland Sommarskog" <esquel@.sommarskog.se>
Newsgroups: microsoft.public.sqlserver.security
Sent: Wednesday, June 27, 2007 8:09 AM
Subject: Re: permission problems running SQLExpress under terminal server

> (astroboyfusion@.yahoo.com.au) writes:
program[vbcol=seagreen]
> And the error message is?
I'm sorry Erland, I should have given the details of the error.
---
"failed to retrieve data for this request."
microsoft.sqlserver.express.smoenum
(Microsoft SQL Server, Error: 229)
---

> It appears that you implemented your program without thinking or knowing
> about security. Well, here are the bad news: you need to learn that now.
Your comment about security is spot on, I had to quickly migrate from Access
to Sqlserver. This is an incomplete project which I picked up to complete.

> Exactly what you need to do, depends on what your application is up to.
> If all you do is to access tables, it's only a matter of granting
> table permissions. Of course, had you used stored procedure, all you
> would need would be to grant rights to run the procedure. But if your
> application also bulk-load files etc, you may need heftier stuff.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx