August 20, 2012 at 7:59 am
I've got a schema (Reporting) that I want to grant CREATE, ALTER & DELETE permissions to a group; the T-SQL statements I did are:
GRANT ALTER ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT EXECUTE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT DELETE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT CREATE VIEW TO [abc\BI Admins]
GRANT CREATE FUNCTION TO [abc\BI Admins]
GRANT INSERT ON [log].[DatabaseChangeLog] TO [abc\BI Admins] -- used by DDL trigger
But the user still gets the error:
[font="Courier New" color="red" size="2"]Msg 262, Level 14, State 1, Procedure vwSalesOrderDetail, Line 6
CREATE VIEW permission denied in database 'MWC2'.[/font]
What obvious thing am I missing?
Thanks,
Rob
August 20, 2012 at 8:58 am
check the database MCW2 permissions available to the role...
here is a better link ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
August 20, 2012 at 9:07 am
robert.gerald.taylor (8/20/2012)
I've got a schema (Reporting) that I want to grant CREATE, ALTER & DELETE permissions to a group; the T-SQL statements I did are:
GRANT ALTER ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT EXECUTE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT DELETE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT CREATE VIEW TO [abc\BI Admins]
GRANT CREATE FUNCTION TO [abc\BI Admins]
GRANT INSERT ON [log].[DatabaseChangeLog] TO [abc\BI Admins] -- used by DDL trigger
But the user still gets the error:
[font="Courier New" color="red" size="2"]Msg 262, Level 14, State 1, Procedure vwSalesOrderDetail, Line 6
CREATE VIEW permission denied in database 'MWC2'.[/font]
What obvious thing am I missing?
Thanks,
Rob
did the CREATE VIEW command feature the fully qualified CREATE VIEW Reporting.vwSalesOrderDetail or just CREATE VIEW vwSalesOrderDetail ;
if the second, then the default schema of the users must be Reporting, otherwise they try to create dbo.viewname, which they don't have access to do, right?
Lowell
August 20, 2012 at 9:15 am
Yes, the CREATE VIEW statement is fully qualified. The BI Admins group does not have permissions to the dbo schema.
CREATE VIEW [Reporting].[vwSalesOrderDetail]
AS
...
August 20, 2012 at 9:15 am
Lowell (8/20/2012)
robert.gerald.taylor (8/20/2012)
I've got a schema (Reporting) that I want to grant CREATE, ALTER & DELETE permissions to a group; the T-SQL statements I did are:
GRANT ALTER ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT EXECUTE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT DELETE ON SCHEMA::Reporting TO [abc\BI Admins]
GRANT CREATE VIEW TO [abc\BI Admins]
GRANT CREATE FUNCTION TO [abc\BI Admins]
GRANT INSERT ON [log].[DatabaseChangeLog] TO [abc\BI Admins] -- used by DDL trigger
But the user still gets the error:
[font="Courier New" color="red" size="2"]Msg 262, Level 14, State 1, Procedure vwSalesOrderDetail, Line 6
CREATE VIEW permission denied in database 'MWC2'.[/font]
What obvious thing am I missing?
Thanks,
Rob
did the CREATE VIEW command feature the fully qualified CREATE VIEW Reporting.vwSalesOrderDetail or just CREATE VIEW vwSalesOrderDetail ;
if the second, then the default schema of the users must be Reporting, otherwise they try to create dbo.viewname, which they don't have access to do, right?
Is that relevant since the GRANT CREATE VIEW is at a database level, not a schema level? In which case I ask, did the OP do this on MASTER database, or the database in question; i.e. try USE MWC2
GO
GRANT CREATE VIEW TO [abc\BI Admins]
GO
Jared
CE - Microsoft
August 20, 2012 at 9:19 am
CREATE VIEW permission denied in database 'MWC2'
Looks to me like there is an explicit DENY of CREATE VIEW permissions somewhere.
DENY will override/"cancel" an equivalent GRANT.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 20, 2012 at 10:19 am
SQLKnowItAll (8/20/2012)
Is that relevant since the GRANT CREATE VIEW is at a database level, not a schema level? In which case I ask, did the OP do this on MASTER database, or the database in question; i.e. tryUSE MWC2GO
GRANT CREATE VIEW TO [abc\BI Admins]
GO
i think it would be relevant, but admittedly the error you get is slightly different than the one reported.
here's a full proof of concept, and the error my test user received:
CREATE Database SchemaTest
GO
USE SchemaTest
GO
--create a proof of concept user.
CREATE USER TestUser WITHOUT LOGIN
--our test schema
CREATE SCHEMA Reporting
GRANT ALTER ON SCHEMA::Reporting TO [TestUser]
GRANT EXECUTE ON SCHEMA::Reporting TO [TestUser]
GRANT DELETE ON SCHEMA::Reporting TO [TestUser]
GRANT CREATE VIEW TO [TestUser]
GRANT CREATE FUNCTION TO [TestUser]
--skipped as i don't need for this test.
--GRANT INSERT ON [log].[DatabaseChangeLog] TO [TestUser] -- used by DDL trigger
EXECUTE AS USER='TestUser'
--fails: explicit dbo
CREATE VIEW dbo.MyView
AS SELECT 1 AS SomeIndex
GO
--fails: implieddbo
CREATE VIEW MyView2
AS SELECT 1 AS SomeIndex
GO
--success
CREATE VIEW Reporting.MyView2
AS SELECT 1 AS SomeIndex
REVERT;
Msg 2760, Level 16, State 1, Procedure MyView, Line 2
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply