.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Deny Drop Table Permission to Role

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 
Hi Experts,

                  As i have created one database role and that role has defined the following permission.

                 

GRANT

EXECUTE TO db_executor

DENY

CREATE TABLE TO db_executor

GRANT

SELECT TO db_executor

GRANT

INSERT TO db_executor




View Complete Post


More Related Resource Links

Deny DBO permission to a table

  
Hello

I've been asked to do a very interesting thing. My HR department is having a database-app created for thier use. They DO NOT want SQL Administrators to have any access to this database.

How do I keep S.A. out of the database and still allow them to do admin functionality on the SQL server?

Or

how do I deny DBO access to user tables?



Thanks in advance
Eric_Romi

Data Points: Deny Table Access to the Entity Framework Without Causing a Mutiny

  

Julie Lerman shows database administrators how to limit access to databases from the Entity Framework by allowing it to work only with views and stored procedures instead of tables-without impacting application code or alienating developers.

Julie Lerman

MSDN Magazine August 2010


Restored copy of DB gives bizarre DENY permission to a dbo user

  
We took a backup of our PROD database and restored it in our Integration testing environment, and ran a script to alter the schema from the current PROD schema to the new schema we wanted to test in Integration testing. This same script has worked fine in two other environments and exhibited no issues. However, on our SQL cluster in the Integration testing environment, the script (which is hundreds of lines long and performs numerous things that require you to be dbo to do) fails on one line, a basic UPDATE statement on a single table.  The table has no deny permissions, and only has explicit grant permissions for the Application Role that accesses it.  Other users who are dbo can UPDATE the table.  I can UPDATE all other tables and do everything else a dbo should be able to do.  On every other SQL instance we have tried this on, it works. For some reason, this error only occurs specifically on this one instance of SQL, with my specific user, and this specific table.  I am baffled as to what could be going on, but need to get this repaired to bring our Integration testing environment back on line. What could be causing this?The Pragmatic TSQL Programmer

application role how query a view whose reference table is in another db?

  
Hi everybody. I created  an application role in a database (DB1) and gave it all the rights on a view  in DB1 which refers to a table located in another db (DB2).  I also gave the rights to the app role on a table of  DB1 I tried to use this app. role through the sp_setapprole launched by a user  (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:   The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated.   Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania

Excel Drop Table question

  
Can anyone tell me if the drop table syntax should delete only the data in a worksheet or clear the formatting as well. If not is there a way to clear all the formatting. Thanks

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Modify a table name without drop it and recreate it using T SQL

  

Hi

How to modify a table name without drop it and recreate it using T SQL. Suppose the SSMS is not installed or reachable.

Thank you

 


The complexity resides in the simplicity

how can i drag and drop fields of table in teh desing surface

  

I am followint the insructions of this walkthrough of VS2003

http://msdn.microsoft.com/es-es/library/1as0t7ff(en-us,VS.71).aspx

adn in a part say this


  •  8   Find the authors node and expand it to show the fields in the authors table.
  •  9   Using CTRL+Click, select the au_id, au_lname, au_fname, and city fields.
  •  10  Drag these fields from Server Explorer onto the design surface
  • Now i am using VS2008 

    When i try to drag and drop i can not made it is maybe because of the differences between 2003 and 2008 versions

    how can do it in VS2008?

    Luis 


    Drop tables where table name is a parameter

      

    I'm trying to drop some tables and I created a curser to do so.

    The table name is a parameter.

    I get an error message when I run this code in the curser.

    Is there any way to run this code in a different way.

    I'm trying to drop databases as well where database name is a paramerter, can I do so?

     

     

    Declare

     

    @DatabaseName varchar(30)

    Declare

     

    @FullDatabaseName

    Difference between truncate and drop table on database fragmentation

      

    I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing.  My question is if there is any difference between the effect of dropping a table and truncating the table on database fragmentatiom.

     

    Thanks

    Pam 


    cascading drop down lists from single table

      

    my one table consists of fields (id, flying from, flying to). thus i have one drop down for flying from and another drop down for flying to. i'm hoping to cascade them so 2nd drop down values are dependant on the values from 1st drop down. is it possible to do this using a single table? or must i use two tables and link the id's from both? and do i write anyting in the SelectedIndexChange event? thanks...

    the sqldatasource code looks like this:

    Flying From:

            <asp:DropDownList ID="ddlFlyingFrom" runat="server" DataSourceID="SqlDataSource1"
                DataTextField="FlyingFrom" DataValueField="Id" AutoPostBack="True" OnSelectedIndexChanged="ddlFlyingFrom_SelectedIndexChanged">
            </asp:DropDownList>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
                SelectCommand="SELECT [Id], [FlyingFrom] FROM [Flights]"></asp:SqlDataSource>
          &nb

    Getting the values of drop down repeating table in info path

      
    HI how can i get all the values of a drop down repeating table, using XPathNavigator please reply me!

    Does dropping a table automatically drop its contraints and indexes?

      
    When I have a table that has foreign key constraints and/or index and generate a delete script with Management Studio, the script explicitly drops all constraints/indexes before dropping the table.  Is this necessary?  Or does dropping a table automatically "cascade delete" all that table's constraints, indexes, etc...?

    Drag and drop field from db into HTML table cell possible?

      

    Hi

    I'm used to Dreamweaver where this is possible. I find it hard to believe that I would need to use a 1 column gridView control.

    Is there a way to drag one field from the db Explorer window onto my page and NOT get a grid view?

    Or alternatively, I used to be able to put something like this in ASP classic (years ago!) <%= Eval("User") %>

    Any help would be SO appreciated.

    Thanks



    How do you Drop Table in C# code behind?

      

    I have a stored procedure that builds a table.  I need to delete the table before running it or I get an object already exists error.  How do you code Drop Table in C# code behind.  I have am calling the SP with execute non-query statement.

    Thank you 


    How can I create link table in an Access DB for a group permission in SharePoint?

      

    Hello,

    I move all the tables to SharePoint site using "Move To SharePoint" button.  When it finished, access create additional link table called "User Information List"  However, I need a site owner permission list in my access table link(Not User Information List-it has too many list).

    Can anyone help me, please?

    Thanks


    Categories: 
    ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
    Sql Server  SharePoint  Silverlight  Others  All   

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend