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


What can the role db_ddladmin do?


What can the role db_ddladmin do?

Author
Message
Axel Achten
Axel Achten
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 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
vbenus
vbenus
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 46

Hi,

try stored ptocedure sp_dbfixedrolepermission




Kindest Regards,

vbenus
Axel Achten
Axel Achten
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 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
Graham Cottle
Graham Cottle
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 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.





ferrell
ferrell
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 1

Grasshopper

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

Thanks





Graham Cottle
Graham Cottle
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 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





Axel Achten
Axel Achten
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 54
Oh boy I'm happy:-)

This was more than I expected.

Thanks guys...
Enrique Ortiz
Enrique Ortiz
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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!!


Lorelei
Lorelei
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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'''


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