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


ADVERTISEMENT

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

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

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

SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions

May 12, 2015

Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data

View 1 Replies View Related

Permissions On Views And Tables

Aug 12, 2005

I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:

View 2 Replies View Related

Views Security And Permissions

May 1, 2008

I have a database with two views in it.

As it is right now i have two sql logins one for readers and one for modifiers. I need to open the database up to windows auth.

I want users to only be able to see views and not the tables. I also want users to be able to modify the table if they are in one view but not in the other views. How would i do this? I have limited experience with permissions like this.

View 3 Replies View Related

Permissions With Views And Tables

Jul 23, 2005

We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!

View 5 Replies View Related

Permissions For Creating Views

Jun 6, 2007

In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema.

Two questions:

1) Can I set it up to where she can save a view to a schema which she is the owner?

2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures?

View 4 Replies View Related

Permissions To View INFORMATION_SCHEAM Views

Aug 9, 2002

SQL7, sp3

What specific permissions do you need to be able to view information_schema views? I thought public role had permissions to select on these views, but this is not the case? What do I do?

my developers have db_reader, db_writer, and db_ddladmin. They do not have db_owner. If I make them Sysadmin in sql they can view them, but that doens't fit in our security setup we have. THoughts?

Thanks,

View 2 Replies View Related

Deny User Permissions To See System Views?

Jun 4, 2008

Hi all, Is there any easy way to not allow a user to see system views?  I have set up 1 view for a login, and I have to use an ODBC connection to access it.  However, the 3rd party application I'm using is apparently timing out because of the number of tables/views that are returned. TIA! - Mark 

View 2 Replies View Related

SQL Security :: Permissions Disappearing From Tables And Views

Feb 26, 2014

As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will

Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions

This allows the report in CRM to access the database and this works.

The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.

I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.

View 6 Replies View Related

Views Vs. Standard Procedures

Jan 31, 2007

Hello,
I have a question where the answer may be simply opinion, or there may be strong reasons.  I guess that's why I'm asking the question... 
The question is - when should one create a view, and when should one use a stored procedure?  Obviously, when doing multiple queries or passing parameters, you must use a SP.  I'm talking about the case of a single query perhaps joins a couple of tables.
 
Hope that's not a stupid question...
 
Thanks in advance,
Paul

View 4 Replies View Related

Using Views Within Stored Procedures

Jul 22, 2007

Good evening:Can anyone give me the low-down on using view from within my stored procedures?  I've got some rather complex views that join multiple tables resulting in a staggering SQL query.  I then use stored procedures to accept input parameters to determine order by and direction clauses.  This results in some rather large "IF ELSE IF ELSE IF..." statements in my SP.  Put a complicated SQL query together with lots of IF THEN and it becomes difficult to keep stuff straight in the SP.  Therefore, using the view to keep my SQL in one place is an ideal solution. However, and here's the question, does it have a negative impact on speed and/or scalability.  We plan to use these SP's within an ASP.NET website that may get a very large amount of traffic. Any advice/help you can give me would be really appreciated. Thanks!
- Future Daddy
 

View 2 Replies View Related

Getting At Views And Stored Procedures

Feb 15, 2001

I have been having troubling getting at views and stored proceduers with ADO objects while developing in VB.

I can get a list of the views and stored procedures, but I don't know how to actually get the text values of them... i would like the text so that I can parse/compare them agianst one another...

Any thoughts or help on this?

I would really appreciate the help!

Matt

View 2 Replies View Related

Views In Stored Procedures?

Feb 16, 2007

Hello,

This maybe a simple question, I'm using reporting services to make a report, question is can I write a stored procedure that queries from a join of two views instead of two tables?

I'm having trouble getting my table data into the reportable state I want it, and I think organising into views might be easier.

Thanks for any help.

View 3 Replies View Related

Views Inside Stored Procedures.

Apr 18, 2007

Hi everybody,I am trying to alter a view inside a Stored Procedure and sql server is not allowing me to do so.Can we not have an alter view inside a Stored Procedure?Here is the code: CREATE PROCEDURE dbo.[UPDATE_VIEW_LINEITEMALLOTMENT]     -- Add the parameters for the stored procedure here@MAINID int,@BE VARCHAR(8)ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    --SET NOCOUNT ON;        ALTER VIEW [Budget_Management_System].[dbo].[LineItem_OCAFund]        AS        SELECT     TOP (100) PERCENT SUM(dbo.ALL_OCAFUND.AMOUNT) AS Expr2, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC,                               dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_LINEITEM.MAINID        FROM         dbo.APP_LINEITEM INNER JOIN                              dbo.ALL_ORG ON dbo.APP_LINEITEM.ROWID = dbo.ALL_ORG.LINEITEMID INNER JOIN                              dbo.ALL_OCAFUND ON dbo.ALL_ORG.ROWID = dbo.ALL_OCAFUND.ORGID INNER JOIN                              dbo.APP_AMENDMENT ON dbo.ALL_ORG.AMENDMENTID = dbo.APP_AMENDMENT.ROWID AND                               dbo.ALL_OCAFUND.AMENDMENTID = dbo.APP_AMENDMENT.ROWID        GROUP BY dbo.APP_LINEITEM.LINEORDER, dbo.APP_LINEITEM.BE, dbo.APP_LINEITEM.MAINID, dbo.APP_LINEITEM.LINENUM, dbo.APP_LINEITEM.PC,                               dbo.APP_LINEITEM.CATEGORY, dbo.APP_LINEITEM.ROWID, dbo.APP_AMENDMENT.AMENDMENTORDER        HAVING      (dbo.APP_LINEITEM.BE = @BE) AND (dbo.APP_LINEITEM.MAINID = @MAINID) AND (dbo.APP_AMENDMENT.AMENDMENTORDER = 1)        ORDER BY dbo.APP_LINEITEM.LINEORDER   ENDthanks a lot,Murthy here 

View 7 Replies View Related

Accessing Views From Stored Procedures

Sep 7, 2007

Hi,I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?Thanks. 

View 4 Replies View Related

Is There Any Point In Views? Vs Stored Procedures?

Nov 8, 2007

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

View 12 Replies View Related

Scripting Views && Stored Procedures

Oct 9, 2002

Is there a way to script views and/or stored procedures from Query Analyzer? I am hoping to do this as part of a stored procedure.

Thanks!

View 3 Replies View Related

Transfer Stored Procedures And Views Only

Oct 19, 2001

I have two databases named A and B. They are exactly same table structure, but the records are different, and we have near 400 stored procedures and over100 views in the database A. Now my question is that how can I transfer all stored procedures and views from database A to database B?

Thank you very much!

View 2 Replies View Related

Views / Stored Procedures / Functions

Dec 1, 2005

Hi All,

Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.

View 7 Replies View Related

Queries, Views, Procedures : Best Practice

Nov 23, 2007

Hello all,

I'm looking for a best practice.
Let's say you have a report that contains out of 3 queries.

How are you going to create the report and why?

1. Just use "select * from table where p1= value" in your report
2. Save the query in your db as a view and use the view in your report
3. Create a procedure that contains all 3 queries. And use the procedure with some parameters in your report?

Kr
Karel.

View 5 Replies View Related

Question Regarding Stored Procedures, Views And ASP

Jul 20, 2005

Please could someone explain to me the differences between a storedprocedure and a view.The reason for this question is I have two almost identical ASP pages.Both get the same results but one uses a stored procedure and one usesa view. If the query returns no results the 'view page' generateserrors and therefore I have to check for BOF and EOF, whereas the'stored procedure page' does not generate errors and instead wouldappear to return a recordset with 0 entries.Thanks in advanceNeil.

View 1 Replies View Related

Can't Import Views And Store Procedures

Aug 6, 2007

I recently set up a new sql 2005 standard edition and planning to mirgrate our production sql 2000 data. but only the tables were migrated from the copy data or import / export task. I cannot find a way to recreate the store procedures and views on the new server without create one view and one store procedure at a time unless all hundreds of views and procedures were rescripted. can anyone help

Thank

Andy Wong
awong@virginiadare.com

View 3 Replies View Related

Diffrence Between Views And Stored Procedures

Jun 18, 2007

Hi

what is the diffrence between views and stored procedures in sqlserver 2005

thanks in advance.

View 3 Replies View Related

Create Views In Stored Procedures?

Sep 25, 2007

Can you create views within a stored procedure?

View 10 Replies View Related

MS Access And Stored Procedures Or Views

Mar 13, 2008

Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error?
It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access.
Thanks in advance - I hope.

View 9 Replies View Related

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

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 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







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