CREATE VIEW permissions

Question

Post reply

CREATE VIEW permissions

robert.gerald.taylor

SSChampion

Points: 13133
More actions
August 20, 2012 at 7:59 am

#280024

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

check the database MCW2 permissions available to the role...

here is a better link ..

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/abc8e23d-0169-4cf8-a546-3dd0a5758d21

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 3

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

...

Jared SSC Guru Points: 61793 More actions · Answer 4

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

ScottPletcher SSC Guru Points: 100946 More actions · Answer 5

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) A socialist is someone who will give you the shirt off *someone else's* back.

Lowell SSC Guru Points: 323504 More actions · Answer 6

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. try USE MWC2 GO 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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!