Sql Server 2005 Questions Regarding Editing Views
Jun 21, 2006
Previously in Sql Server 2000, we would be in enterprise manager, you'd
double click on a view, and a nice little dialog box opened with the t-sql
statetments, there was also a check sql syntax and apply and cancel buttons.
Not exactly query anaylizer, just a quick lightweight dialog box. Is this
feature still around? Seems like I have to go into the query anaylizer like
mode to edit a view now. I am a total newbie to version 2005. Are there any
options I can set to make it behave the old way? All feedback is
appreciated.
TIA,
~CK
View 2 Replies
ADVERTISEMENT
Feb 29, 2008
Please direct me towards good documentation about updating, inserting and deleting data using views involving multiple tables.
Thanks
View 2 Replies
View Related
Apr 25, 2007
I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?
Any help will be greatly appreciated.
thulo
View 3 Replies
View Related
Aug 21, 2007
Hello,
We are currently running a corporate client with Windows 2000 and .Net 1.1. We are running a number of SQL Server 2000 applications and are now thinking of upgrading to SQL Server 2005 as part of a data consolidation exercise. I am concerned on a number of points:
Can I connect to SQL Server 2005 using old ADO connectors? We have about 40 Excel VBA solutions, and we dont want to upgrade to SQL Server 2005 if we will be unable to connect to the data source. We cannot upgrade any new versions of MDAC or upgrade the .NET framework so this is a concern.
Do we need .NET 2.0 or Visual Studio 2005 to connect and work with SQL Server 2005? If so, this will be a problem as we cannot upgrade any client beyond .NET 1.1, and only have VS 2003 as a scripted application we can install for any development.
Has anyone have any experience of the KPI capabilities of SQL 2005? We are bordering on committing to a Business Objects BI platform, and having worked with BO Dashboard Manager and Performance Manager for 4 months (it was horrible), I am not relishing the prospect and would like to propose SQL 2005 as an alternative.
Many, many thanks
Indy
View 4 Replies
View Related
Apr 4, 2006
I recently upgraded to sql server 2005 for developing on my local system and cant seem to find the option that automatically sets the drop procedure at the top and the usernames on the bottom of a procedure that I script as new. I used to do it in the old query analyzer so Im sure its in there somewhere. Thanks in advance for any help.RyanOC
View 4 Replies
View Related
Jan 29, 2008
Hi pardon my ignorance but I wonder if someone could answer a few questions for me.
I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.
A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.
So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.
How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.
I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.
I suspect the problem is the way my program is going about the checking.
Should I use an SQL insert statement like??
If exists(SELECT username from users where username=@username)
BEGIN
RAISEERROR("Username already exists")
END
ELSE
BEGIN
INSERT etc
If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?
Also some more general questions.
1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.
Dim lclRowRet As SqlDataReader
lclRowRet = LoadUserCommand.ExecuteReader(CommandBehavior.SingleRow)
lclRowRet.Read()
2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()
As a rule I close all my connections as soon as I'm done with them.
Many thanks for your help in advance.
nik
View 5 Replies
View Related
Oct 10, 2007
A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.The default collation for SQLS is apparently SQL_Latin1_General_CP1_CI_AS.I wish to use a variation of this, SQL_Latin1_General_CP1_CS_AIcollation, but there is no such collation returned fromfn_helpcollations(). Also, if I try to use this collation ina CREATE DATABASE stmt, SQLS yells about it.I see that there is a Latin1_General_CS_AI. What effects are therein using this collation? The SQL_* collations are SQL collations,while non-SQL_* collations are Windows collations, yes? SQLS runsonly on Windows, so am I safe in using Latin1_General_CS_AI? Whatdoes the CP1 in the SQL collation signify? Am I asking for trouble?------------------------------------Assuming that I set Latin1_General_CS_AI (or any other case-sensitivecollation) at the database level, I believe my DDL/DML for that databasealso becomes case-sensitive. How can I specify that I want ONLY my dataaccess to be case-sensitive, and not my DDL/DML? I don't want to haveto remember to type "select * from MyCamelCase" when "mycamelcase"should work.Any help appreciated.A new SQLS DBA..aj
View 3 Replies
View Related
Nov 14, 2007
So I am fairly new to Express and I have installed it on my development machine; much tio my chagrin it is quite difficult to import data into SQLEXPRESS. I have a 'sa' account setup and I have created a new database and table within that database, however, when I try to import data into that table by setting up a link server to excel I am having some major issues!
I ran this code first to create the linked server...
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:Anchor_Hocking blactionlist.xls'
SET @provstr = 'Excel 11.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog
Next I try to run any of the statement below and I get the errors pasted below...
SELECT * FROM Anchor_Hocking...Sheet1$
EXECUTE SP_TABLES_EX 'Anchor_Hocking'
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:Anchor_Hocking blactionlist.xls;User ID=sa;Password=sa;Extended Properties=Excel 8.0')...Sheet1$
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Anchor_Hocking".
The file is not open. I have granted full access rights to all users....I am really frustrated!
Also, how can I get SSIS on this machine with SQLEXPRESS?
-Brian
View 3 Replies
View Related
May 16, 2007
First of all I would like to politely greet everybody as I'm new on that forum and new to Data Mining in fact.
To introduce myself I can say I'm a student of Computer Science and I'm trying to use Time Series algorithm for weather analysis. I know that forecasting weather is a hopeless task even for the fastest computers in the world but what I'm trying to do is a kind of aposteriori analysis of historical data to notice some dependencies or characteristic weather behavior on a specified region and perhaps make some short time predictions.
I tried Time Series Algorithm although I have some doubts about methodological justification of this choice (if You have any critical comments please share them with me). But my main questions are about the usage of the algorithm itself:
I've read the documentation and a tutorial on this page for historical predictions but I still don't know what exactly are HistoricalModelCount and HistoricalModelGap. I know that my historical predictions are bounded by a €“ HistoricalModelCount*HistoricalModelGap*, but it's a rather operational knowledge... The explanation is always clouded with an €œinternal model€? phrase. Can You point me to a document where I can find some more detailed information? (What is the form of the model? How is it built? etc.)
Periodicity Hint. How should I treat these optional values? Are they other possible periods of data? I have data about weather measurements made every six hours for thirteen years** so is it a good choice to set this parameter to {365*4,4} (The first goes for a year and the second for a day)?
This is a technical question and I'm really ashamed of myself that I bother You with it. On the time chart in a model Viewer I can see date from the last year only. Zooming out/in, clicking insanely on every pixel on the screen, did not give any result (apart of broken mouse buttons). Is is possible to browse that data in mining model viewer chart?
Thank You in advance for Your replies!
*This formula suggests how this parameters could work but I would like to know it for sure €“ don't want to make some awful mistakes in my project. :-)
**Of course I plan to reduce the amount of data but the period will stay.
View 2 Replies
View Related
Apr 1, 2007
The first question is how to of TimeSeries Algorithm?
Using SQL Server 2005 TimeSeries Algorithm ,I build a data mining model.But after three days,it is still training.The data has 2,200,00 rows.
So what can i do to improve the processing speed.
Thanks!
The second question is parameters in Data Mining Query Task.
Data Mining Query Task is used to get data from data mining model.In the mining model form, i choose a mining model . And in the query form,i wrote a dmx ,"select flattened top 100 predicttimeseries([Xssl],1)
from [Time Series XSSL]".Last i choose a table that is for the data from mining model.
If the "100" is variable , how can i do ?
Thanks a lot!
View 1 Replies
View Related
May 12, 2008
I want the sites for ASP.NET 2.0 and SQL SERVER 2005 interview questions.
2) Sites for SQL SERVER 2005 concepts
View 6 Replies
View Related
Feb 24, 2006
Hi,
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greets,
Ivo Klerkx
View 3 Replies
View Related
Jul 7, 2006
Hi guys
Am i able to deny the SA account access to a specific database?
thanks in advance for your help.
TheGing
View 4 Replies
View Related
Jun 5, 2007
All:
I am writing an Internet/Extranet based (ASP.Net 2.0) web application that uses SQL server 2005 as the database. I am using forms authentication on my web application. I am also storing the connection string to SQL server in my web config file. The conn string is encrypted using DPAPI with entropy. I currently have created a SQL login account on my SQL server for use by the web application. This is the user ID I am using in my conn string. The reason for this is because all persons using the application will NOT have a windows login.
Here is my question: The login I created currently has defaulted to the "dbo" role and therefore has "dbo" rights to the database. I want to setup up this login account so that all it can do is execute stored procedures. I dont want this SQL login to be able to do anything else. In my application I am using stored procedures for ALL data access functions, via a data access layer in my application. Can someone guide me step by step as to how to setup this type of access for this SQL login.
Thanks,
Blue.
View 4 Replies
View Related
Jun 25, 2007
I have a webpage that displays 4000 or more records in a GridView control powered by a SqlDataSource. It's very slow. I'm reading the following article on custom paging: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx. This article uses an ObjectDataSource, and some functionality new to Sql Server 2005 to implement custom paging.There is a stored procedure called GetEmployeesSubestByDepartmentIDSorted that looks like this:ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted( @DepartmentID int, @sortExpression nvarchar(50), @startRowIndex int, @maximumRows int)AS IF @DepartmentID IS NULL -- If @DepartmentID is null, then we want to get all employees EXEC dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows ELSE BEGIN -- Otherwise we want to get just those employees in the specified department IF LEN(@sortExpression) = 0 SET @sortExpression = 'EmployeeID' -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment SET @startRowIndex = @startRowIndex + 1 -- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @DepartmentID) + ' ) as EmpInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql ENDThe part that's bold is the part I don't understand. Can someone shed some light on this for me? What is this doing and why?Diane
View 4 Replies
View Related
Jul 15, 2007
Hi all, i have standard edition of sql server, on a server hat doesnt have sql server standard would i be able to connect to it using my connection string. Or does the server has to have standard edition too. Is this same for express edition, and if possible to do this whats the difference between express connection string from standard edition thanks
View 3 Replies
View Related
Oct 10, 2006
How to get the list of catalog views sys.* (sys.objects, sys.columns ....) in sql server 2005
select * from sys.objects where objectproperty(object_id, 'IsSystemTable') = 1
did not return the list as I've expected
thanks
View 4 Replies
View Related
Feb 29, 2008
I think I read somewhere that sql server 2005 Compact Edition (3.1) does not support views. Does anyone know if views were added to 3.5?
View 3 Replies
View Related
Apr 28, 2006
I am using a XML file and retrieving data for my SSIS 2005 (Intigration Service) package, where after retrieving the data I need to update my XML file with new data by using script task or XML task
<?xml version="1.0" ?>
<TimeBuilder>
<StartYear>2003</StartYear>
<EndYear>2004</EndYear>
</TimeBuilder>
View 3 Replies
View Related
Nov 23, 2006
I have a SQL Server 2005 database (called BDHSE) in a PC which i call PC1. I have a second PC (PC2) and both are within a network (a WLAN).
What i want is to have access to BDHSE from an application in VB6 (APP1) running in PC2. All the INSERT, DELETE, UPDATE records process is done through APP1.
APP1 ia currently running in PC1 and is to be installed on PC2.
I have these questions:
1. What do i need to install in PC2 since all the INSERT, DELETE, and UPDATE is done using APP1? I guess i only have to install the Microsoft SQL Native Client (with all the prerequisites of course) but i am not sure.
2. In the APP1 made in VB6, do i have to change the connectionstring since i am accesing the database which physically is at PC1 and the APP1 will be used in PC2?
3. Any advice you can give me on doing this will be well received.
Thanks in advance,
BSc Fernando Martinez
View 4 Replies
View Related
Dec 18, 2007
I have created a database and used visual basic 2005.net2.0 to build the application. I used the data wizard. it created datatables and then I bound it to the contols on the form. The program is published to the server and each instance loads from the server and executes. When it stops, the instance is destructed from the host pc. The pc's are large ram and very small hard drive. These were the specs of the job. now when each pc loads an instance of the program, they can all load and edit the same exact record. I tried to bust it and I got on the same record and changed first name on one and last name on the other. the second one overwrote the first name edit. All of the data handling is done inside of the dataset designer and it does not allow edits. ( actually you can edit, but the next time you save it, or run it, it will rewrite what you added and it will be gone.) is there anything I can add to the form vb to tell it to lock the current record?
View 3 Replies
View Related
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
Jun 7, 2006
We have found that it is common for Visual Studio 2005 to crash when editing or running SSIS packages -- from CTP versions through beta versions and including the release version.
Of course we kept hoping that newer releases would become more stable, or at least more robust -- and now I'm hoping there will be a service pack, which might make it more robust?
View 3 Replies
View Related
May 2, 2006
Please help me with editing a XML file through script tack in SSIS (Integration Service) 2005
Thank you
View 1 Replies
View Related
Jul 20, 2005
I have some software (written in Delphi 5) which has been working forseveral months without a problem.I have been given a copy of the database on our development server(SQL 7) and have pointed the software to this. So, nothing has changedwith the software. In theory the servers should be the same.However, if I try to make a small change to any record (as I would onthe 'live' system), I get an error stating that another user isediting the record. Now, I'm the only person with access to thisserver. So am I stopping myself I wonder ?If I create an ODBC connection to the database through Access 2000, Iget the same error. I feel I can reasonably assume that the softwareis not at fault.I'm sure it's something SQL based, whether it's an error orconfiguration difference, but I'm stuck.I've checked who is locking/blocking, and yes it will show me ashaving the record open, but this is me attempting to edit the recordso I would expect this.I can edit the table directly in SQL, but it takes 30 seconds or more,freezes EM and then allows the change. Table info below :if exists (select * from sysobjects where id =object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PostReceived]GOCREATE TABLE [dbo].[PostReceived] ([PostID] [int] IDENTITY (1, 1) NOT NULL ,[Type] [varchar] (100) NULL ,[ClientsName] [varchar] (100) NULL ,[DateReceived] [datetime] NULL ,[EnteredBy] [varchar] (100) NULL ,[AssignedTo] [varchar] (100) NULL ,[DateAssignedTo] [datetime] NULL ,[Adviser] [varchar] (100) NULL ,[TargetDate] [datetime] NULL ,[CompletionDate] [datetime] NULL ,[Completed] [bit] NULL ,[KeyAccount] [varchar] (100) NULL ,[Notes] [text] NULL ,[Specific1] [varchar] (20) NULL ,[Specific2] [varchar] (20) NULL ,[Specific3] [varchar] (20) NULL ,[Specific4] [varchar] (20) NULL ,[Specific5] [varchar] (20) NULL ,[ToDelete] [bit] NULL ,[EnterUser] [varchar] (20) NULL ,[Returned] [bit] NULL ,[ReturnDate] [datetime] NULL ,[ReturnReason] [varchar] (87) NULL ,[PrintAdviser] [bit] NULL ,[EmailAdviser] [bit] NULL ,[EmailSM] [bit] NULL ,[EmailRegionManager] [bit] NULL ,[ReturnText] [varchar] (150) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO(Yes I know about using Identity in the key, but it's on my list tochange before anyone says anything).
View 5 Replies
View Related
May 3, 2000
Hello!
We have a SQL Server table that needs to be maintained by the actuarial department. Therefore, I was going to have them make changes to the table using something easy like Microsoft Access 2000. When I go into Access and create an ODBC link to the table, I cannot change the data. (i.e. The insert new record feature is greyed out.) I CAN change the data via Enterprise Manager, so I don't think it is SQL Server security. Any other ideas?
Thanks,
Sharon
View 1 Replies
View Related
Aug 17, 2000
What is the best way to read and edit data in the tables of a sql server 6.5 database?
Thanks
Gunnar
gunnardl@yahoo.com
View 1 Replies
View Related
Jun 11, 2014
I am trying to edit the 3 facets found under Report Server while logged onto the server as an administrator. This is one of 3 (supposedly) identical Report Servers (Windows Server 2012 Standard - Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) ) and the only one experiencing an issue. The images below show what happens when I try to change the facets to TRUE and their original values. Incidentally, when first hitting the facets, they are not set to true even though the config file has all 3 values set to true.
[URL]
View 0 Replies
View Related
Apr 25, 2007
Hi guys,
I have read many articles on the matter and I have probably used up all my printer's ink in doing so, however, some questions still remain.
1) What happens if I have to reboot the mirror.. security update, etc.? Obviously the session is broken during reboot, but would I have to do another backup of the principal and resync everything?
2) I know it is not best practice but at this point I have no choice, however, I wanted to get your guys feedback on having two instances of SQL2005 on my development box. One for the mirroring of the production and the second for development. The two instances would live on their own drive... not partitioned and have adequate memory and space.
What would I have to look forward with this?
3) Lastly, I am still uncertain if mirroring is approved for production. Is it?
Thanks for your help.
View 4 Replies
View Related
Jan 29, 2008
What is the proper syntax to return records that appear in 1 table but NOT the other table? I have 2 tables that should contain the same records(based on shipping report number), so my join will use this field. How can I only return the data where the shipping report number appears in only 1 of the tables though>?
View 3 Replies
View Related
Apr 26, 2006
Hello,I have been researching the use of symmetic and asymmetic encryption inSQL 2005 and I am pretty excited to give it a try. Through examples, Ican encrypt the data, but I cant figure out what to do next...What I want:1. our social security field to be encrypted so that only the person(s)that need it can decrypt it.2. prevent DBA's from decrypting the data themselfs3. Simple way to encrypt the data on the table (maybe a trigger?)I thought I would use asymmetric keys, this way I can embed the publickey into my data warehouse process to encrypt the data.I thought I would prompt the user for the private key when the reportruns, that way I wont store the key on the server.This would be a place to start.Someone in the office said that we can store the keys in Activedirectory, so maybe I could make this seemless to the user running thereport?I've found a lot of great articles that got me started, but I amneeding the next stepAny Ideas would be apprecitated!TIARoblinks to articles I have found handy:http://www.databasejournal.com/feat...int.php/3483931http://www.devx.com/dbzone/Article/29232/0/page/3http://www.sqlservercentral.com/col...rintversion.asp
View 4 Replies
View Related
May 28, 2008
I am an Oracle DBA who inherited SQL Server administration. I have been to some 2005 training and I've been supporting several DB's for a while now but I still have some nagging security questions and would appreciate some help.
1) I needed to grant execute on a specific procedure but when I drilled down, I found that it already had execute in the EFFECTIVE PERMISSIONS. I would like to know how to tell where it got this permission from. I did some digging and found that execute appears to have been granted to the schema itself. I didn't know you could do that. Would this result in the effective permission that I observed?
2) I am trying to audit the permissions on existing principles. In Management Studio I drilled down and found permissions under Security and under Server Properties. There are also more permissions under Database Properties and Security and still more assigned at the specific object level. Where can I go or what can I query to see ALL the permissions a principle has been granted across the entire server?
3) If I grant a principle CONTROL to a schema does that also automatically confer DDL rights to said schema or would additional privs be required to perform DDL?
Thanks in advance,
Roger Westbrook
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
