create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.
On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:

>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:
>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create record each day from time frame

I have a table that has cost records for a specific item for a specific star
t
date.
Start, cost, item, PromoCode, end date
01/02/2006, 2.45, 1234, R,
01/05/2006, 2.00, 1234, P, 01/08/2006
01/10/2006, 2.55, 1234, R,
If a record has a start date then that new cost begains. And if it doesn't
have an end date it will go indefinitely. Also when a promo (P) ends then
the cost goes back to the Regular (R) cost record.
So the cost records that I would create from the above records is
01/02/2006, 2.45, 1234, R
01/03/2006, 2.45, 1234, R
01/04/2006, 2.45, 1234, R
01/05/2006, 2.00, 1234, P
01/06/2006, 2.00, 1234, P
01/07/2006, 2.00, 1234, P
01/08/2006, 2.45, 1234, R
01/09/2006, 2.45, 1234, R
01/10/2006, 2.55, 1234, R
01/10/2006, 2.55, 1234, R
.....
Looking for any help with how to start a stored procedure or query to come
up with these records.
Thanks!Suggest joining to an auxiliary calendar table
See http://www.aspfaq.com/2519

CREATE ENDPOINT to Expose a single DB instance

I have been looking at this statement for allowing access from a remote location to a specific database on my SQL Server (2005) from a client application.

Is it possible to do this using this method? I see lots of examples using this to expose Web Services of DB objects, however I need the client application to be able to authenticate to and access all objects in the database. If so, could some one provide a simple example of the arguments to use? For example, which protocol arguments are valid for this? I would like to use SQL Server Authentication. Is this possible?

Regards,

-Troy

I think the answer is to create a new instance of SQL Server on a non-standard port and move the DB to that instance. At least that is what I've chosen to do. If anyone knows of a better way, let me know.

Create cursor from stored procedure

I have a stored procedure which I want to view the results in a specific
sorted order, however I do not have control over the stored procedure as it
has been created with the ENCRYPTED keyword. I tried to use the INSERT
tablename (columns) EXE storedproc syntax, but I get the error about using
nested insert exec.
So my next attempt is to try the following:
DECLARE myCursor CURSOR FOR
storedprocedure
But I don't know if this is possible in any way. Could someone please give
me a suggestion?
Thanks,
MarkMark Miller wrote:
> I have a stored procedure which I want to view the results in a specific
> sorted order, however I do not have control over the stored procedure as it
> has been created with the ENCRYPTED keyword. I tried to use the INSERT
> tablename (columns) EXE storedproc syntax, but I get the error about using
> nested insert exec.
> So my next attempt is to try the following:
> DECLARE myCursor CURSOR FOR
> storedprocedure
> But I don't know if this is possible in any way. Could someone please give
> me a suggestion?
> Thanks,
> Mark
>
Try using OPENQUERY to execute the stored procedure:
SELECT *
FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
ORDER BY somefield
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, Tracy.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E480DA.3090801@.realsqlguy.com...
> Mark Miller wrote:
>> I have a stored procedure which I want to view the results in a specific
>> sorted order, however I do not have control over the stored procedure as
>> it has been created with the ENCRYPTED keyword. I tried to use the INSERT
>> tablename (columns) EXE storedproc syntax, but I get the error about
>> using nested insert exec.
>> So my next attempt is to try the following:
>> DECLARE myCursor CURSOR FOR
>> storedprocedure
>> But I don't know if this is possible in any way. Could someone please
>> give me a suggestion?
>> Thanks,
>> Mark
> Try using OPENQUERY to execute the stored procedure:
> SELECT *
> FROM OPENQUERY(<<servername>>, 'EXEC storedprocedure')
> ORDER BY somefield
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com