Copying roles from one database to another w/ permissions

Question

Post reply

Copying roles from one database to another w/ permissions

Brian M

SSC Rookie

Points: 47
More actions
September 18, 2006 at 1:41 pm

#113575

Never had to do this before, but the developer created over 30 roles with different permissions across 170 tables.
He then created a new database, with the same tables, but did not recreate the roles, he wants me (the poor IT guy) to copy the roles from the other database into this new database.
The built in scripting tools do not copy the Securables, just the names of the roles.
Any suggestions?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Th. Fuchs SSC Enthusiast Points: 142 More actions · Answer 1

-- Execute batch in the old DB

-- Execute result in new DB

-- Script permissions on all tables

-- Author: Th. Fuchs, IMC GmbH Chemnitz

declare @object int, @hresult int, @property varchar(255), @return varchar(8000)

declare @src varchar(255), @desc varchar(255), @cmd varchar(300)

declare @ScriptType integer, @tabname varchar(200), @dbname varchar(128), @pwd varchar(20)

declare @tablelist table (tabid integer, tabname varchar(128))

set @dbname = 'INVEKOS2' -- define db to script

set @pwd = '' -- top secret!

-- Create the sqlserver-object

execute @hresult = sp_OACreate 'SQLDMO.SQLServer', @object output

if @hresult = 0 -- connect to server

execute @hresult = sp_OAMethod @object, 'Connect', NULL, 'SMUL-DB-121', 'sa', @pwd

-- Get all tablenames

insert into @tablelist(tabid, tabname)

select id, user_name(objectproperty ( id , 'OwnerId')) + '.' + object_name(id)

from dbo.sysobjects

where objectproperty(id, 'IsTable') = 1

and objectproperty(id, 'IsSystemTable') = 0

-- step through tables, script descriped in

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp

select @ScriptType = 2 -- SQLDMOScript_ObjectPermissions

declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for

select tabname from @tablelist order by tabid

open cur_tab

fetch next from cur_tab into @tabname

while @@fetch_status = 0 and @hresult = 0

begin

select @cmd = 'databases("' + @dbname + '").tables("' + @tabname + '").script'

execute @hresult = sp_OAMethod @object, @cmd, @return OUTPUT, @ScriptType

print @return

fetch next from cur_tab into @tabname

end

close cur_tab

deallocate cur_tab

-- Destroy the object.

if @hresult = 0 -- disconnect and freemem

execute @hresult = sp_OADestroy @object

-- If Error occurs, get a tip

if @hresult != 0

begin

execute sp_OAGetErrorInfo @object, @src OUT, @desc OUT

select hresult = convert(varbinary(4),@hresult), Source = @src, Description = @desc

end

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Why you could not just restore the database under a different name and if the developer needs empty tables in the new database just truncate all tables?

Regards,Yelena Varsha

Brian M SSC Rookie Points: 47 More actions · Answer 3

The data that has come into existence in this new database cannot be eradicated because it is semi-live data.

Brian M SSC Rookie Points: 47 More actions · Answer 4

I am getting the following Error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

What connection is it trying to use? how is it connecting (IP?)