Re: db_ddladmin rights in Management Studio
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 18 Aug 2007 21:42:48 +0000 (UTC)
gj111 (gj111@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
We are running SQL Server 2005 enterprise with SP2. Recently, after
assigning db_ddladmin permissions to someone, I ran across something
that I'm curious about. It seems that as a db_ddladmin of a database in
the dbo schema, it's possible to create and drop objects, but not alter
them using Management Studio. However, it is possible to alter objects
in Query Editor as db_ddladmin. This isn't really a problem as we can
use TSQL for these modifications. Just curious as to what appears to be
a difference in behavior between Management Studio and Query Editor.
I'm new to SQL Server 2005's security model, but from the messages
returned when attempting to modify with the GUI, my guess is that this
has something to do with the differences in SQL Server 2000 and 2005
regarding users and schemas owning objects. But I'm not sure why the
behavior appears inconsistent between the GUi and TSQL. Is the behavior
different or am I missing something?
I ran Profiler to see what queries Mgmt Studio runs, and I found that it
runs:
select Has_Perms_By_Name(N'dbo.binary', 'Object', 'ALTER') as ALT_Per,
Has_Perms_By_Name(N'dbo.binary', 'Object', 'VIEW DEFINITION') as
View_def_Per,
Has_Perms_By_Name(N'dbo.binary', 'Object', 'CONTROL') as Contr_Per
This query returns 1, 0, 0 for a user with db_ddladmin, why Mgmt Studio
thinks that this guy is not good enough. It appears that Mgmt Studio
has overlooked db_ddladmin.
This could be construed as a good thing, at least when it comes to
the Table Designer and the diagrams, as these tools are seroiusly
buggy and are dangerous to use for table modifications, unless you
understand what they do, including what they shouldn't do.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: Script SQL2005 Security?
- Next by Date: Security for connections from DMZ
- Previous by thread: Re: Script SQL2005 Security?
- Next by thread: Security for connections from DMZ
- Index(es):
Relevant Pages
|
