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
ADVERTISEMENT
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
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
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
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
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
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
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
Nov 27, 2000
Does anyone know how to create a view from another database (ie I want to create a view in DB1 from master)
View 1 Replies
View Related
Nov 14, 2001
help,
Is it OK to create new Views while production database is live?
rob
View 2 Replies
View Related
Jun 9, 2004
Hi all,
Bit of a SQL server newbie here, I have two tables; Fish (containing Name and ID) and Fishing (containing just a concatonatted FishingID), but to relate the tables i would have to join (reference) the fields in the Fish table ie: fishname and FishID to match the other tables FishingID.
Every time i concanonate the fishname and FishID in the criteria the view doesent run but if i actually write an example FishingID in the criteria the view runs.
How do i refernce the fields in the criteria section?
Jnr
View 5 Replies
View Related
Dec 8, 2004
Hai Guys,
Can i create a view by joining 2 tables that r on two different database.
Good Day.
Thanks.
Shabu
View 7 Replies
View Related
Sep 20, 2001
I am not sure if this can be done I have never tried to do this before. What I am looking to do is to create a view in a database to select information from another table in a different database. Is this possiable?
Thanks in advance,
Anita
View 1 Replies
View Related
Jun 17, 2008
can anyone tell me why when I create a view if I do this in my select statement "CAST(CrewNo AS char(10)) AS CrewNo" and then save the view the above CAST statement will be changed to this "CAST(CrewNo AS char(10)) AS Expr1". After I have it saved and then go back and change it back to "CAST(CrewNo AS char(10)) AS CrewNo" it will stay that way. Just looking for a reason why this is happening as I don't know if I'm doing something wrong or not.
View 2 Replies
View Related
Jul 23, 2005
Hi NG,I'm trying to run the following script as part of a larger scriptfileusing isql:USE MYDBGOCREATE VIEW AGENCYSEARCH_mwbASSELECTId,SaveId,Checked,SessionId,substring(convert(char(26),InsertedDate,109),1,20) +substring(convert(char(26),InsertedDate,109),25,26 )"InsertedDate",Name,key,sortorder,initnameFROM MYDB.dbo.AGENCYSEARCHGOThis results in an error:Msg 156, Level 15, State 1, Server Myserver, ProcudereAGENCYSEARCH_mwbIncorrect syntax near keyword 'key'OK, so my guess is that 'key' is a reserved word and cannot be used inthis context. But the column in this table is named 'key' so how can Icreate my view?Probably a basic problem for all the cracks over here but I'm just asimple Oracle DBA :-))))t.i.a.
View 1 Replies
View Related
Jul 20, 2005
Recently we upgraded our server from SQL 7.0 to SQL 2000. I created anew view in Enterprise Manager which pulls data from a linked server'sdatabase. When I write the view...CREATE VIEW dbo.VIEW1ASSELECT *FROM LinkedServerName.DatabaseName.dbo.TableNameand I click on the run button it changes to:CREATE VIEW dbo.VIEW1ASSELECT *From LinkedServerName.DatabaseName.dbo.TableName TableName_1I can still see the correct results, but the users can't- Once Iremove the TableName_1 they can see the results.When I tried creating a view and did not use a linked server,everything worked fine. Anyone have any answers?
View 1 Replies
View Related
Nov 20, 2006
Cordial greetings,
Again i need help with a couple of issues in migrating from Oracle to SQL Server 2005. I need the equivalent sentence in SQL Server of the following sentence:
CREATE OR REPLACE VIEW IBA_MPDATOSGENERALES AS (
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N'
AND IBA_MPACCIONESXREQ.CERRADAAXR = 'N'
AND NOT IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL
UNION
SELECT IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION, IBA_MPREQUERIMIENTOS.APLAZADOREQ, IBA_MPACCIONESXREQ.FECHAFINALAXR, IBA_USUARIOS.NOMBREUSU
FROM IBA_MPPROCEDXLOC, IBA_MPREQUERIMIENTOS, IBA_MPACCIONESXREQ, IBA_USUARIOS
WHERE IBA_MPPROCEDXLOC.IDPROCEDENCIALOCALIZACION = IBA_MPREQUERIMIENTOS.IDPROCEDENCIALOCALIZACION
AND IBA_MPREQUERIMIENTOS.IDREQUERIMIENTO = IBA_MPACCIONESXREQ.IDREQUERIMIENTO(+)
AND IBA_MPACCIONESXREQ.USERNAME = IBA_USUARIOS.USERNAME
AND (IBA_MPACCIONESXREQ.CERRADAAXR = 'S'
OR (IBA_MPACCIONESXREQ.CERRADAAXR = 'N' AND ((IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'N' AND IBA_MPACCIONESXREQ.FECHAPROXEJECUCIONAXR IS NULL) OR (IBA_MPACCIONESXREQ.TIPOACCIONAXR = 'S' AND IBA_MPACCIONESXREQ.FECHAFINALAXR IS NULL))))
)
I know that (+) is for outer joins (left or right). I also know that UNION may mean FULL OUTER JOIN. but this query? The real query is a lot more complex, well actually there are just more fields needed from more tables but with this exmple is enough. The bottom line is that i need data from various tables, most of them involved in a JOIN clause. Also check out that in SQL Server doesnt exists the REPLACE word or does it?
Thank you in advance,
Fernando Martinez
View 5 Replies
View Related
Aug 17, 2004
I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.
Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.
I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.
I'm not sure why this is as I am logged in as sa.
Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.
Can anyone help?
Cheers.
View 1 Replies
View Related
Aug 6, 2007
Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
Can anyone out there help me?
View 3 Replies
View Related
Nov 13, 2007
Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
View 3 Replies
View Related
Aug 9, 2007
I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON
The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:
EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)
Below is my select command:
------
SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION
FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT
GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION
ORDER BY
dbo.vw_SEASON06.EVENT
-----
This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.
How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?
Any help is appreciated!
View 7 Replies
View Related
Nov 30, 2007
Hi,
I have problem that Im sure others must have had before so I am looking for advice on the best way to solve it. I have a table of text information tbl_base which is related to another table containing tags, which can contain several rows for each row in base.
I want to create an indexed view of the data for full text search. I would like to select all the tags related to a particular row in the tbl_base together into a string and join it to the end of the tbl_base table so they can be indexed along with the tbl_base data for full text indexing. Ive tried several methods but I am never able to make an index on my view because it say I cant use COALESCE, or Cursors.
Sample data here :
tbl_base
id | Text
------------------------
1 | BLah blah
2 | Dum de dum
3 | HAr HAr
tbl_base_tags
tagID | base_id | TagText
--------------------------
1 | 1 | first
2 | 1 | second
3 | 1 | third
4 | 2 | fourth
The view I want to end up with will look like this :
vw_tables
base_id | Text | Tags
-------------------------------------
1 | Blah blah | first second third
2 | dum de dum | fourth
How can I achieve this? Do I have to index all the tables seperately?
View 1 Replies
View Related
Jul 20, 2005
Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!
View 1 Replies
View Related
Mar 24, 2015
I have existing domain users with SQL logins and having different set of permissions, we are migrating to new domain, and we have new user ID's with new domain. Is there any way through query I can create an user and copy the permissions.
View 0 Replies
View Related
Jun 7, 2007
I am trying to create a linked server in the management studio and am getting an error
"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"
I have tried giving the user rights via
GRANT ALTER ANY LINKED SERVER TO [DOMuser]
as well as adding them to the setupadmin group. No luck.
I can add the linked server via sp_addlinkedserver as the user.
Any ideas?
View 1 Replies
View Related
Nov 13, 2006
We deploy our app via ClickOnce. Rather than shipping the .mdf and .ldf, the app detects whether the database is there and creates it via script if it's not. The script was originally generated from SQL Exrpess Mangement Console, etc.
In the past, we've made the stipulation that the user must have administrator access to the machine. However, we now need to find a workable solution for users who are not administrators on their machine. Currently, we use the following connection string before attempting to run the db creation script:
conn.ConnectionString = "Server=.\sqlexpress;Integrated Security = true;User Instance=false";
The script fails when trying to execute "CREATE DATABASE [myDatabaseName] ON PRIMARY "
with the error:
"CREATE DATABASE permission denied in database 'Master'"
How can we get around this permissions issue in the most automated way possible?
TIA!
View 1 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Aug 2, 2006
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 Replies
View Related
