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 CLR Based Stored Procedures

I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.

O sure it does, its located under File --> New --> Project --> Visual C# --> Database --> SQL Server Project.

If its not located in there you have to reinstall the Visual Studio template from the setup file.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

how to create an stored procedure to an existan database?

i already hava an database in the MSDE database server, but now i want to add some stored procedures to that specifi database in the MSDE database server, how can i do that?You can use Enterprise Manager from SQL Server, and if you have the correct edition, you can use Visual Studio.NET.|||is there any other way?? like can i use comand prompt to create stored procedures?i want that way, and I have MSDE.|||I did reply to yourother post, and now that I see what you want to do, I also recommend this link:Administering SQL Server Using osql. There are further links at the bottom which should help you with what you are trying to do.

Terri|||thanks very much
that helps me a lot.sql

How to crack encrypted store procedure in SQL 2005?

Dear all,
Is it possible to crack encrypted store procedures in SQL 2005? I have an
application that uses a lot of stored procedures in SQL 2005 that I must
protect the source codes in store procedures. I am worry about users can
crack encrypted stored procedures so my application source codes will be
re-engineering completely.
Please share with me your thoughts how to protect source codes in stored
procedure in SQL server.
Thanks.
Maggie.Maggie,
Yes, they can be cracked.
If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
If it is worth $99 to someone: http://www.elitude.net/
And then there is this from the August 2007 SQL Server Magazine:
http://www.sqlmag.com/Article/Artic...rver_95728.html
Note that the code is downloadable, even if you are not a subscriber who can
read the whole article. It does require a significant number of rights to
be able to run this decryption procedure, but on another server not in your
control you have no control over what people do.
You can encrypt, of course, but legal protection comes through contracts and
other legal agreements.
RLF
"Maggie" <Maggie@.discussions.microsoft.com> wrote in message
news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
> Dear all,
> Is it possible to crack encrypted store procedures in SQL 2005? I have an
> application that uses a lot of stored procedures in SQL 2005 that I must
> protect the source codes in store procedures. I am worry about users can
> crack encrypted stored procedures so my application source codes will be
> re-engineering completely.
> Please share with me your thoughts how to protect source codes in stored
> procedure in SQL server.
> Thanks.
> Maggie.
>|||Hi Russell,
Thanks a lot for the reply, it helps me a lot.
Maggie.
"Russell Fields" wrote:

> Maggie,
> Yes, they can be cracked.
> If it is worth $79.95 to someone: http://www.devlib.net/decryptsql.htm
> If it is worth $99 to someone: http://www.elitude.net/
> And then there is this from the August 2007 SQL Server Magazine:
> http://www.sqlmag.com/Article/Artic...rver_95728.html
> Note that the code is downloadable, even if you are not a subscriber who c
an
> read the whole article. It does require a significant number of rights to
> be able to run this decryption procedure, but on another server not in you
r
> control you have no control over what people do.
> You can encrypt, of course, but legal protection comes through contracts a
nd
> other legal agreements.
> RLF
> "Maggie" <Maggie@.discussions.microsoft.com> wrote in message
> news:DB70D424-0BE5-490C-9794-6676AFE47E2E@.microsoft.com...
>
>

how to copy tables and its data with procedures etc to another database in MS sql 2005

i want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005.

can any one help

plz reply

tnx alot

If you need the data then there is no way to use SQL: Scripting but you can backup the database and restore it in the other place.

Another solution is to use the copy database (copy between instances) or the DTS.

Let me know if you need further info.

Regards

|||

Thanx brother for Repy !

DTS services are in Sqlserver2000 enterprise manager for using them it is necessary to attach my .mdf file in sqlserver2000 but when i try to attach an mdf file created from sqlserver2005 express edition i am getting error message and i cant use backup feature because i want to move only some tables along with their data to another database not duplicating the same database.

|||

When you install mssql 2005 on an exisiting version of mssql 2000 the DTS will be upgraded.

If you don't want to waste your time in DTS, create a backup of the db and restore it in the other db,

Regards

|||

backup/restoring method not workng, it gives error messge as " backupset holds the backup of database other than the existing"

tnx for reply

|||How did you do that? Let me know what you have done step by step.

How to copy store procedure from SQL 2000 to 2005?

I'm using MS SQL Server Management Studio. How do copy my store procedures that are on the SQL Server 2000 to SQL Server 2005? What are my options? I don't have admin rights...only rights to my database.you can script the procedures in sql 2000 and run the script in sql 2005 for the procs to be created..............but i believe you need to have sysadmin privilege...........|||

You can read the definition from

SELECT Routine_definition
FROM INFORMATION_SCHEMA.Routines
Where Routine_Name = 'YourProcedureName'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Expanding on Jens' comments:

Since the INFORMATION_SCHEMA views are accessible by 'Public', you may read the procedure definitions, and then you can copy and paste into a new query window for the SQL 2005 server, and after you execute the code, the procedure will be saved in the SQL 2005 server.

|||

Now, this is one procedure at a time, correct?

Thanks!

|||

That's correct.

If you had admin rights, then there are much easier methods.

How to copy sql express tables & stored procedures into remote full sql server 2005

Hi all,

I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .

my web site is ready

I didn't have problem to upload my site to my hoster.

Now I want to upload all my tables and my stored procedure create locally with VWD express

How can i do it ?

NB: I know i can't design DB (create/modify tables and stored proc) with express edition

thank's for your help

Hi all,

I have my all tables and stored procedures on the remote server and my site is working well but it was very hard to.

let me resume SSMSE steps for tables:

1. Connect to local server

2. Connect to remote server

3. Create Sql file by using "Script out Create " command on the local table

4. Change connection from local to remote

5. execute your sq file to create table on the remote DB

6. Open table on remote DB

7. Open table on local DB

8. use copy/paste to copy rows from local to remote

you must do it for all table including aspnet tables when you use features like menbership, roles profiles and personalization

for stored procedures no steps 6 7 8 and you must change the name of the databases on thesql file

how to copy only the stored procedures to another server/database

Hi all,
I am just getting my feet wet with SQL server, and need
to only copy the stored procedures from one db to another. I tried the
export tool, but even though it allows me to select the objects I want, it
also tries to copy other objects (such as user names), and it fails because
they already exist in the other db.
Thanks in advance,
Carlos.Don=B4t know what you mean by "export tool", But either in the Script
generator of the Enterprise manager as well as in the DTS wizard you
can define if you want to script out / export the depended objects. If
you don=B4t want them just change the tab, deselct the options and
script out / export the objects to the other database.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--

how to copy only the stored procedures to another server/database

Hi all,
I am just getting my feet wet with SQL server, and need
to only copy the stored procedures from one db to another. I tried the
export tool, but even though it allows me to select the objects I want, it
also tries to copy other objects (such as user names), and it fails because
they already exist in the other db.
Thanks in advance,
Carlos.Don=B4t know what you mean by "export tool", But either in the Script
generator of the Enterprise manager as well as in the DTS wizard you
can define if you want to script out / export the depended objects. If
you don=B4t want them just change the tab, deselct the options and
script out / export the objects to the other database.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--