SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB_OWNER Privilege but Unable to create views


DB_OWNER Privilege but Unable to create views

Author
Message
MSSQL_NOOB
MSSQL_NOOB
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 173
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.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10148 Visits: 1917
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
MSSQL_NOOB
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 173
There is no explicit permissions granted / deny on that username.
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10073 Visits: 4894
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
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10073 Visits: 4894
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
MSSQL_NOOB
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 173
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
MSSQL_NOOB
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 173
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
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10073 Visits: 4894
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
MSSQL_NOOB
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 173
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search