How to create objects under dbo without DDL admin rights??

Does someone have any suggestions on what rights to grant a user to allow
them to only create stored procedures, functions under dbo without granting
DDL admin?Hi,
Open Database properties and the under permissions you can set for user to
create and alter objects.
Danijel Novak
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||That I know, but I want a user to be able to create a new procedure, but
create it as dbo.procedure. As far as I know, you cannot do this without DDL
Admin rights.
"Danijel Novak" wrote:

> Hi,
> Open Database properties and the under permissions you can set for user to
> create and alter objects.
> --
> Danijel Novak
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>|||In SQL 2000 and earlier versions, the user must be either:
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member
Of course, membership in these role provides considerably more permissions
as well.
Note that the ability to create dbo-owned procs and functions essentially
allows users to retrieve and manipulate data in all dbo-owned tables. This
is one reason why one typically allows only DBAs to create dbo-owned
objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||Hi,
true said by Dan , that's why ! we only do it with only
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member , so in shore please don't permit it to not dbo
user.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Dan Guzman" wrote:

> In SQL 2000 and earlier versions, the user must be either:
> - a db_ddladmin role member
> - a db_owner role member
> - the database owner
> - a sysadmin role member
> Of course, membership in these role provides considerably more permissions
> as well.
> Note that the ability to create dbo-owned procs and functions essentially
> allows users to retrieve and manipulate data in all dbo-owned tables. Thi
s
> is one reason why one typically allows only DBAs to create dbo-owned
> objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>

how to create function which eval expression

hi all,
I would like to create a function which can eval string expressiion
select dbo.fn_eval_string('1+2+3')
return 6
and if I do dbo.fn_eval_string('2-(5-3)')
return 0
is that possible? it should also support multiply and division.Kevin,
One possible solution is to use dynamic sql, but you can not use it in a
udf. You will have to use a stored procedure instead.
Example:
create procedure p1
@.s varchar(50),
@.i int output
as
set nocount on
declare @.sql varchar(4000)
set @.sql = N'select @.i = ' + @.s
exec sp_executesql @.sql, N'@.i int output', @.i output
go
declare @.i int
exec p1 '2 * (3 + 5)', @.i output
print @.i
go
The code has not been tested.
Be careful with the use of dynamic sql, there are pros and cons that you
should be aware of, before using it.
http://www.sommarskog.se/dynamic_sql.html
AMB
"Kevin" wrote:

> hi all,
> I would like to create a function which can eval string expressiion
> select dbo.fn_eval_string('1+2+3')
> return 6
> and if I do dbo.fn_eval_string('2-(5-3)')
> return 0
> is that possible? it should also support multiply and division.
>
>

How to create a user with name dbo and loginname KING

How to create a user with name dbo and loginname KING.
So that When I open Users Tab in the database, it should read Name as dbo and lOGIN AS kingCreate the login for KING, then use sp_changedbowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_30s2.asp) ?

-PatP

How to create a CLR integrated trigger on table in schema?

In CLR integrated trigger:

If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees

I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]

How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.

Thank you.

Hi,

this was discussed in an earlier thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=132062&SiteID=1

Normally you should be able to only prefix the Schema before the table target.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to count rows in this query...

SELECT ID_AnagraficaRivendita
FROM dbo.AnagraficaRivendite
WHERE EXISTS
(SELECT *
FROM dbo.Flussi_Rivendite
WHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)
AND EXISTS
(SELECT *
FROM dbo.Flussi_Rivendite
WHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)
GROUP BY ID_AnagraficaRivendita

hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)

There are so many ways like using COUNT(). But I always prefer usingROW_NUMBER()

e.g.

SELECT ROW_NUMBER()OVER (ORDER BY ID_AnagraficaRivenditaASC)AS Row, ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHEREEXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita

|||

try this (it was not tested but it should give you idea if it will not work)

SELECT ID_AnagraficaRivendita, FL.CodiceProdotto,count(*) Counter

FROM dbo.AnagraficaRivendite

LeftjoinFROM dbo.Flussi_Rivendite FL

ON dbo.AnagraficaRivendite.ID_AnagraficaRivendita= FLID_AnagraficaRivendita

and FL.CodiceProdottoin(631,615)

wherenot FL.CodiceProdottoisnull

groupby ID_AnagraficaRivendita, FL.CodiceProdotto

|||

Thank you for your reply, I have added ROW_NUMBER()OVER (ORDER BY ID_AnagraficaRivenditaASC)AS Row but it doesn't work, I receive an error (ADO Error: ROW_Number is not a valid function)

I don't know if is important: I am working on Sql Server 2000 and my query is in a Stored Procedure.

|||

RETURN @.@.ROWCOUNT

http://msdn2.microsoft.com/en-us/library/ms187316.aspx

|||

row_number is new in SQL 2005, not available in SQL 2000

|||

At my home pc I have just tried instruction ROW_NUMBER on SQL Server 2005 and it runs, but maybe my question it was not clear, sorry: I need TOTAL of extracted rows, on the contray with ROW_NUMBER I receive a progressive numeration of record (if it's possible, please suggest me a solution compatible on SQL Server 2000, too)

|||

Change your stored procedure into this:

SELECTCOUNT(ID_AnagraficaRivendita)AS TotalRows, ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHEREEXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita

Howevere let me also tell you thatthis is not at all an elegant solution. If you know how to work with return values, I suggest you addRETURN@.@.ROWCOUNT at the end of your original query, as suggested by DisturbedBuddha.

~

|||

thanks my friends