Besides the extra storage space. Why would I want to use
the Personal Edition(not free) instead of MSDE (free). I
have read the "Choosing an Edition" related information
but besides space issues, is there any reason not to use
MSDE in favor of the Personal Edition?
Thanks,
MikeHi MT,
With MSDE you don't get "tools". We supply some (details at our site).
Google for others.
HTH,
--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"MT" <miketownsend33@.yahoo.com> wrote in message
news:07e001c34b6b$1c866720$a401280a@.phx.gbl...
> Besides the extra storage space. Why would I want to use
> the Personal Edition(not free) instead of MSDE (free). I
> have read the "Choosing an Edition" related information
> but besides space issues, is there any reason not to use
> MSDE in favor of the Personal Edition?
> Thanks,
> Mike|||Which "Tools"
Mike
>--Original Message--
>Hi MT,
>With MSDE you don't get "tools". We supply some (details
at our site).
>Google for others.
>HTH,
>--
>Greg Low (MVP)
>MSDE Manager SQL Tools
>www.whitebearconsulting.com
>"MT" <miketownsend33@.yahoo.com> wrote in message
>news:07e001c34b6b$1c866720$a401280a@.phx.gbl...
>> Besides the extra storage space. Why would I want to
use
>> the Personal Edition(not free) instead of MSDE (free).
I
>> have read the "Choosing an Edition" related information
>> but besides space issues, is there any reason not to
use
>> MSDE in favor of the Personal Edition?
>> Thanks,
>> Mike
>
>.
>|||hi MT,
"MT" <MikeTownsend33@.yahoo.com> ha scritto nel messaggio
news:0a4901c34b8d$230f8f00$a301280a@.phx.gbl...
> Which "Tools"
all the client graphical tools to manage SQL Server like Enterpise Manager,
Query Analyzer, Profiler...
MSDE only provides oSql.exe, a command line utility by which you can almost
do the same things but the hard way... via T-SQL statements... =;-) rather
then via a nifty user interface...
for your convenience you can have a look at a prj of mine, at the link
following my sign., that provides a fee user interface similar to Enterprise
Manager..
hth
Andrea Montanari
montanari_andrea@.virgilio.it
http://www.asql.biz/DbaMgr.shtm
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||MSDE is just the relational data engine from within the entire SQL Server
product. That's why its name is Microsoft SQL Server 2000 Desktop ENGINE.
Personal Edition is a full EDITION of SQL Server including:
- Tools: Query Analyzer, Enterprise Manager, Profiler, Index Tuning Wizard,
etc.
- Analysis Services: OLAP, Data Mining
- Various bits and pieces of development libraries, legacy utilities, etc.
In general the scenarios for MSDE vs Personal Edition are very different.
MSDE is intended for use embedded within an application, not as a
multi-application database server. Personal Edition is intended primarily
as a client-side store for an application that runs both on the desktop and
on a server. Since the clients already require a CAL (or the SQL Server
server has Processor Licenses) there is no additional cost to use Personal
Edition in this scenario.
Personal Edition has a secondary use, which is as a way to install a full
edition of SQL Server 2000 on a Windows Desktop OS. So if you purchase
Standard Edition but want to install on Windows XP, you have to install
Personal Edition. You are paying a lot for a server product and then
crippling it (both in the OS selection and the database edition you need to
install), but some people are willing to do that. They save some $ on the
OS, and they get the full SQL Server product other than the governor. If it
meets their needs, great.
MSDE technically isn't free. The MSDE model is that you buy a development
tool and you receive the right to redistributed MSDE at no charge with (and
as part of) applications that you develop USING THAT TOOL. Microsoft has one
no cost development tool, ASP.NET Web Matrix, that includes MSDE as well.
But the no cost redistribution of MSDE in that case is limited to
applications developed with Web Matrix. There is another scenario where you
purchase a SQL Server 2000 edition and get MSDE redistribution rights. In
this case the SQL Server 2000 edition takes on the role of the development
tool.
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"MT" <miketownsend33@.yahoo.com> wrote in message
news:07e001c34b6b$1c866720$a401280a@.phx.gbl...
> Besides the extra storage space. Why would I want to use
> the Personal Edition(not free) instead of MSDE (free). I
> have read the "Choosing an Edition" related information
> but besides space issues, is there any reason not to use
> MSDE in favor of the Personal Edition?
> Thanks,
> Mike|||Thanks!! Much clearer now.
As we have purchased several processor licenses for the
Standard Edition of SQL Server and in some cases we have
installed MSDE on a few laptops so that users could use
the same applications that the Standard Edition is using
to test/develop or in one case subscribe to.
We now have one user who wishes to have a copy of the
Personal Edition installed for the same purpose, and
we're reluctant to do so.. is there a reason that you
could think of why they would need to use the Personal
Edition as opposed to MSDE?
MT
>--Original Message--
>MSDE is just the relational data engine from within the
entire SQL Server
>product. That's why its name is Microsoft SQL Server
2000 Desktop ENGINE.
>Personal Edition is a full EDITION of SQL Server
including:
>- Tools: Query Analyzer, Enterprise Manager, Profiler,
Index Tuning Wizard,
>etc.
>- Analysis Services: OLAP, Data Mining
>- Various bits and pieces of development libraries,
legacy utilities, etc.
>In general the scenarios for MSDE vs Personal Edition
are very different.
>MSDE is intended for use embedded within an application,
not as a
>multi-application database server. Personal Edition is
intended primarily
>as a client-side store for an application that runs both
on the desktop and
>on a server. Since the clients already require a CAL
(or the SQL Server
>server has Processor Licenses) there is no additional
cost to use Personal
>Edition in this scenario.
>Personal Edition has a secondary use, which is as a way
to install a full
>edition of SQL Server 2000 on a Windows Desktop OS. So
if you purchase
>Standard Edition but want to install on Windows XP, you
have to install
>Personal Edition. You are paying a lot for a server
product and then
>crippling it (both in the OS selection and the database
edition you need to
>install), but some people are willing to do that. They
save some $ on the
>OS, and they get the full SQL Server product other than
the governor. If it
>meets their needs, great.
>MSDE technically isn't free. The MSDE model is that you
buy a development
>tool and you receive the right to redistributed MSDE at
no charge with (and
>as part of) applications that you develop USING THAT
TOOL. Microsoft has one
>no cost development tool, ASP.NET Web Matrix, that
includes MSDE as well.
>But the no cost redistribution of MSDE in that case is
limited to
>applications developed with Web Matrix. There is
another scenario where you
>purchase a SQL Server 2000 edition and get MSDE
redistribution rights. In
>this case the SQL Server 2000 edition takes on the role
of the development
>tool.
>
>--
>Hal Berenson, SQL Server MVP
>True Mountain Group LLC
>
>"MT" <miketownsend33@.yahoo.com> wrote in message
>news:07e001c34b6b$1c866720$a401280a@.phx.gbl...
>> Besides the extra storage space. Why would I want to
use
>> the Personal Edition(not free) instead of MSDE (free).
I
>> have read the "Choosing an Edition" related information
>> but besides space issues, is there any reason not to
use
>> MSDE in favor of the Personal Edition?
>> Thanks,
>> Mike
>
>.
>
Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Permissions in SQLExpress
Hello. I have a problem with assigning permissions in SQLExpress.
First a little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry Dyck
Thanks,
Gerry
|||
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||
Thanks again have great holiday season and terrific new year,
Gerry
|||
First a little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry Dyck
It looks like you connect to the database as a member of PWNDAdmin and try to grant the role permissions you don't have. The error you were getting is caused by running as a low privileged principal with no access to the procedures. You don't need to grant db_owner status to grant execute permission, you just need to be able to grant permissions on the object and one way to achieve this would be to connect to the database as the dbo before granting the permissions.
Thanks
Laurentiu
Thanks,
Gerry
|||
But didn't you get db_owner status because you're a PWDNAdmin member and you made that a db_owner? Or were you a db_owner before that?
As a db_owner, you should be able to grant any permission on objects in the database.
Can you post the T-SQL grant statement that fails as well as the text and number of the error that you are receiving?
Thanks
Laurentiu
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||
Maybe the mystoredprocedure resides in a special schema and you should prefix its name with the schema name. The statement may fail because it cannot find the procedure within the schemas it searches by default.
Thanks
Laurentiu
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||
The grant ends up being part of the procedure definition, so when you will call the procedure, you will actually perform a grant on it. You should always use BEGIN and END to delimit the body of your stored procedure. Also, simply adding a GO before the grant would make the script run as intended, but I recommend always using BEGIN and END.
Thanks
Laurentiu
Thanks again have great holiday season and terrific new year,
Gerry
|||
I am glad I could be of help. Happy holidays to you as well.
Thanks
Laurentiu
Friday, March 9, 2012
Permissions
Hello. I have a problem with assigning permissions in SQLExpress. First a
little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry DyckHi,
add that users to db_datareader or db_ddladmin(it will grant permission to
Data Defination Languages)
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:
> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>|||Hi,
add them to db_ddladmin or grant db_datareader
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:
> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>
little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry DyckHi,
add that users to db_datareader or db_ddladmin(it will grant permission to
Data Defination Languages)
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:
> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>|||Hi,
add them to db_ddladmin or grant db_datareader
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:
> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>
Saturday, February 25, 2012
Permission levels for MSDE, ASAP
We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!Domain admins are allowed into the database by virtue of the fact that they are included in the Local Administrators group in the machine running MSDE. So, the quickest thing you could do is make your users local admins, rather than domain admins. In order to reduce their permissions further, you will have to settle what you want the users to be able to do. Local Admins can do anything to the instance of MSDE (configure settings, drop/add databases, forget to backup their data, etc.), but then, these are MSDE users, so they are usually only affecting themselves.|||Thanks again for replying so quickly! I am a little unsure what you mean by local admin though. It is an NT server, by the way. There is no group called Local Admin. I tried the group Administrators, but that didn't work. Do you mean on each workstation?|||We may have to back up a step here. MSDE is usually installed on workstations (my assumption, my bad). You have one central server, with various workstations connecting to the instance of MSDE on that server, then? Now we have a bit of a rat hole. Welcome to the world of SQL Server permissions. In other words, not much is going to be automatic here. Here is how you will likely have to start:
1) Create a domain group for the users who need to use the application.
2) On the server, with a command window, run
osql -E (-S servername\instancename)
NOTE: You may need to supply the MSDE instance name. If you get a numbered prompt, you are in.
3) switch to the application database by running the following:
1>use (dbname)
2>go
4) Run the following commands:
1>exec sp_grantlogin '(domain\new group from above)'
2>exec sp_grantdbaccess '(domain\new group from above)'
3>exec sp_addrolemember db_owner, '(domain\new group from above)'
Once you have done all that, you should be able to run without making people domain admins. I am not too wild about having you grant db_owner to the users, but they would have had sysadmin otherwise, but that is a long debate between programmers and DBAs that I probably do not need to invoke here. Good luck.|||I thank you very much for getting back with me. I apoligize for not replying in a few days, as I was vey busy. The method you described is what I was basically looking to do. I did exactly what you said, but it did not seem to work. I'm afraid I've done it wrong. I just wanted to make sure of the notiation you meant for me to use when typing it. Let's say for example:
1. the user group is called SQL Users
2. the domain is called DOMAIN
3. the server is called SERVER1
4. the database is called IT Help Desk
Would this be correct?
osql -E -S SERVER1
1>use [IT Help Desk]
2>go
1>exec sp_grantlogin '(DOMAIN\SQL Users)'
2>exec sp_grantdbaccess '(DOMAIN\SQL Users)'
3>exec sp_addrolemember db_owner, '(DOMAIN\SQL Users)'
Also...the single quotes...in the command prompt, there are open and close ones. The one that is usually used for both (located near the enter key on he keyboard) shows up as a close single quote. So what should be used? An open then close one? Both closed? Just wondering. If anyone else knows, feel fee to post as well.|||can you access the database thru sql enterprise manager?|||I'm using MSDE!! This is the free version of MSSQL that comes with MS Office. There is no EM for it.|||Sorry. If you lose the parentheses, you should be able to get it to work. If not, post whatever output (error message) you do get.|||It's not really an error message. It's just not able to connect to the server because of permission settings. I know this, but I can't get the server to let anyone connect. I tried without the pathrenthesis, but it still didn't work. TO be more detailed, when in MS Access, and trying to open a form (the point at which it needs to talk to the server) it gives me a run time error 18456, which says that the database could not log onto the server and to verify that the log on info is correct. I know that it's the groups that is the issue, because when I add the user that I am currently logged on as in the Domain Users group, it works fine. I think you were going in the right direction. I just wish MSDE had an interface to work with. I hate this stupid command line stuff. Too much coding. I'm not a professional programmer, altough I have taken VB a few years ago and know most of that. Other then that, web programming is all I know. Anyways, I'm heaing home for the holidays, this project isn't top priority, so I'll work on it when I get back. If you have any other ideas, please let me know. If you want to contact me directly, please email pilotboi@.comcast.net, thanks! Happy holidays!|||I had a similar problem with XP. If using Windows XP your (regular) local user will have very restricted user rights concerning writing to the registry. In addition there may be problems using the .NET framework and MSDE.
It may not be enough for installing or even using the MSDE. The MSDE requires writing to the registry, therefore the user does not need administrator rights but local user rights of a poweruser. We had the same problem. Have a look to the MSDE installation guide and/or readmes and the security settings of your .NET framework. This was helping us, the error messages did not lead to the right conclusions.|||You can use 3rd party tools to manage MSDE and the Enterprise Manager
I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.
Any ideas how how to make MSDE let anyone connect? Thanks in advance!Domain admins are allowed into the database by virtue of the fact that they are included in the Local Administrators group in the machine running MSDE. So, the quickest thing you could do is make your users local admins, rather than domain admins. In order to reduce their permissions further, you will have to settle what you want the users to be able to do. Local Admins can do anything to the instance of MSDE (configure settings, drop/add databases, forget to backup their data, etc.), but then, these are MSDE users, so they are usually only affecting themselves.|||Thanks again for replying so quickly! I am a little unsure what you mean by local admin though. It is an NT server, by the way. There is no group called Local Admin. I tried the group Administrators, but that didn't work. Do you mean on each workstation?|||We may have to back up a step here. MSDE is usually installed on workstations (my assumption, my bad). You have one central server, with various workstations connecting to the instance of MSDE on that server, then? Now we have a bit of a rat hole. Welcome to the world of SQL Server permissions. In other words, not much is going to be automatic here. Here is how you will likely have to start:
1) Create a domain group for the users who need to use the application.
2) On the server, with a command window, run
osql -E (-S servername\instancename)
NOTE: You may need to supply the MSDE instance name. If you get a numbered prompt, you are in.
3) switch to the application database by running the following:
1>use (dbname)
2>go
4) Run the following commands:
1>exec sp_grantlogin '(domain\new group from above)'
2>exec sp_grantdbaccess '(domain\new group from above)'
3>exec sp_addrolemember db_owner, '(domain\new group from above)'
Once you have done all that, you should be able to run without making people domain admins. I am not too wild about having you grant db_owner to the users, but they would have had sysadmin otherwise, but that is a long debate between programmers and DBAs that I probably do not need to invoke here. Good luck.|||I thank you very much for getting back with me. I apoligize for not replying in a few days, as I was vey busy. The method you described is what I was basically looking to do. I did exactly what you said, but it did not seem to work. I'm afraid I've done it wrong. I just wanted to make sure of the notiation you meant for me to use when typing it. Let's say for example:
1. the user group is called SQL Users
2. the domain is called DOMAIN
3. the server is called SERVER1
4. the database is called IT Help Desk
Would this be correct?
osql -E -S SERVER1
1>use [IT Help Desk]
2>go
1>exec sp_grantlogin '(DOMAIN\SQL Users)'
2>exec sp_grantdbaccess '(DOMAIN\SQL Users)'
3>exec sp_addrolemember db_owner, '(DOMAIN\SQL Users)'
Also...the single quotes...in the command prompt, there are open and close ones. The one that is usually used for both (located near the enter key on he keyboard) shows up as a close single quote. So what should be used? An open then close one? Both closed? Just wondering. If anyone else knows, feel fee to post as well.|||can you access the database thru sql enterprise manager?|||I'm using MSDE!! This is the free version of MSSQL that comes with MS Office. There is no EM for it.|||Sorry. If you lose the parentheses, you should be able to get it to work. If not, post whatever output (error message) you do get.|||It's not really an error message. It's just not able to connect to the server because of permission settings. I know this, but I can't get the server to let anyone connect. I tried without the pathrenthesis, but it still didn't work. TO be more detailed, when in MS Access, and trying to open a form (the point at which it needs to talk to the server) it gives me a run time error 18456, which says that the database could not log onto the server and to verify that the log on info is correct. I know that it's the groups that is the issue, because when I add the user that I am currently logged on as in the Domain Users group, it works fine. I think you were going in the right direction. I just wish MSDE had an interface to work with. I hate this stupid command line stuff. Too much coding. I'm not a professional programmer, altough I have taken VB a few years ago and know most of that. Other then that, web programming is all I know. Anyways, I'm heaing home for the holidays, this project isn't top priority, so I'll work on it when I get back. If you have any other ideas, please let me know. If you want to contact me directly, please email pilotboi@.comcast.net, thanks! Happy holidays!|||I had a similar problem with XP. If using Windows XP your (regular) local user will have very restricted user rights concerning writing to the registry. In addition there may be problems using the .NET framework and MSDE.
It may not be enough for installing or even using the MSDE. The MSDE requires writing to the registry, therefore the user does not need administrator rights but local user rights of a poweruser. We had the same problem. Have a look to the MSDE installation guide and/or readmes and the security settings of your .NET framework. This was helping us, the error messages did not lead to the right conclusions.|||You can use 3rd party tools to manage MSDE and the Enterprise Manager
Permission Issue
I am trying to test a new ASP.NET application on my local instance of MSDE.
I created a virtual directory named "db" with a sub-directory of
"processed". My web page opens an XML file in the "db" directory, does some
processing and then I want to move the file to the "processed"
sub-directory. When I execute the Move I get the following error:
'************************************************* ***
Exception Details: System.UnauthorizedAccessException: Access to the path
"C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
ASP.NET is not authorized to access the requested resource. Consider
granting access rights to the resource to the ASP.NET request identity.
ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
Network Service on IIS 6) that is used if the application is not
impersonating. If the application is impersonating via <identity
impersonate="true"/>, the identity will be the anonymous user (typically
IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET write access to a file, right-click the file in Explorer,
choose "Properties" and select the Security tab. Click "Add" to add the
appropriate user or group. Highlight the ASP.NET account, and check the
boxes for the desired access.
Source Error:
Line 50: End If
Line 51: Response.Write("Executing File.Move command!<br>")
Line 52: File.Move(strFilename, fntarget)
Line 53: '*****
Line 54: Else
'************************************************* ****
The instructions don't seem to apply to my system (Win XP Pro SP1)? The
options are different and I don't see any "ASP.NET" account? I suspect I am
min-interpreting something here?
Thanks for any help.
Wayne
Hi
Post this question in microsoft.public.inetserver.iis.security as it is not
a database problem.
Regards
Mike
"Wayne Wengert" wrote:
> I am trying to test a new ASP.NET application on my local instance of MSDE.
> I created a virtual directory named "db" with a sub-directory of
> "processed". My web page opens an XML file in the "db" directory, does some
> processing and then I want to move the file to the "processed"
> sub-directory. When I execute the Move I get the following error:
> '************************************************* ***
> Exception Details: System.UnauthorizedAccessException: Access to the path
> "C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
> ASP.NET is not authorized to access the requested resource. Consider
> granting access rights to the resource to the ASP.NET request identity.
> ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
> Network Service on IIS 6) that is used if the application is not
> impersonating. If the application is impersonating via <identity
> impersonate="true"/>, the identity will be the anonymous user (typically
> IUSR_MACHINENAME) or the authenticated request user.
> To grant ASP.NET write access to a file, right-click the file in Explorer,
> choose "Properties" and select the Security tab. Click "Add" to add the
> appropriate user or group. Highlight the ASP.NET account, and check the
> boxes for the desired access.
> Source Error:
>
> Line 50: End If
> Line 51: Response.Write("Executing File.Move command!<br>")
> Line 52: File.Move(strFilename, fntarget)
> Line 53: '*****
> Line 54: Else
> '************************************************* ****
> The instructions don't seem to apply to my system (Win XP Pro SP1)? The
> options are different and I don't see any "ASP.NET" account? I suspect I am
> min-interpreting something here?
> Thanks for any help.
> Wayne
>
>
>
|||OK - will do
Wayne
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:95192DB5-306F-4001-B69D-9471BEE2088D@.microsoft.com...
> Hi
> Post this question in microsoft.public.inetserver.iis.security as it is
not[vbcol=seagreen]
> a database problem.
> Regards
> Mike
> "Wayne Wengert" wrote:
MSDE.[vbcol=seagreen]
some[vbcol=seagreen]
path[vbcol=seagreen]
or[vbcol=seagreen]
Explorer,[vbcol=seagreen]
am[vbcol=seagreen]
I created a virtual directory named "db" with a sub-directory of
"processed". My web page opens an XML file in the "db" directory, does some
processing and then I want to move the file to the "processed"
sub-directory. When I execute the Move I get the following error:
'************************************************* ***
Exception Details: System.UnauthorizedAccessException: Access to the path
"C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
ASP.NET is not authorized to access the requested resource. Consider
granting access rights to the resource to the ASP.NET request identity.
ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
Network Service on IIS 6) that is used if the application is not
impersonating. If the application is impersonating via <identity
impersonate="true"/>, the identity will be the anonymous user (typically
IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET write access to a file, right-click the file in Explorer,
choose "Properties" and select the Security tab. Click "Add" to add the
appropriate user or group. Highlight the ASP.NET account, and check the
boxes for the desired access.
Source Error:
Line 50: End If
Line 51: Response.Write("Executing File.Move command!<br>")
Line 52: File.Move(strFilename, fntarget)
Line 53: '*****
Line 54: Else
'************************************************* ****
The instructions don't seem to apply to my system (Win XP Pro SP1)? The
options are different and I don't see any "ASP.NET" account? I suspect I am
min-interpreting something here?
Thanks for any help.
Wayne
Hi
Post this question in microsoft.public.inetserver.iis.security as it is not
a database problem.
Regards
Mike
"Wayne Wengert" wrote:
> I am trying to test a new ASP.NET application on my local instance of MSDE.
> I created a virtual directory named "db" with a sub-directory of
> "processed". My web page opens an XML file in the "db" directory, does some
> processing and then I want to move the file to the "processed"
> sub-directory. When I execute the Move I get the following error:
> '************************************************* ***
> Exception Details: System.UnauthorizedAccessException: Access to the path
> "C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
> ASP.NET is not authorized to access the requested resource. Consider
> granting access rights to the resource to the ASP.NET request identity.
> ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
> Network Service on IIS 6) that is used if the application is not
> impersonating. If the application is impersonating via <identity
> impersonate="true"/>, the identity will be the anonymous user (typically
> IUSR_MACHINENAME) or the authenticated request user.
> To grant ASP.NET write access to a file, right-click the file in Explorer,
> choose "Properties" and select the Security tab. Click "Add" to add the
> appropriate user or group. Highlight the ASP.NET account, and check the
> boxes for the desired access.
> Source Error:
>
> Line 50: End If
> Line 51: Response.Write("Executing File.Move command!<br>")
> Line 52: File.Move(strFilename, fntarget)
> Line 53: '*****
> Line 54: Else
> '************************************************* ****
> The instructions don't seem to apply to my system (Win XP Pro SP1)? The
> options are different and I don't see any "ASP.NET" account? I suspect I am
> min-interpreting something here?
> Thanks for any help.
> Wayne
>
>
>
|||OK - will do
Wayne
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:95192DB5-306F-4001-B69D-9471BEE2088D@.microsoft.com...
> Hi
> Post this question in microsoft.public.inetserver.iis.security as it is
not[vbcol=seagreen]
> a database problem.
> Regards
> Mike
> "Wayne Wengert" wrote:
MSDE.[vbcol=seagreen]
some[vbcol=seagreen]
path[vbcol=seagreen]
or[vbcol=seagreen]
Explorer,[vbcol=seagreen]
am[vbcol=seagreen]
Subscribe to:
Posts (Atom)