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

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

spav_dejpravarw_zak_Firmy

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

    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)