Sql-server – SQL Server 2000: Can’t DROP VIEW

sql serversql-server-2000view

I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.

Background: I found that one view can be drop and I know for sure no one uses it. And it links to detach (unused) database. I decided to drop it. Before I drop I check the view to make sure.

CREATE View vw_obsolete_view
AS
SELECT [very long],
,
,
...
FROM [unused_database].dbo.vw_obsolete_view  


DROP VIEW vw_obsolete_view

Now, View was dropped. It seem to be OK. However, I got a call from user that Application (ADP) got an error message. It states that view vw_obsolete_view is missing.

Since this view is not my coding and the my Application is call only my objects. So I have no idea why this cause an error. It took me an hour to figure out. I check all dependent objects. Nothing links to this view at all. I checked all ADP VBA code. Nothing relates to this view also.

I can't find what wrong with this. But I know that the problem is from dropping view because I was the only one who changes the database. So I make a new view with the same name.

CREATE View vw_obsolete_view
AS
SELECT 1 AS total

The error at the end user is disappear. End User is happy but I still can't drop view. I still have no clue.

Sidenote: I can't upgrade SQL Server right now.

Best Answer

Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.

For example: is if just one client or user who notices? Are they running a different ADP?

The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.

Related Solutions

SQL Server – Setting User Permissions for Different Schemas

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as 
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA] 
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc] 
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :

enter image description here

PostgreSQL – How to Replace a Materialized View

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.

When you do so, I can only recommend two small things:

Use DROP MATERIALIZED VIEW blabla CASCADE to get a list of all dependent objects
Do the drop and recreation of all dependent object in one transaction.

Best Answer

Related Solutions

SQL Server – Setting User Permissions for Different Schemas

PostgreSQL – How to Replace a Materialized View

Related Question