What can the role db_ddladmin do?
-
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
-
Hi,
try stored ptocedure sp_dbfixedrolepermission
Kindest Regards,vbenus
-
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
-
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.
-
Grasshopper
Would you be willing to "share" your stored proc that changes ownership from you to DBO?
Thanks
-
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
-
Oh boy I'm happy:-)
This was more than I expected.
Thanks guys...
-
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!!
-
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'''
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply