SQL 2012 :: DENY Permission On Column Subsequently Reference In View?
Aug 6, 2014
Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user
CREATE USER TestDenyOnViewUser WITHOUT LOGIN
GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply)
CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
I create a new user who will have a read only permission on TestDB.
I want to give only select permission on TestDB and also I don't want that the new user will not see any other database.
DENY VIEW ANY DATABASE to user_readonly
ALTER AUTHORIZATION ON DATABASE :: TestDB TO user_readonly
but when I am using the above query then the new user is the owner of the testdb. i don't want that. I want that the user will have only select permission on the table.is there any way?
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
Any one can help me, below error messages for reference, thanks! Exception Details: System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.Source Error:
Line 96: cmdMid.Connection = conMid; Line 97: cmdMid.CommandText = "exec sp_insertspend '" + uid + "','" + Mid + "','" + status + "','" + spend + "'"; Line 98: cmdMid.ExecuteNonQuery(); Line 99: conMid.Close(); Line 100:Source File: f:Microsoft Visual Studio 8WebSoccermain.aspx.cs Line: 98 Stack Trace:
Can any body tell me how can I restrict a user who has Sa previlages, from droping a table. He should be able to do everything except droping the table.
I would like to know if there is any way to restrict users from creating temp tables.
Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.
I would like to disable a user account from logging to the database. I would like to know the difference between deny connect to sql permission and disabling an account by alter login disable. Please advice. Thanks
I'm having trouble creating a read-only view. I've got 1 or more tables that I wish to remain updatable but I want to create a view that covers the table and/or spans all the tables. However, I want the view to be select only. I can't seem to get it to work.
DENY UPDATE ON [dbo].[MyView] TO [dbo] CASCADE
All that seems to execute my dbo user can still use...
Update MyView set SomeID = SomeID + 10
Plus, ideally I just want to say, DENY UPDATE ON VIEW TO ALL
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be useful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt.
a. I tried GRANT DELETE... - no luck b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
Can anyone recommend a good reference book on views, stored procedures (system stored procedures as well) and triggers? I am in need of assistance in these areas.
guys, ive never worked with Views before so forgive me. i know how to create one, and that it creates a virtual table in memory, but i've got one small question.
if i create a view:
CREATE view dbo.myView as select Distinct FirstName,LastName from SomeTable
When ever i reference that view, such as Select FirstName,LastName from myView where LastName like 'Jo%'
does that View Refresh itself??
in other words does it run each time i Reference it??? or is it static from when i created it.
Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?
I am trying to create a set of "Reporting" views and grant Select permission on those views instead of the tables. I created the view in the same database but under a different schema from the tables. When the user tries to select from the views they get an error saying they do not have select privilege on the tables. Do I need to grant them select on the underlying tables?
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
What is the best way to grant a user permission to create a view?I first created a role using enterprise manager but for the role Icreated it doesn't seem to offer that permission. It offers the basicstuff such as insert, select, and update.I could go in and use a grant create view sql statement I suppose butI'd rather do it through enterprise manager where it would be visibleif I need to change it in the future.-David
Hi, This is regarding the SQL 2005 Report Manager (http://localhost/Reports) page. I have posted the reports, the reports run fine, buttons like new folder, new data source, etc... are visible (lets call this machine as A). The problem is when my machine is accessed from a nearby computer on the same network as http://machineA/Reports, the Report manager Home page appears with limited or no buttons. The reports are also not visible in the browser of machine B. How do I make the reports visible to users (grant permission) for them to run and view those reports?
In a nutshell, how the posted reports be accessed by users?
I've been looking through permissions to see if it's possible to grant a user permission to see only certain records from a table.
For example:
I'm granting users to view records in the table 'Sales', but I only want the users to see their respective data.
User A should only be able to see Sales where Sales.Location = 1
User B should only be able to see Sales where Sales.Location = 2
... and so on.
I believe I've read this is possible, but in looking through the permissions you can define, I've only found where you can limit the columns a user sees, not records.
Anyone have any ideas or did I just remember something wrong?
On our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?
We have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database? I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
I gave a user all required permission to view the SSRS report. User is able to select from the dropdown list but unable to view the data, It is showing a blank screen.
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below
CREATE VIEW DB1.[dbo].[View1] AS -- For brevity, I made it as simple statement. SELECT * From DB2.dbo.table2 GO
For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.
1. Is my understanding correct?
2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can€™t access DB2.dbo.table2 directly?
3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio
I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
SELECTDISTINCTLOC.[place-ref] AS 'Place Ref' ,PLA.address1 AS 'Address1' ,PLA.address2 AS 'Address2' ,PLA.address3 AS 'Address3' ,PLA.address4 AS 'Address4' ,PLA.address5 AS 'Address5' ,PLA.[post-code] AS 'Postcode' ,LOC.[location-sts] AS 'Location Status'
[Code] ....
Above is a small example.
As you can see 13 Alamein Avenue is listed twice. This is because it pulls back the former and the current tenancy. I don't want to exclude former tenancies - so I want to pull back the most recent Tenancy Reference for each Place Reference.
I created a CLR procedure with a service reference in Visual Studio 2010 that is being used in SQL Server 2008 R2. We recently tried to deploy it to a 2012 SQL Server, but were unable to due to compatibility issues. When I tried to upgrade the package to Visual Studio 2013 I got this error message:
Unable to cast object of type 'Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode' to type 'Microsoft.VisualStudio.Data.Tools.Package.Project.Internal.FolderNode'
I tried to create this using the 2012 Data Tools, but was unable to add a service reference with the GUI. It appears Microsoft has removed this functionality. My company has a third party app that we have limited control over, which is why we need this functionality.
In database projects, VS2015, I want to create some views for me database that will reference another database table using 3 part naming reference.
Works fine in SSMS but when I try and build me project I is throwing up a reference error.
I can't import the other database into this project, so is there a way to suppress the error? I don't really want to exclude these view from the project.
