DB_OWNER Privilege but Unable to create views

Question

DB_OWNER Privilege but Unable to create views

MSSQL_NOOB

SSCommitted

Points: 1605
More actions
December 13, 2012 at 10:46 am

#98577

MSSQL 2000
A username has a DB_OWNER privilege. But when he tries to create views OR create dts package; it gives him a permissions error. Should I grant him the db_ddladmin instead?
Edit
Look at http://technet.microsoft.com/en-us/library/cc966453.aspx
Seems like db_ddladmin is for Runs any Data Definition Language (DDL) command in a database.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Create DTS packages is in the msdb database. You didn't indicate what database you're trying to give permissions.

If I remember right for SQL Server 2000, even if you're a DB_Owner member, an explicit DENY such as DENY CREATE VIEW would stop the user from doing so. Check to see if that's the case in the database you're working in.

K. Brian Kelley
@kbriankelley

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 2

There is no explicit permissions granted / deny on that username.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

MSSQL_NOOB (12/13/2012)
There is no explicit permissions granted / deny on that username.

How did you verify that there is not an explicit deny for that User?

EXEC sp_helprotect NULL, 'UserName'

Is that User a member of a role that has a DENY?

Adding the User to the db_ddladmin Fixed Database is not going to help because the db_Owner role includes the permissions of db_ddlAdmin.

I would double check because the error that the user is getting would suggest that there is a DENY out there.

Good luck.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

MSSQL_NOOB (12/13/2012)
MSSQL 2000
A username has a DB_OWNER privilege. But when he tries to create views OR create dts package; it gives him a permissions error.

What is the specific Error Message?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 5

I clicked on the database name, Security tree, Users, right click his Username, Properties, Securables on SSMS2008. There is nothing on the Permissions: Explicit.

When I run

USE [dbname]
GO
EXEC sp_helpprotect NULL, 'john'

I get

Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 6

Welsh Corgi (12/13/2012)
What is the specific Error Message?

CREATE VIEWS permission denied in database 'DBNAME'.

Edit:

I actually found http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/bfd630bc-e405-4f9d-b63f-f673b3f4ccf5/

that has the same issue. They are saying it was a bug in MS.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

MSSQL_NOOB (12/14/2012)
Welsh Corgi (12/13/2012)
What is the specific Error Message?
CREATE VIEWS permission denied in database 'DBNAME'.
Edit:
I actually found http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/bfd630bc-e405-4f9d-b63f-f673b3f4ccf5/
that has the same issue. They are saying it was a bug in MS.

This is not a bug.

The User has been denied the CREATE VIEW Permission.

Try revoking the deny permission.

REVOKE CREATE VIEW TO [UserName]

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 8

Welsh Corgi (12/14/2012)
This is not a bug.
The User has been denied the CREATE VIEW Permission.
Try revoking the deny permission.
REVOKE CREATE VIEW TO [UserName]

I have issued the following

USE [dbname]

GO

REVOKE CREATE VIEW FROM <username>

It completed successfully; but the user has been out of office - so I cannot test it yet. It's his domain account and we're not allowed to have his password. Since it's 2000, I cannot use EXECUTE AS, correct?