<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Security
»
DB_OWNER Privilege but Unable to create views...
DB_OWNER Privilege but Unable to create views
Rate Topic
Display Mode
Topic Options
Author
Message
MSSQL_NOOB
MSSQL_NOOB
Posted Thursday, December 13, 2012 10:46 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38,
Visits: 111
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
K. Brian Kelley
K. Brian Kelley
Posted Thursday, December 13, 2012 12:32 PM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584,
Visits: 1,790
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
MSSQL_NOOB
MSSQL_NOOB
Posted Thursday, December 13, 2012 1:00 PM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38,
Visits: 111
There is no explicit permissions granted / deny on that username.
Post #1396359
Welsh Corgi
Welsh Corgi
Posted Thursday, December 13, 2012 1:32 PM
Hall of Fame
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833,
Visits: 4,052
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
Welsh Corgi
Welsh Corgi
Posted Thursday, December 13, 2012 1:46 PM
Hall of Fame
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833,
Visits: 4,052
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
MSSQL_NOOB
MSSQL_NOOB
Posted Friday, December 14, 2012 8:35 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38,
Visits: 111
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
MSSQL_NOOB
MSSQL_NOOB
Posted Friday, December 14, 2012 8:40 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38,
Visits: 111
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
Welsh Corgi
Welsh Corgi
Posted Friday, December 14, 2012 8:59 AM
Hall of Fame
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833,
Visits: 4,052
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
MSSQL_NOOB
MSSQL_NOOB
Posted Tuesday, December 18, 2012 1:45 PM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 38,
Visits: 111
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
