20070105 - MSSQL 2005 a uložené procedury v databázi master

Pro MSSQL 2005 byly upraveny následující procedury:

 

spav_dejpravarw

spav_dejpravarw_all_dbs

spav_dejpravarw_zak_Firmy

spav_odeberpravarw

spav_odeberpravarw_all_dbs

spav_odeberpravarw_zak_Firmy

 

Pokud připojíte databáze Varia z MSSQL 2000 k MSSQL 2005, je ke každému databázovému uživateli implicitně vytvořeno schéma shodné s názvem databázového uživatele. Po spuštění libovolné z výše uvedených procedur na nastavení oprávnění se potom zobrazí hlášení "The database principal owns a schema in the database, and cannot be dropped". Úprava spočívá v tom, že imlicitně vytvořená schemata jsou odstraněna a databázový uživatel může být bez problémů odstraněn. Aktuální verze procedur jsou k dispozici v adresáři SQL\MSSQL2005\procedury instalačního CD ne bo na stránce stažení software v souboru sql11.zip.

 

Pokud používáte procedury na nastavení oprávnění k zakázkovým databázím, měly by být upraveny následujícím způsobem (zvýrazněné řádky jsou přidány):

 

1) Číslované databáze - nastavení oprávnění:

 

CREATE procedure [dbo].[spav_dejpravarw_zak_DXXX]
  @cislofirmy int,
  @uzivatel varchar(128)
AS
set nocount on
declare @fmtData  varchar(4),
  @fmtArchiv  varchar(4),
  @counter  numeric,
  @Databaze varchar(128),
  @Data   varchar(132),
  @DataA    varchar(132),
  @StringData   varchar(1024),
  @StringDataA    varchar(1024)

select @Databaze = 'DEvi'  /*Zde uvedete jméno databáze zakázkového modulu*/
if @cislofirmy > 4999
  begin
    raiserror 13000 'Číslo firmy je moc velké. Zkuste menší než 4999.'
  End
Else
  begin
    select @fmtData=''
    select @fmtArchiv=cast(@cislofirmy + 5000 as varchar)
    select @counter=4-len(cast(@cislofirmy as varchar))
    while (@counter>0)
      begin
        select @fmtData=@fmtData + '0'
        select @counter=@counter-1
      End
    select @fmtData=@fmtData+cast(@cislofirmy as varchar)
    select @Data= @Databaze + @fmtData
    select @DataA= @Databaze + @fmtArchiv

 

    select @StringData  = 'USE ' + @Data  + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'
    select @StringDataA = 'USE ' + @DataA + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'

    if exists(select * from sysdatabases where [name] = @Data)
      execute (@StringData)
    if exists(select * from sysdatabases where [name] = @DataA)
      execute (@StringDataA)

 

    select @StringData  = 'USE ' + @Data   + ' if not exists(select sysusers.name, master.dbo.syslogins.name from sysusers inner join master.dbo.syslogins on sysusers.sid = master.dbo.syslogins.sid where sysusers.name = ''dbo'' AND master.dbo.syslogins.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sysusers where name = ''' + @uzivatel + ''') execute sp_revokedbaccess ''' + @uzivatel + ''' execute sp_grantdbaccess ''' + @uzivatel + ''' execute sp_addrolemember db_datareader, ''' + @uzivatel + ''' execute sp_addrolemember db_datawriter, ''' + @uzivatel + ''' END'
    select @StringDataA = 'USE ' + @DataA  + ' if not exists(select sysusers.name, master.dbo.syslogins.name from sysusers inner join master.dbo.syslogins on sysusers.sid = master.dbo.syslogins.sid where sysusers.name = ''dbo'' AND master.dbo.syslogins.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sysusers where name = ''' + @uzivatel + ''') execute sp_revokedbaccess ''' + @uzivatel + ''' execute sp_grantdbaccess ''' + @uzivatel + ''' execute sp_addrolemember db_datareader, ''' + @uzivatel + ''' execute sp_addrolemember db_datawriter, ''' + @uzivatel + ''' END'

    if exists(select * from sysdatabases where [name] = @Data)
      execute (@StringData)
    if exists(select * from sysdatabases where [name] = @DataA)
      execute (@StringDataA)
  End

 

2) Číslované databáze - odebrání oprávnění:

 

CREATE procedure [dbo].[spav_odeberpravarw_zak_DXXX]
  @cislofirmy int,
  @uzivatel varchar(128)
AS
set nocount on
declare @fmtData  varchar(4),
  @fmtArchiv  varchar(4),
  @counter  numeric,
  @Databaze varchar(4),
  @Data   varchar(8),
  @DataA    varchar(8),
  @StringData   varchar(1024),
  @StringDataA    varchar(1024)

select @Databaze = 'DXXX'  /*Zde uvedete jméno databáze zakázkového modulu*/
if @cislofirmy > 4999
  begin
    raiserror 13000 'Číslo firmy je moc velké. Zkuste menší než 4999.'
  End
Else
  begin
    select @fmtData=''
    select @fmtArchiv=cast(@cislofirmy + 5000 as varchar)
    select @counter=4-len(cast(@cislofirmy as varchar))
    while (@counter>0)
      begin
        select @fmtData=@fmtData + '0'
        select @counter=@counter-1
      End
    select @fmtData=@fmtData+cast(@cislofirmy as varchar)
    select @Data= @Databaze + @fmtData
    select @DataA= @Databaze + @fmtArchiv

 

    select @StringData  = 'USE ' + @Data  + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'
    select @StringDataA = 'USE ' + @DataA + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'

    if exists(select * from sysdatabases where [name] = @Data)
    execute (@StringData)
    if exists(select * from sysdatabases where [name] = @DataA)
    execute (@StringDataA)

 

    select @StringData  = 'USE ' + @Data  + ' if not exists(select sysusers.name, master.dbo.syslogins.name from sysusers inner join master.dbo.syslogins on sysusers.sid = master.dbo.syslogins.sid where sysusers.name = ''dbo'' AND master.dbo.syslogins.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sysusers where name = ''' + @uzivatel + ''') execute sp_revokedbaccess ''' + @uzivatel + ''' END'
    select @StringDataA = 'USE ' + @DataA + ' if not exists(select sysusers.name, master.dbo.syslogins.name from sysusers inner join master.dbo.syslogins on sysusers.sid = master.dbo.syslogins.sid where sysusers.name = ''dbo'' AND master.dbo.syslogins.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sysusers where name = ''' + @uzivatel + ''') execute sp_revokedbaccess ''' + @uzivatel + ''' END'

    if exists(select * from sysdatabases where [name] = @Data)
    execute (@StringData)
    if exists(select * from sysdatabases where [name] = @DataA)
    execute (@StringDataA)
  End

 

 

3) Nečíslované databáze - nastavení oprávnění:

 

CREATE procedure [dbo].[spav_dejpravarw_zak_Firmy]
  @cislofirmy int,
  @uzivatel varchar(128)
AS
set nocount on
declare @Databaze varchar(5),
@StringData   varchar(1024)

select @Databaze = 'Firmy'

select @StringData  = 'USE ' + @Databaze + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'

if exists(select * from sys.databases where [name] = @Databaze)
  execute (@StringData)

 

select @StringData  = 'USE ' + @Databaze + ' if not exists(select su.name, sl.name from sys.database_principals su inner join master.sys.server_principals sl on su.sid = sl.sid where su.name = ''dbo'' AND sl.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sys.database_principals where name = ''' + @uzivatel + ''') DROP USER [' + @uzivatel + '] CREATE USER [' + @uzivatel + '] execute sp_addrolemember db_datareader, ''' + @uzivatel + ''' execute sp_addrolemember db_datawriter, ''' + @uzivatel + ''' END'

if exists(select * from sys.databases where [name] = @Databaze)
  execute (@StringData)


 4) Nečíslované databáze - odebrání oprávnění:

 

CREATE procedure [dbo].[spav_odeberpravarw_zak_Firmy]
  @cislofirmy int,
  @uzivatel varchar(128)
AS
set nocount on
declare @Databaze varchar(5),
@StringData   varchar(1024)

select @Databaze = 'Firmy'  /*Zde uvedete jméno databáze zakázkového modulu*/

select @StringData  = 'USE ' + @Databaze + ' if exists (select * from sys.schemas where name = ''' + @uzivatel + ''') DROP SCHEMA [' + @uzivatel + ']'

  if exists(select * from sys.databases where [name] = @Databaze)
    execute (@StringData)

 

select @Databaze = 'Firmy'  /*Zde uvedete jméno databáze zakázkového modulu*/
select @StringData  = 'USE ' + @Databaze + ' if not exists(select su.name, sl.name from sys.database_principals su inner join master.sys.server_principals sl on su.sid = sl.sid where su.name = ''dbo'' AND sl.name = ''' + @uzivatel + ''') BEGIN if exists (select * from sys.database_principals where name = ''' + @uzivatel + ''') DROP USER [' + @uzivatel + '] END'

  if exists(select * from sys.databases where [name] = @Databaze)
    execute (@StringData)