February 8, 2006 at 9:30 am
Hi
A view in a database (say "one") looses all it permission when it depends on a table or view in another database (say "two") and that table or view in second database is dropped and re-created.
Probabbly following example will make it clear.
-----------------------------------------------------------
(1) Create a table and a view in some database (I will call it database "ONE")
-- IN DATABASE "ONE"
CREATE TABLE test_tbl (empno int, empname varchar(10))
CREATE VIEW test_view
AS
SELECT empname FROM test_tbl
-----------------------------------------------------------
(2) Create a view in second database (I will call it database "two") that depends on the view in database "One" (we can create this view also to directly depend on the table in database "one")
-- IN DATABASE "TWO"
CREATE VIEW test_view
AS
SELECT * FROM one..test_view
-----------------------------------------------------------
(3) Now grant the permission on view (or table if you use table) in database "ONE" for some user that also belongs to database "two" (I am using "someuser" here)
-- IN DATABASE "ONE"
GRANT SELECT ON test_view TO someuser
-----------------------------------------------------------
(4) Now any time if we drop and recreate the view (or table) in source database ("One" here) the permissios are lost for the view in second database (although it keeps the schema and if something is changed in source view/table we can user "sp_refreshview" to refresh the schema)
Like we can try folloing as an example:
-- IN DATABASE "ONE"
IF OBJECT_ID('test_view') IS NOT NULL
DROP VIEW test_view
GO
CREATE VIEW test_view
AS
SELECT empname FROM test_tbl
-----------------------------------------------------------
(5) When we go in database "two" and do a select on 'test_view' it will say "SELECT permission denied on object 'test_view' .............
------------------------------------------------------------
My question is, is there any way by which we can keep these permission? Because when a developer tries to recreate a object system does not give them any message like another object in some other database depends on this so they would go ahead and recreate the table/view but when users of the other database tries to run SELECTs they get permission error.
Thanks for the help
February 8, 2006 at 10:26 am
Each time the underlying tables are dml modified the view needs to be recompiled.
Why are you using drop view instead of alter view?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply