Grant "create Stored Procedure"

Feb 24, 2000

Hi!
How do I grant rights to an user for creating stored procedures (using T-SQL)?
Thanks,
Fabio

View 1 Replies


ADVERTISEMENT

Grant CREATE VIEW, CREATE PROCEDURE ...

Apr 12, 2006

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

View 8 Replies View Related

Grant Create Procedure Using SSMS

Jun 5, 2008

Is it possible to use sql server managment studio to grant the create procedure to a user on their own schema?

View 2 Replies View Related

How To Grant Create Procedure Permission To Database User?

Apr 7, 2008

Can somebody tell me without pointing to any other link how to grant Create Procedure permission to DB user.

View 3 Replies View Related

How To Grant Create View Access Without Grant Alter On Schema::

Aug 16, 2007

In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.

Is there any way to grant this privilage without granting alter on schema also?

View 1 Replies View Related

Stored Procedure To Grant All Users Db_owner

Jul 20, 2005

I am trying to come up with a stored procedure to grant all usersdb_owner role (rather than have to manually check the users eachtime)Thanks in advance for your helpTY

View 1 Replies View Related

Grant Create - And Execute - Procedures

May 6, 2008

I am creating a login with a user in one database. The id has to
create procedures in the dbo schema, execute them, and grant execute on them to other users.
BOL says to
GRANT ALTER ON SCHEMA::DBO TO username ;
GRANT CREATE PROCEDURE TO username ;
I did that. User can now create procedures, but cannot execute what he creates. And cannot grant execute on the sp to anybody.

I don't want to give this id a lot of priviledges.

View 16 Replies View Related

How To Grant Permission To Create A New Object

Jan 23, 2008

I am new to sql server. right now i getting used to management studio.I am trying to creata a new object / a new database but i am getting an error which says permission not granted

can any help me to move ahead

thanks

View 1 Replies View Related

How To Grant User Permission To Create Other Users?

Jun 26, 2007

There's something I can't quite figure out about user creating



The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).



Question is how can I allow users to create these user with giving them as few permissions as possible.



If there's is somewhere a code sample on the net I would appreciate the link.







View 1 Replies View Related

SQL 2012 :: Grant Statements To Create Userdefined Role

May 11, 2015

Need to create a user defined role with grant permissions for below .

View Definition
Execute all Function
Grant View
Grant Synonym
dbo
View Definition
Not getting grant statements for above permissions.

I mean like below.

-----------------------------------------------------------------
CREATE ROLE [Role1]
GRANT EXECUTE ON SCHEMA ::dbo TO [Role1]
-----------------------------------------------------------------

View 1 Replies View Related

How Can I Create Stored Procedure?

Feb 13, 2008

 forumid    questid   answerid   answer     replyby   replyon    1            1            1            xxxx        aaa        01/01/08(mm/dd/yy)    1            1            2            yyyy        bbb        01/02/08    2            1            1            zzzz        ccc        01/02/08    1            1            3            hhhh         bbb        01/04/08    2            1            2            uuuu         vvv         01/04/08    1            2            1            tttt            ooo        01/05/08suppose i give forumid value=1 i want following answer recent reply and no.of replies  forumid    questid   answerid  no.of.reply  answer     replyby   replyon            1            1            3            3               hhhh        bbb       01/04/08      1            2            1            1               ttt            ooo        01/05/08 

View 14 Replies View Related

Create Stored Procedure

Apr 21, 2004

Hi!

I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.

Please help me!

Mike.

View 5 Replies View Related

How To Create Rdl Using Stored Procedure Which Use

Apr 3, 2008

Hi

I am sql server 2005.

how to create rdl using stored procedure which uses parameter


Thank you.

View 1 Replies View Related

Create Stored Procedure

May 6, 2008

Is there a way to create a stored procedure that accepts a column name and returns the name of the tables that have that column name?

View 2 Replies View Related

Create A Stored Procedure

May 7, 2008

I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:

create proc spTheNameOfTheProcedure
as
select table_name from information_schema.columns
where column_name = XXXXXX

no sure what to put where I have XXXXX

View 2 Replies View Related

Create Stored Procedure

Dec 22, 2007

Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007

If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)


results would be 10_12_2007,10_13_2007.

So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3

View 1 Replies View Related

How To Create A PDF From A Stored Procedure

Sep 18, 2007

I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):

First Name Last Name Address
Mike Mik Jr 141552 South
Charlie D 1422141

Lets say my table name whichthat has all these data is called dbo.TestTable
I spent so much time in google and I have not found one simple good example. Can you help me please


Thanks in advance for your help

View 5 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.

View 9 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

Stuck Trying To Create A Stored Procedure

May 3, 2005

Hi

Let me start by saying that SQL is
not my strong suit so please bear with me.

Here's my situation
I'm building a property portfolio system with db structure as follows

tblProperty (The master table - linked 1 to many via propertyID)
tblValuations (columns valuationID, propertyID, currentValue, valuationDate)

For each property in tblProperty the valuations table can hold multiple valuation entries.

I need to have a summary that shows the number of properties a user has
listed and crucially I need to be able to find the latest
valuation for each property & add them together to retrieve the
total value of a users portfolio

I'm totally stumped with this so any help/advice would be gratefully received

Lee

 

View 1 Replies View Related

How To Create An Array In A Stored Procedure

Aug 8, 2000

I just want to Know how to create an Array in a stored procedure. Please can you give the syntax and any Example.

View 1 Replies View Related

VB5 Create Temporary Stored Procedure

Sep 3, 1998

When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?

The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240

View 4 Replies View Related

Create Stored Procedure Without Admin Right

Mar 12, 2001

I have no right to connect to my hosting company using MS SQL2000 client.
And I have to use stored procedure, can any body tell me is it possible to
create stored procedure ( I need is complex one with loops of if loops).
If possible please list the syntax.

View 2 Replies View Related

Unable To Create Stored Procedure

Jun 4, 2004

I tried to create a stored procedure but instead of opening up to a new stored procedure it displays an exist stored procedure. I erased the code and typed in my code now i received this error message.

MS SQL-DMO (ODBC SQLState:42000)
Error 2729: Procedure ‘spUpdate_date_time’ group number 1 already exists
in the database. Choose another procedure name


Does anyone know how I can fix this?

Your help is appreciated

View 2 Replies View Related

How Do You Create A Database Within A Stored Procedure

Apr 20, 2004

I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

select @iStatus = 0
select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


create database @dbname
on
( name = @dbname + 'data',
filename = 'F:MSSQLData' + @dbname + 'data.mdf',
size = 10mb,
filegrowth = 10% )
log on
( name = @dbname + 'log',
filename = 'F:MSSQLData' + @dbname + 'log.ldf',
size = 5mb,
filegrowth = 10% )

select @iStatus = @@error
if @iStatus = 0
print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
else
print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

end

View 3 Replies View Related

T-SQL (SS2K8) :: How To Create Stored Procedure

Oct 16, 2014

yesterday i was trying to create Stored procedure but it fails i don't know why

CREATE proc GetBooksbyBorrowerID @Borrower_id INT
AS
BEGIN
SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b
WHERE a.borrower_id=@Borrower_id
AND a.ISBN = b.ISBN
END
GO
EXEC SP_Task1 10001

View 9 Replies View Related

Permissions To Create A Stored Procedure

Apr 30, 2008

A simple one (not for me)

MS SQL Server 2005

Which permissions do I need to have (as a User) to create a Stored Procedure
Which other entity's properties do I need to change in order to create a Stored Procedure

Thanks

View 2 Replies View Related

Need Help To Create A View/Stored Procedure

Jun 24, 2008

Experts

I am trying to create a view or Stored Procedure between different table

Table1 consist of the follwing Fields:

Ref_No: String hold the reference number, Unique
Details: String

Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique

What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers

Any advice?

View 4 Replies View Related

Create View Within A Stored Procedure

May 21, 2007

hi,

Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go

go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?

thank you

View 20 Replies View Related

How To Create A Dbf File From A Stored Procedure?

Jan 28, 2008

Hi there,

Does anyone know any possible way of creating a dbf file from within a stored procedure?

Any guidence, articles, examples, topics to revice would be a great help.

Thanks guys
Butterfly82

View 6 Replies View Related

How Do I Create A Stored Procedure In SQL Server05

Feb 8, 2008

Yes it looks like a stupid question
but when i right click stored procedures
and click new stored procedure, it gives me a
QRY analyzer style window and all i can do is save
the qry as a regular .qry file ?

View 5 Replies View Related

Stored Procedure To Create New Database

Jul 20, 2005

Is there a stored procedure installed by sql server 2000 that I cancall and just pass in the name of a new database and have it createthe database for me? If not, how do I do it in sql? Thanks.

View 3 Replies View Related

How To Create Rdl Using Stored Procedure Which Uses Parameter

Apr 3, 2008

Hi
I am sql server 2005.
how to create rdl using stored procedure which uses parameter

Thank you.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved