SQL Security :: View Definition Permission On Target Database
May 15, 2015
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 have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
What is the correct way to create a security group that allows the group members to Select (Read) the content of a database?
1. Create a security group in AD 2. Add the required members to the group 3. Add the security group as a login on the SQL server (Under Security>Logins) 4. Add the security group to the specific database with Grant in Connect and Select
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
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?
I was able to build and Preview the report, but ran into an issue when trying to deploy the report that I created. After reviewing various postings on this issue, I still can't seem to get past the issue.
Currently running SQL 2014 Developer Edition, SQL Server Data Tools for VS 2013, and O/S Windows 8.1 all on the laptop. I followed the video and left the default value of LocalHostReportServer. System said LocalhostReport Server could not be found.
I went to SQL Configuration Manager and tried using the Logon as the URL. Still received the same error.
I went to Reporting Services Configuration Manager and used the URL, still received the same error.Â
Under SSMS I see the "securityadmin" Server role. Where in SSMS can I find the actual list of permsisions granted to this role? (not just a generic description of what this role can do.)
We are trying to create a view that references lengths in both metres and feet.
What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.
We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.
I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".
I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".
Table: workorder has columns: woid, startdate (plus many others) Table: wo_comment has columns: woid, comment, sequence
Desired View: view_workorder has columns: woid, startdate, allcomments
I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?
create view [workorder_view] as ( Select w.woid, w.startdate, cast( DECLARE @all_comments nvarchar(4000), @curr_comment nvarchar(256) DECLARE wo_comment_cur CURSOR FOR SELECT woc.Comments FROM WO_Comment as woc WHERE woc.woid = w.woid OPEN wo_comment_cur FETCH NEXT FROM wo_comment_cur INTO @curr_comment WHILE (@@FETCH_STATUS = 0) BEGIN Set @all_comments = @all_comments + @curr_comment FETCH NEXT FROM wo_comment_cur INTO @curr_comment END CLOSE wo_comment_cur; DEALLOCATE wo_comment_cur; as nvarchar(80000)) as COMMENTS
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only, 2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login. 3.If we Deny the View definition option he can't able to see the Objects which are there under the DB. 4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2 Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
SELECT [DEFINITION] FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)
I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.
i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'
below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ... View definition includes no output columns or no items in the FROM clause. A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.
query:
select Case_CaseId, Logged, CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting, CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating, CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected, CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved, CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved, CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed from (
SELECT Case_CaseId, MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged, MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting, MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,
AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved, MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved, MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed FROM CaseProgress GROUP BY Case_CaseId ) as temp order by Case_CaseId
Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns
CREATE View hrinu.Parity as select T1.Matcle as CorpID, T2.Nmpres as Name, T4.DATDEB as LeaveFrom, T4.TEMDEB as PM, T4.DATFIN as LeaveTo, T4.TEMFIN as AM, T10.LIBLON as LeaveType, T8.LIBLON as Location, T12.LIBLON as ParentOrg
from HRINU.zy00 T1, HRINU.zy3y T2, HRINU.zy39 T3, HRINU.zyag T4, HRINU.zy38 T5, HRINU.zy1s T6, HRINU.zd00 T7, HRINU.zd01 T8, HRINU.zd00 T9, HRINU.zd01 T10, HRINU.zd00 T11, HRINU.zd01 T12 where T4.Nudoss = T3.nudoss and T4.Nudoss = T1.Nudoss and T1.Nudoss = T2.nudoss and T3.nudoss = T5.nudoss and T6.nudoss = T1.nudoss AND T7.NUDOSS = T8.NUDOSS AND T9.NUDOSS = T10.NUDOSS AND T11.NUDOSS = T12.NUDOSS AND T3.IDWKLO = T7.CDCODE AND T4.MOTIFA = T9.CDCODE AND T5.IDESTA = T11.CDCODE and T6.stempl = 'A' and t7.cdstco = 'z04' AND T8.CDLANG = 'U' and t9.cdstco = 'DSJ' AND T10.CDLANG= 'U' and t11.cdstco= 'DRE' AND T12.CDLANG= 'U' and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00 and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00 and T6.dtef1s <= getdate() and T6.datxxx > getdate()
Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables. Also throw some light on how to analyse the execution plan for further enhancements.
Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
Hi Every body, I have newly joined this group. I am new to DB administration. I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this. Regards, Krishna
I have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
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?
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
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.