Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB_OWNER Privilege but Unable to create views Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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.
Post #1396301
Posted Thursday, December 13, 2012 12:32 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1396349
Posted Thursday, December 13, 2012 1:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
There is no explicit permissions granted / deny on that username.
Post #1396359
Posted Thursday, December 13, 2012 1:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1396376
Posted Thursday, December 13, 2012 1:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1396384
Posted Friday, December 14, 2012 8:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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.

Post #1396696
Posted Friday, December 14, 2012 8:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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.
Post #1396699
Posted Friday, December 14, 2012 8:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1396713
Posted Tuesday, December 18, 2012 1:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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?
Post #1398024
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse