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 Script with dependent objects SQL Server 2005

Hi,

I need to script a view but with dependent objects. The view has a lot of user defined functions and manually creating the script is a pain.

There is a way to do it in SQL Server (2000) Enterprise manager where one right clicks the view --> All Tasks --> Generate Script and then Check

"Generate Scripts for all dependent objects" in the Formatting tab.

The db i am using is on SQL Server 2005.

Is there a equivalent way to do this in SQL Server 2005 Management Studio ?

regards,

Rohan Wali

Have you tried the generate script wizard in SSMS?|||You might try using SMO in this case http://www.sqlteam.com/item.asp?ItemID=23185, as I don't see using script wizard.|||

Hi,

Even i dont see a solution using the script wizard. I will try using SMO using the link provided and see how it goes from there.

Thanks for the tip.

regards,

Rohan Wali

sql

Create Procedure Output ntext

Hello,

I need to produce with T-SQL a user defined function or stored
procedure that make one SLQ-Statement and prepare as string from the
result set.
The request muss be able to return a very long unicode string. The
return value nvarchar is being truncated so I'm trying to create a
stored procedure, that returns a ntext string.

I can't manage it (I am no T-SQL specialist). Maybe someone can help
me?

Thanks for your help.

--
Here is my sp:
alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
@.return ntext output)
AS
BEGIN
DECLARE @.onelevel nvarchar(4000)
DECLARE @.pos varchar(1000)
DECLARE @.leveldone varchar(100)
DECLARE @.levelplaned varchar(100)
DECLARE @.planeddate nvarchar(4000)
DECLARE @.elementid varchar(10)
DECLARE @.levelid varchar(10)
DECLARE @.levelstatus varchar(10)
DECLARE @.levelupd nvarchar(4000)
DECLARE @.levelauthor varchar(10)
DECLARE @.prevelementid varchar(10)

BEGIN
declare level_cursor CURSOR FOR
SELECT
B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
convert(varchar,A.Upd,126) as Upd,A.Author
FROM T_TABLE1 as B left outer join T_TABLE2 as A on
(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
where B.ParentID=@.parentid
ORDER BY B.ElementID,B.ElementPos
END

set @.onelevel=''

OPEN level_cursor

FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
@.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor

WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.prevelementid=@.elementid

if (@.pos IS NULL)
set @.onelevel=''
else
set @.onelevel=@.pos

if (@.elementid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.elementid

if (@.levelid IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelid

if (@.leveldone IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.leveldone

if (@.levelplaned IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelplaned

if (@.planeddate IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.planeddate

if (@.levelstatus IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelstatus

if (@.levelupd IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelupd

if (@.levelauthor IS NULL)
set @.onelevel=@.onelevel+'*-*'
else
set @.onelevel=@.onelevel+'*-*'+@.levelauthor

-- Part Output
print @.onelevel

if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)

FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor

if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
FETCH NEXT FROM level_cursor
INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
@.planeddate,@.elementid,
@.levelstatus,@.levelupd,@.levelauthor

END

CLOSE level_cursor
DEALLOCATE level_cursor

RETURN

END

--
Call of the function with:
exec dbo.F_FUNCTION 550,1632, 0, ''

Here the beginning of the query analyser output:

1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.

--I think you cannot return that using an output parameter - you'll have to
return it as a field in a SELECT.

On 24 Oct 2005 08:17:01 -0700, rey@.infoman.de wrote:

>Hello,
>I need to produce with T-SQL a user defined function or stored
>procedure that make one SLQ-Statement and prepare as string from the
>result set.
>The request muss be able to return a very long unicode string. The
>return value nvarchar is being truncated so I'm trying to create a
>stored procedure, that returns a ntext string.
>I can't manage it (I am no T-SQL specialist). Maybe someone can help
>me?
>Thanks for your help.
>--
>Here is my sp:
>alter procedure F_FUNCTION (@.userid int, @.parentid int, @.status int,
>@.return ntext output)
>AS
>BEGIN
> DECLARE @.onelevel nvarchar(4000)
> DECLARE @.pos varchar(1000)
> DECLARE @.leveldone varchar(100)
> DECLARE @.levelplaned varchar(100)
> DECLARE @.planeddate nvarchar(4000)
> DECLARE @.elementid varchar(10)
> DECLARE @.levelid varchar(10)
> DECLARE @.levelstatus varchar(10)
> DECLARE @.levelupd nvarchar(4000)
> DECLARE @.levelauthor varchar(10)
> DECLARE @.prevelementid varchar(10)
> BEGIN
> declare level_cursor CURSOR FOR
> SELECT
>B.ElementPos,B.LevelID,A.LevelDone,A.LevelPlaned,A .PlanedDate,A.MatrixContentID,A.Status,
>convert(varchar,A.Upd,126) as Upd,A.Author
> FROM T_TABLE1 as B left outer join T_TABLE2 as A on
>(A.MatrixContentID=B.ID AND A.UserID=@.userid AND A.Status<>3)
> where B.ParentID=@.parentid
> ORDER BY B.ElementID,B.ElementPos
> END
> set @.onelevel=''
> OPEN level_cursor
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid,@.leveldone, @.levelplaned,
> @.planeddate,@.elementid, @.levelstatus,@.levelupd,@.levelauthor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>set @.prevelementid=@.elementid
> if (@.pos IS NULL)
> set @.onelevel=''
> else
> set @.onelevel=@.pos
> if (@.elementid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.elementid
> if (@.levelid IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelid
> if (@.leveldone IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.leveldone
> if (@.levelplaned IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelplaned
> if (@.planeddate IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.planeddate
> if (@.levelstatus IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelstatus
> if (@.levelupd IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelupd
> if (@.levelauthor IS NULL)
> set @.onelevel=@.onelevel+'*-*'
> else
> set @.onelevel=@.onelevel+'*-*'+@.levelauthor
> -- Part Output
> print @.onelevel
> if (@.return is NULL)
> exec(@.return+@.onelevel)
> else
> exec(@.return+'*;*'+@.onelevel)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> if (@.prevelementid IS NOT NULL AND @.prevelementid=@.elementid)
> FETCH NEXT FROM level_cursor
> INTO @.pos,@.levelid, @.leveldone, @.levelplaned,
>@.planeddate,@.elementid,
> @.levelstatus,@.levelupd,@.levelauthor
> END
> CLOSE level_cursor
> DEALLOCATE level_cursor
> RETURN
>END
>--
>Call of the function with:
>exec dbo.F_FUNCTION 550,1632, 0, ''
>Here the beginning of the query analyser output:
>1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '*'.
>--|||Am 24 Oct 2005 08:17:01 -0700 schrieb rey@.infoman.de:

...
> --
> Call of the function with:
> exec dbo.F_FUNCTION 550,1632, 0, ''
> Here the beginning of the query analyser output:
> 1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '*'.
> --

ntext is a valid datatype for the OUTPUT variable of a stored procedure.
This is not the problem. Your error appears here:
...
-- Part Output
print @.onelevel

if (@.return is NULL)
exec(@.return+@.onelevel)
else
exec(@.return+'*;*'+@.onelevel)
...
because if @.return is Null then you do a
exec('1.00000*-*691*-*1684*-*3*-*0*-**-*0*-*2005-09-22T00:43:00*-*277')
and what should this be?
EXEC starts another stored proc, from there you get your error message. And
so it is an error in line 1.

bye
Helmut|||Hello Helmut,

thanks for your answer.
With exec(@.return+@.onelevel) I try to fill my @.return variable with
the content of @.onelevel. I can't do it with set @.return=@.onelevel
because @.return is of type ntext.

How can I do it else?

thanks and bye,
Agns.|||Not sure if you can do this with ntext, but try this

select @.return = @.return + '*;*' + @.onelevel|||(rey@.infoman.de) writes:
> thanks for your answer.
> With exec(@.return+@.onelevel) I try to fill my @.return variable with
> the content of @.onelevel. I can't do it with set @.return=@.onelevel
> because @.return is of type ntext.
> How can I do it else?

You can't. Since you are in a dead end, I suggest that you explain
your underlying business problem that you are trying to solve. What
does the calling side of this look like?

I can offer one workaround: move to SQL 2005, which offers the
new datatype nvarchar(MAX), which in difference to ntext is a first
class citizen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 25 Oct 2005 00:35:55 -0700, rey@.infoman.de wrote:

>Hello Helmut,
>thanks for your answer.
>With exec(@.return+@.onelevel) I try to fill my @.return variable with
>the content of @.onelevel. I can't do it with set @.return=@.onelevel
>because @.return is of type ntext.
>How can I do it else?
>thanks and bye,
>Agns.

What's wrong with returning it via SELECT rather than via a parameter?