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

What can the role db_ddladmin do? Expand / Collapse
Author
Message
Posted Tuesday, June 22, 2004 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 2:07 AM
Points: 11, Visits: 54
Our programmers must be able to create tables, views etc. in the databases they designed.
We want to grant them the role db_ddladmin but can anyone tell me where I can find the exact list of commands they can use and what the difference is with the dbo role

Kind regards
Post #122308
Posted Tuesday, June 22, 2004 8:03 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 7:32 AM
Points: 673, Visits: 46

Hi,

try stored ptocedure sp_dbfixedrolepermission

 




Kindest Regards,

vbenus
Post #122339
Posted Tuesday, June 22, 2004 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 2:07 AM
Points: 11, Visits: 54
I tried them but they asume everybody knows all DDL commands.
So i'll rephrase: does anyone have a list with all the DDL commands?

Regards
Post #122345
Posted Wednesday, June 23, 2004 12:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 12, 2013 5:09 AM
Points: 243, Visits: 178

I can't supply a list, but I can speak from experience. Our DBAs only allow the DDLAdmin and SecurityAdmin roles. They went to this route when they migrated from 6.5 to 7.0 some years ago. They used to grant dbowner. The only reason they went this way was to prevent developers from performing their own backups. Not that I've looked into it, I believe that on 2000, it is possible to have dbowner and be denied backup rights. We still have ddladmin though.

What it doesn't allow me to use is SQL Profiler and the Index Tuning Wizard, both of which can be useful.

Otherwise, I have no problems with development. I can do everything I need to with just the ddladmin role. However, because I don't have dbowner, all objects belong to me. Thus I have to change ownership to dbo and add permissions to each object. But having written a stored procedure which does all of this, I find it no hardship these days.




Post #122483
Posted Wednesday, June 23, 2004 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 17, 2005 5:38 AM
Points: 7, Visits: 1

Grasshopper

Would you be willing to "share" your stored proc that changes ownership from you to DBO?

Thanks




Post #122539
Posted Wednesday, June 23, 2004 6:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 12, 2013 5:09 AM
Points: 243, Visits: 178

No problem at all:

enjoy. Graham

/*

This Procedure takes all of my objects and converts them to dbo ownership and

grants access permissions to the admin role and the dbo user

 

what we want to do is:

       make dbo the owner of all our objects

       revoke all access to objects

       Grant access as required

 

*/

 

CREATE PROCEDURE dbo.DBO_Owners AS

 

declare @strExecLine nvarchar(4000)

declare @strName nvarchar(128)

declare @strOwner nvarchar(128)

declare @intRowid int

 

set nocount on

-- tables

 

-- create a temporary table to hold a list of all the user tables

create table #tables

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

 

insert into #tables

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'u' and sysobjects.name <> 'dtproperties'

 

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #tables where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #tables where bt_used = 0

 

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

              --print @strExecLine

              exec (@strExecLine)

       end

 

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public]'

       exec (@strExecLine)

 

       -- grant access as required

 

       select @strExecLine = 'grant select on ' + @strname + ' to [public]'

       exec (@strExecLine)

 

-- Add in alternative permissions here if required

 

       update #tables

       set bt_used = 1

       where in_rowid = @intRowid

end

 

drop table #tables

 

-- views

 

-- create a temporary table to hold a list of all the user views

create table #views

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

 

insert into #views

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'v' and sysobjects.status > 0

 

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #views where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #views where bt_used = 0

 

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

              exec (@strExecLine)

       end

 

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

 

       -- grant access as required

       select @strExecLine = 'grant select on ' + @strname + ' to [public]'

       exec (@strExecLine)

 

 

 

       update #views

       set bt_used = 1

       where in_rowid = @intRowid

end

 

drop table #views

 

 

-- stored procedures

-- create a temporary table to hold a list of all the user procedures

create table #procs

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

 

insert into #procs

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'p' and sysobjects.[name] not like 'dt%'                        --and sysobjects.[name] not like 'dbo%'

 

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #procs where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #procs where bt_used = 0

 

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

       --     print @strExecLine

              exec (@strExecLine)

       end

 

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

 

       select @strExecLine = 'grant execute on ' + @strname + ' to [public]'

       exec (@strExecLine)

 

       update #procs

       set bt_used = 1

       where in_rowid = @intRowid

end

 

drop table #procs

 

 

-- User Defined functions

-- create a temporary table to hold a list of all the user defined functions

create table #udfs

       (in_rowid     int   identity(1,1),

       vc_name       nvarchar(128),

       vc_owner      nvarchar(128),

       bt_used              bit)

 

insert into #udfs

       (vc_name, vc_owner, bt_used)

select sysobjects.[name], sysusers.[name], 0

from sysobjects inner join sysusers on sysobjects.uid = sysusers.uid

where sysobjects.xtype = 'FN' and sysobjects.[name] not like 'dt%'                       --and sysobjects.[name] not like 'dbo%'

 

-- loop through this table and build / execute the change owner, and the grant

while exists (select * from #udfs where bt_used = 0)

begin

       select top 1

              @intRowid = in_rowid, @strName = vc_name, @strOwner = vc_owner

       from #udfs where bt_used = 0

 

       if @strOwner <> 'dbo'

       begin

              select @strExecLine = 'sp_changeobjectowner ''' + @strName + ''',''dbo'''

       --     print @strExecLine

              exec (@strExecLine)

       end

 

       -- revoke access before regranting it

       select @strExecLine = 'revoke all on ' + @strName + ' from [public] '

       exec (@strExecLine)

 

       select @strExecLine = 'grant execute on ' + @strname + ' to [public]'

       exec (@strExecLine)

 

 

       update #udfs

       set bt_used = 1

       where in_rowid = @intRowid

end

 

drop table #udfs

 




Post #122546
Posted Thursday, June 24, 2004 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 2:07 AM
Points: 11, Visits: 54
Oh boy I'm happy

This was more than I expected.

Thanks guys...
Post #122740
Posted Friday, September 24, 2004 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2005 9:13 AM
Points: 1, Visits: 1

The folowing link provides more information about the fixed roles and what could they do, provided just as General Information:

http://www.databasejournal.com/features/mssql/article.php/1479561

Regards!!

 

Post #138736
Posted Thursday, October 21, 2004 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 2, 2005 8:06 AM
Points: 44, Visits: 1

Thanks!

 

I modified it to use the existing owner name that I need changed to avoid an Invalid object name error.

 

       if @strOwner = 'existingname'

       begin

              select @strExecLine = 'sp_changeobjectowner 'existingname.'' + @strName + ''',''dbo'''

Post #142856
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse