Es muy habitual que durante nuestro día a día recurramos algunos de los procedimientos almacenados de sistema de la familia "sp_help". Los motivos pueden ser muchos, lo importante es saber que existen y el código que contienen podemos reutilizarlo para nuestros propósitos.
En este caso necesitamos migrar los derechos que tienen los usuarios y grupos sobre los objetos
de la base de datos, la cual previamente hemos recuperado en un servidor que tiene instalado
SQL Server 2005.
Este procedimiento que dejo aqui, sirve para generar automáticamente un script que contiene
las instrucciones sql para volver a crear los permisos de un usuario o grupo de usuarios y asi
facilmente poderlo correr en la nueva instalación que tenemos de sql.
Aqui tomamos de base el procedimiento sp_helpprotect y creamos un nuevo en nuestra base
de datos con el nombre de sp_helpprotectLUIS
Aqui esta el procedimiento
--exec sp_helprotectLUIS null,'COSTOS'
--
CREATE PROCEDURE sp_helprotectLUIS
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as
/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null
@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null
@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would
-- have 'dbo' as grantor.
@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
Declare
@vc1 sysname
,@Int1 integer
Declare
@charMaxLenOwner character varying(11)
,@charMaxLenObject character varying(11)
,@charMaxLenGrantee character varying(11)
,@charMaxLenGrantor character varying(11)
,@charMaxLenAction character varying(11)
,@charMaxLenColumnName character varying(11)
Declare
@OwnerName sysname
,@ObjectStatementName sysname
/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id int Null
,Type1Code char(6) collate database_default NOT Null
,ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,ActionCategory char(2) collate database_default Null
,ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,ObjectName sysname collate database_default NOT Null
,GranteeName sysname collate database_default NOT Null
,GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,ColId smallint Null
,Max_ColId smallint Null
,All_Col_Bits_On tinyint Null
,new_Bit_On tinyint Null ) -- 1=yes on
/* Check for valid @permissionarea */
Select @permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea) <= 0
AND charindex('S',@permissionarea) <= 0)
begin
raiserror(15300,-1,-1 ,@permissionarea,'o,s')
return (1)
end
select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF (@vc1 is not null and @vc1 <> db_name())
begin
raiserror(15302,-1,-1) --Do not qualify with DB name.
return (1)
end
/* Derive OwnerName and @ObjectStatementName*/
select @OwnerName = parsename(@name, 2)
,@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName is NULL and @name is not null)
begin
raiserror(15253,-1,-1,@name)
return (1)
end
/* Copy info from sysprotects for processing */
IF charindex('O',@permissionarea) > 0
begin
/* Copy info for objects */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
/* 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level */
SELECT id
,case
when columns is null then '2Simpl'
else '1Regul'
end
,Null
,val1.name
,'Ob'
,val2.name
,columns
,user_name(objectproperty( id, 'ownerid' ))
,object_name(id)
,user_name(uid)
,user_name(grantor)
,case
when columns is null then '.'
else Null
end
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
and (@ObjectStatementName is null or object_name(id) = @ObjectStatementName)
and (@username is null or user_name(uid) = @username)
and (@grantorname is null or user_name(grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and val2.type = 'T' --T is overloaded.
and val2.number = sysp.protecttype
and sysp.id != 0
IF EXISTS (SELECT * From #t1_Prots)
begin
UPDATE #t1_Prots set ObjType = ob.xtype
FROM sysobjects ob
WHERE ob.id = #t1_Prots.Id
UPDATE #t1_Prots
set Max_ColId = (select max(colid) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code = '1Regul'
/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)
Set new_Bit_On accordinglly */
UPDATE #t1_Prots SET new_Bit_On =
CASE convert(int,substring(Columns_Orig,1,1)) & 1
WHEN 1 then 1
ELSE 0
END
WHERE ObjType <> 'V' and Type1Code = '1Regul'
/* Views don't get new columns */
UPDATE #t1_Prots set new_Bit_On = 0
WHERE ObjType = 'V'
/* Indicate enties where column level action pretains to all
columns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots set All_Col_Bits_On = 1
where #t1_Prots.Type1Code = '1Regul'
and not exists
(select *
from syscolumns sysc, master..spt_values v
where #t1_Prots.Id = sysc.id and sysc.colid = v.number
and v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean off
where first byte is 0 means off means off and on mean on */
case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v.high = 0)
/* Indicate entries where column level action pretains to
only some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots set All_Col_Bits_On = 0
WHERE #t1_Prots.Type1Code = '1Regul'
and All_Col_Bits_On is null
Update #t1_Prots
set ColumnName =
case
when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from #t1_Prots
where ObjType IN ('S ' ,'U ', 'V ')
and Type1Code = '1Regul'
and NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)
/* Expand and Insert individual column permission rows */
INSERT into #t1_Prots
(Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId )
SELECT prot1.Id
,'1Regul'
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,col_name ( prot1.Id ,val1.number )
,val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,syscolumns sysc
where prot1.ObjType IN ('S ' ,'U ' ,'V ')
and prot1.All_Col_Bits_On = 0
and prot1.Id = sysc.id
and val1.type = 'P'
and val1.number = sysc.colid
and
case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1.high <> 0
delete from #t1_Prots
where ObjType IN ('S ' ,'U ' ,'V ')
and All_Col_Bits_On = 0
and new_Bit_On = 0
end
end
/* Handle statement permissions here*/
IF (charindex('S',@permissionarea) > 0)
begin
/* All statement permissions are 2Simpl */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On )
SELECT id
,'2Simpl'
,Null
,val1.name
,'St'
,val2.name
,columns
,'.'
,'.'
,user_name(sysp.uid)
,user_name(sysp.grantor)
,'.'
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@username is null or user_name(sysp.uid) = @username)
and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and (@ObjectStatementName is null or val1.name = @ObjectStatementName)
and val2.number = sysp.protecttype
and val2.type = 'T'
and sysp.id = 0
end
IF NOT EXISTS (SELECT * From #t1_Prots)
begin
raiserror(15330,-1,-1)
return (1)
end
/* Calculate dynamic display col widths */
SELECT
@charMaxLenOwner =
convert ( varchar, max(datalength(OwnerName)))
,@charMaxLenObject =
convert ( varchar, max(datalength(ObjectName)))
,@charMaxLenGrantee =
convert ( varchar, max(datalength(GranteeName)))
,@charMaxLenGrantor =
convert ( varchar, max(datalength(GrantorName)))
,@charMaxLenAction =
convert ( varchar, max(datalength(ActionName)))
,@charMaxLenColumnName =
convert ( varchar, max(datalength(ColumnName)))
from #t1_Prots
/* Output the report */
EXECUTE(
'Set nocount off
SELECT ''Owner'' = ''GRANT '' + substring (ActionName ,1 ,' + @charMaxLenAction + ')+'' ON''
,''Object'' = substring (ObjectName ,1 ,' + @charMaxLenObject + ')
,''Grantee'' = '' TO '' +substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
from #t1_Prots
order by
ActionCategory
,Owner ,Object
,Grantee
,ColId --Multiple -123s ( <0 ) possible
Set nocount on'
)
Return (0) -- sp_helprotect
GO
Comentarios
Publicar un comentario