Stored Procedures And Permissions

Feb 14, 2007

i have a stored procedure that calls another stored procedure which does the following:

exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0
begin
/* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */
return
end

exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false

now if I execute the first sp in management studio it doesn't error but if I call from asp it does error.
I gave the user permission to the first sp -- so I need to give permi

View 1 Replies


ADVERTISEMENT

Stored Procedures Permissions

Aug 1, 2007

Good evening:
When assigning permissions to logins/roles, etc., does a login/user with rights to a stored procedure need rights to all of the tables and views that it accesses?  
In other words: 
If you create a login/user with rights to 3 stored procs, but deny access to the same user to the tables and/or views that the SP uses, will the sproc still run?
Stupid question?  Sorry if it is.
** Future Daddy

View 11 Replies View Related

Permissions On Stored Procedures

Jun 12, 2001

Is there any way by which I can grant the same permissions to all of my stored procedures by one command?

Thanks,
Adita

View 2 Replies View Related

Permissions On Stored Procedures

Jul 3, 2001

Till yesterday I was able to execute the stored procedure sp_cycle_errorlog.
I am member of Domain Admin on NT.
I am member of sysadmin server roles on SQL Server 7.0 (SP1).
Now I receive the following error:

Server: Msg 15003, Level 16, State 1, Line 0
Only members of the sysadmin role can execute this stored procedure.

I have already stop and restart SQL Server.
Any idea?
Thank you.

View 1 Replies View Related

Permissions On Stored Procedures

May 17, 2006

Using Server Management
Studio Express and SQL Server 2005 Express - is it possible to assign
Exec permissions for users on a sproc by sproc basis. If so, how do I
do this?

TIA

--
Mike Brind

View 1 Replies View Related

CLR Stored Procedures Table Permissions

Jul 4, 2013

I have an app which calls a SP, which in turn calls a CLR Stored Procedure.

The CLR stored procedure calls a number of different tables, using a Context connection string.

The issue is that the CLR SP requires the user to have permissions to the tables directly, instead of just permissions to the SP which was expected.

If I just give permission to the SP, then the CLR SP fails. So I then add the table permissions, and it then works.

So the question is, how do I raise security so the app does not have permissions on the tables?

View 5 Replies View Related

Permissions Of CLR Stored Procedures To DB Objects

Jan 25, 2008

I need to understand the permissions that a CLR stored procedure needs when it accesses tables. In a dbo TSQL stored procedure it has owner permissions on all dbo tables, so there is no need to grant permissions on tables to the database user.

Some developers recently implemented a CLR stored procedure that returned an error with update permission denied on table name. Once I granted the user account update permission on that table, it was able to execute OK.

I have been looking for a good explanation for the way the permissions to database objects need to be setup for CLR stored procedures. For example, could I have said to modify the procedure to use the EXECUTE AS clause, instead of granting the user account direct permission on the table? Does anyone have any links to good articles on this subject?


Edit:
I have a feeling I'm on my own trying to figure out how this works. I've been searching the web for hours, and I haven't found anything that directly addresses this.


CODO ERGO SUM

View 5 Replies View Related

Stored Procedures/ Table Permissions

Jul 20, 2005

Hi, is there any way that I can automate granting user permissions totables/ stored procedures in SQL server 2000?I have a whole bunch of tables and rather than having to right click eachtable/ then permissions in Enterprise manager I would like to be able toiterate through each table object in a database and grant the relevantpermissions.... Same with stored procedures.Is this possible?? If so, how can I do itThanks in advanceMark

View 2 Replies View Related

Execute Permissions On Stored Procedures

Apr 24, 2007

HI,

would like to know how to give execute permissions for all the stored procedures in a database at one shot. please advise.

View 6 Replies View Related

Permissions For Stored Procedures Generated By VS 2005

Apr 25, 2005

I have a DataSet (Data Component in Beta 1) and I want to add Fill and
Get methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.

View 1 Replies View Related

Adding Execute Permissions On Stored Procedures

Jul 23, 2005

How can i add Execute permissions on the Stored Procedures under thecreated user permission iusing SQLDMO ?

View 1 Replies View Related

How Do I Give EXECUTE Permissions On Stored Procedures?

Sep 20, 2007

Hey guys,I'm pretty new to SQL configuration, and I need to give EXECUTEpersmissions for one of the SQL user roles. I am running SQL 2005Management Studio Express - free version. I found the list of mystored procedures, but I can not locate any permissions screen. Cansomeone help point me in the right direction? Thanks!

View 7 Replies View Related

Inter-database Stored Procedures And Permissions

Jul 20, 2005

Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ

View 4 Replies View Related

SSIS / MSDB Stored Procedures / Permissions

Jul 23, 2007

All,

Here is my problem, its very simple, But I dont have a solution.
To run / import / what ever I else I forgot (?) SSIS in SQL SERVER, what are permissions I'll need.

So far I have developed everything in BIDS, when I try to migrate it to a sql server by using Import package in Integration Services I got the below error.

TITLE: Import Package
------------------------------
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)

------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_dts_listpackages', database 'msdb', schema 'dbo'. (Microsoft SQL Native Client)

The error is very clear in itself, While I have raised a request for the execute permission of this stored procedure, i also like to know what kind of permissions I will need in MSDB to work with out any problems. So that I dont have to go to DBA for execute permission for each error I may get for this.Right now I dont have execute permission on any of the Stored Procs in MSDB.

If any body can show any pointers that would be help full.

View 4 Replies View Related

MS SQL Server Management Studio - Permissions And Stored Procedures

Nov 16, 2006

Hi

My website uses GET variables a lot and i'm trying to safe guard as much as possible against SQL injection attacks. I'm trying to create permissions which will deny a user to Delete/Insert/Update various tables.

I have managed this with the tables themselves, but when using a stored procedure, the tables do not take into account the user permissions which were set for that table!

Basically, how do i stop a stored procedure from Deleting/Inserting/Updating tables? :(

many thanks

View 3 Replies View Related

SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures

Aug 19, 2015

We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).

View 17 Replies View Related

SQL Server Admin 2014 :: Permissions To Debug Stored Procedures Using SSMS?

Jun 25, 2015

What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.

EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.

EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.

Is there any option other than giving sysadmin privilege on SQL?

View 0 Replies View Related

Is It Possible To Change Permissions Of SQL Server System Stored Procedures And DBCC Commands?

May 8, 2008

Hi all,

I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).

Is it possible to change permissions of SQL Server system stored procedures?

Is it possible to change permissions of SQL Server DBCC commands?

Thanks,

Assaf

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Permissions To Change Table, Views And Procedures

May 20, 2008

Hi

What permissions do I need to set so that a user can change tables, views and procedures?

View 7 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

SQL Server 2012 :: Permissions For Executing Procedure Which Executes Other Procedures?

May 13, 2015

I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.

I want to run this top-level procedure using an account which can't do anything else.

Is there a simple way to give it all the permissions it needs without empowering it to do anything else?

View 0 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Stored Procedure Permissions

Oct 6, 1999

I have revoked an update stored procedure permission to a SQL user. The first time the user tries to execute the stored procedure he gets the error "Execute permission denied." But if he attempts it a second time the stored procedure will execute with success. I want to deny the user EXEC on the stored procedure forever. Does anybody have any advice to make this happen with success? Thanks.

View 1 Replies View Related

Stored Procedure Permissions?

Jun 7, 2002

I have created a Stored Procedure that will not insert into a particular table.
Yet, when I run the same code in Query Analyzer it runs as it should and completes the Insert.

I have tried to both recreate the SP and searched for authorization issues, with no luck.

Any suggestions?

View 1 Replies View Related

Stored Procedure Permissions

Jun 12, 2008

What role does a user have to be in to execute a stored procedure?

I am trying to run an exec statement from a web app and am getting "The EXECUTE permission was denied".

Thanks.

View 3 Replies View Related

Stored Proc Permissions

Jan 28, 2008



I have a Active Directory group (under Organizational Groups) and I have a SQLServer group which contains that Active Directory group. In the group under Organizational Groups there are users. I've tried granting the SQLServer group execute permissions to a stored proc I developed and they are not able to execute it. However, when I extract a single user, grant them ability to get to the database and grant that single user execute permissions to the SP, she is able to run the SP.

My question is why isn't the SQLServer group, which contains the Organizational Group who this user is a member of able to execute the SP?


Thanks,
Phil

View 5 Replies View Related







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