Infecté par registry cleaner
Résolu/Fermé
loic42
Messages postés
26
Date d'inscription
mardi 20 mars 2007
Statut
Membre
Dernière intervention
22 mai 2009
-
20 mars 2007 à 17:53
Regis59 Messages postés 21143 Date d'inscription mardi 27 juin 2006 Statut Contributeur sécurité Dernière intervention 22 juin 2016 - 26 mars 2007 à 10:45
Regis59 Messages postés 21143 Date d'inscription mardi 27 juin 2006 Statut Contributeur sécurité Dernière intervention 22 juin 2016 - 26 mars 2007 à 10:45
A voir également:
- Infecté par registry cleaner
- Wise registry cleaner - Télécharger - Nettoyage
- Cleaner gratuit - Télécharger - Nettoyage
- Windows memory cleaner - Télécharger - Optimisation
- Duplicate cleaner free - Télécharger - Divers Utilitaires
- Wise disk cleaner - Télécharger - Nettoyage
24 réponses
loic42
Messages postés
26
Date d'inscription
mardi 20 mars 2007
Statut
Membre
Dernière intervention
22 mai 2009
25 mars 2007 à 01:36
25 mars 2007 à 01:36
je crois que tu as de la lecture qui t'attends...
Voila le fichier en question:
/*
** INSTCAT.SQL
** Installs catalog stored procedures on the Microsoft SQL Server.
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
**
** Owners:
**
** <owner current="true" primary="true">iliak</owner>
*/
/****************************************************************************/
/* This portion sets up the ability to perform all the functions in this */
/* script */
/****************************************************************************/
go
use master
go
dump tran master with no_log
go
set quoted_identifier on
go
if (exists (select * from sysobjects
where name = 'sp_configure' and type = 'P '))
begin
execute sp_configure 'update',1
end
reconfigure with override
go
exec sp_MS_upd_sysobj_category 1 /*Capture datetime for use below.*/
go
/*
** If old versions of tables exist, drop them.
*/
if (exists (select * from sysobjects
where name = 'MSdatatype_info' and type = 'U '))
drop table MSdatatype_info
go
if (exists (select * from sysobjects
where name = 'MSdatatype_info_ext' and type = 'U '))
drop table MSdatatype_info_ext
go
if (exists (select * from sysobjects
where name = 'MStable_types' and type = 'U '))
drop table MStable_types
go
if (exists (select * from sysobjects
where name = 'MSserver_info' and type = 'U '))
drop table MSserver_info
go
if (exists (select * from sysobjects
where name = 'spt_table_types' and type = 'U '))
drop table spt_table_types
go
/*
** If tables or procs already exist, drop them.
*/
if (exists (select * from sysobjects
where name = 'spt_datatype_info' and type = 'U '))
drop table spt_datatype_info
go
if (exists (select * from sysobjects
where name = 'spt_datatype_info_ext' and type = 'U '))
drop table spt_datatype_info_ext
go
if (exists (select * from sysobjects
where name = 'sp_add_server_sortinfo' and type = 'P '))
drop proc sp_add_server_sortinfo
go
if (exists (select * from sysobjects
where name = 'sp_add_server_sortinfo75' and type = 'P '))
drop proc sp_add_server_sortinfo75
go
if (exists (select * from sysobjects
where name = 'spt_server_info' and type = 'U '))
drop table spt_server_info
go
if (exists (select * from sysobjects
where name = 'sp_tables' and type = 'P '))
drop proc sp_tables
go
if (exists (select * from sysobjects
where name = 'sp_statistics' and type = 'P '))
drop proc sp_statistics
go
if (exists (select * from sysobjects
where name = 'sp_columns' and type = 'P '))
drop proc sp_columns
go
if (exists (select * from sysobjects
where name = 'sp_fkeys' and type = 'P '))
drop proc sp_fkeys
go
if (exists (select * from sysobjects
where name = 'sp_pkeys' and type = 'P '))
drop proc sp_pkeys
dump tran master with no_log
go
go
if (exists (select * from sysobjects
where name = 'sp_stored_procedures' and type = 'P '))
drop proc sp_stored_procedures
go
if (exists (select * from sysobjects
where name = 'sp_sproc_columns' and type = 'P '))
drop proc sp_sproc_columns
go
if (exists (select * from sysobjects
where name = 'sp_table_privileges' and type = 'P '))
drop proc sp_table_privileges
go
if (exists (select * from sysobjects
where name = 'sp_column_privileges' and type = 'P '))
drop proc sp_column_privileges
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'sp_server_info' and type = 'P '))
drop proc sp_server_info
go
if (exists (select * from sysobjects
where name = 'sp_datatype_info' and type = 'P '))
drop proc sp_datatype_info
go
if (exists (select * from sysobjects
where name = 'sp_special_columns' and type = 'P '))
drop proc sp_special_columns
go
if (exists (select * from sysobjects
where name = 'sp_databases' and type = 'P '))
drop proc sp_databases
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'sp_ddopen' and type = 'P '))
drop proc sp_ddopen
go
if (exists (select * from sysobjects
where name = 'sp_tableswc' and type = 'P '))
drop proc sp_tableswc
go
if (exists (select * from sysobjects
where name = 'sp_tablecollations' and type = 'P'))
drop proc sp_tablecollations
go
if (exists (select * from sysobjects
where name = 'sp_bcp_dbcmptlevel' and type = 'P'))
drop proc sp_bcp_dbcmptlevel
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'spt_provider_types' and type = 'U '))
begin
drop table spt_provider_types
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_catalogs_rowset' and type = 'P '))
begin
drop procedure sp_catalogs_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_column_privileges_rowset' and type = 'P '))
begin
drop procedure sp_column_privileges_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_columns_rowset' and type = 'P '))
begin
drop procedure sp_columns_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_check_constraints_rowset' and type = 'P '))
begin
drop procedure sp_check_constraints_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_check_constbytable_rowset' and type = 'P '))
begin
drop procedure sp_check_constbytable_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_foreign_keys_rowset' and type = 'P '))
begin
drop procedure sp_foreign_keys_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_indexes_rowset' and type = 'P '))
begin
drop procedure sp_indexes_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_primary_keys_rowset' and type = 'P '))
begin
drop procedure sp_primary_keys_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_procedure_params_rowset' and type = 'P '))
begin
drop procedure sp_procedure_params_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_procedures_rowset' and type = 'P '))
begin
drop procedure sp_procedures_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_provider_types_rowset' and type = 'P '))
begin
drop procedure sp_provider_types_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_schemata_rowset' and type = 'P '))
begin
drop procedure sp_schemata_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_statistics_rowset' and type = 'P '))
begin
drop procedure sp_statistics_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_rowset' and type = 'P '))
begin
drop procedure sp_tables_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_info_rowset' and type = 'P '))
begin
drop procedure sp_tables_info_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_info_rowset_64' and type = 'P '))
begin
drop procedure sp_tables_info_rowset_64
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_constraints_rowset' and type = 'P '))
begin
drop proc sp_table_constraints_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_privileges_rowset' and type = 'P '))
begin
drop proc sp_table_privileges_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_linkedservers_rowset' and type = 'P '))
begin
drop proc sp_linkedservers_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_statistics_rowset' and type = 'P '))
begin
drop proc sp_table_statistics_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_column_constraints' and type = 'P '))
begin
drop proc sp_oledb_column_constraints
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_indexinfo' and type = 'P '))
begin
drop proc sp_oledb_indexinfo
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_ro_usrname' and type = 'P '))
begin
drop proc sp_oledb_ro_usrname
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_deflang' and type = 'P '))
begin
drop proc sp_oledb_deflang
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_defdb' and type = 'P '))
begin
drop proc sp_oledb_defdb
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_database' and type = 'P '))
begin
drop proc sp_oledb_database
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_language' and type = 'P '))
begin
drop proc sp_oledb_language
dump tran master with no_log
end
go
print 'creating table spt_datatype_info_ext'
go
if (charindex('6.00', @@version) = 0 and
charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Pre 6.0 server */
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.0 SQL Server.'
print 'Ignore the following error.'
create table spt_datatype_info_ext (
user_type smallint not null,
CREATE_PARAMS varchar(32) null,
AUTO_INCREMENT smallint null,
typename varchar(32))
end
go
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* 6.0 or later server */
create table spt_datatype_info_ext (
user_type smallint not null,
CREATE_PARAMS varchar(32) null,
AUTO_INCREMENT smallint null,
typename sysname) /* from systypes, to avoid xusertype hard-code */
end
go
grant select on spt_datatype_info_ext to public
go
insert into spt_datatype_info_ext
/* CHAR user_type, create_params, auto_increment */
values (1, 'length' ,0, 'char')
insert into spt_datatype_info_ext
/* VARCHAR user_type, create_params, auto_increment */
values (2, 'max length' ,0, 'varchar')
insert into spt_datatype_info_ext
/* BINARY user_type, create_params, auto_increment */
values (3, 'length' ,0, 'binary')
insert into spt_datatype_info_ext
/* VARBINARY user_type, create_params, auto_increment */
values (4, 'max length' ,0, 'varbinary')
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
insert into spt_datatype_info_ext
/* DECIMAL user_type, create_params, auto_increment */
values (26, 'precision,scale' ,0, 'decimal')
insert into spt_datatype_info_ext
/* NUMERIC user_type, create_params, auto_increment */
values (25, 'precision,scale' ,0, 'numeric')
insert into spt_datatype_info_ext
/* DECIMAL IDENTITY user_type, create_params, auto_increment */
values (26, 'precision' ,1, 'decimal')
insert into spt_datatype_info_ext
/* NUMERIC IDENTITY user_type, create_params, auto_increment */
values (25, 'precision' ,1, 'numeric')
end
else /* Pre 6.0 server, add SYSNAME create param */
begin
insert into spt_datatype_info_ext
/* SYSNAME user_type, create_param, auto_increments */
values (18, 'max length' ,0, 'sysname')
end
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Update usertypes for 8.0 server */
begin tran
insert into spt_datatype_info_ext
/* NCHAR user_type, create_params, auto_increment */
values (0, 'length' ,0, 'nchar')
insert into spt_datatype_info_ext
/* NVARCHAR user_type, create_params, auto_increment */
values (0, 'max length' ,0, 'nvarchar')
/* SET user_type TO SPHINX VALUES */
update spt_datatype_info_ext set user_type = xusertype
from spt_datatype_info_ext e, systypes t where t.name = e.typename
commit tran
end
go
create unique clustered index datatypeinfoextclust on spt_datatype_info_ext(user_type,AUTO_INCREMENT)
go
print 'creating table spt_datatype_info'
go
if (charindex('6.00', @@version) = 0 and
charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Pre 6.0 server */
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.0 SQL Server.'
print 'Ignore the following error.'
create table spt_datatype_info (
ss_dtype tinyint not null,
fixlen int null, /* datatype len for variable, else null */
ODBCVer tinyint null, /* version if needed, else null */
TYPE_NAME varchar(32) not null,
DATA_TYPE smallint not null,
data_precision int null,
numeric_scale smallint null, /* min scale if 6.0 */
RADIX smallint null,
length int null,
LITERAL_PREFIX varchar(32) null,
LITERAL_SUFFIX varchar(32) null,
CREATE_PARAMS varchar(32) null,
NULLABLE smallint not null,
CASE_SENSITIVE smallint not null,
SEARCHABLE smallint not null,
UNSIGNED_ATTRIBUTE smallint null,
MONEY smallint not null,
AUTO_INCREMENT smallint null,
LOCAL_TYPE_NAME varchar(32) null,
charbin tinyint null, /* 0 for char/binary types, NULL for all others */
SQL_DATA_TYPE smallint not null,
SQL_DATETIME_SUB smallint null)
end
go
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* 6.0 or later server */
create table spt_datatype_info (
ss_dtype tinyint not null,
fixlen int null, /* datatype len for variable, else null */
ODBCVer tinyint null, /* version if needed, else null */
TYPE_NAME sysname not null,
DATA_TYPE smallint not null,
data_precision int null,
numeric_scale smallint null, /* min scale if 6.0 */
RADIX smallint null,
length int null,
LITERAL_PREFIX varchar(32) null,
LITERAL_SUFFIX varchar(32) null,
CREATE_PARAMS varchar(32) null,
NULLABLE smallint not null,
CASE_SENSITIVE smallint not null,
SEARCHABLE smallint not null,
UNSIGNED_ATTRIBUTE smallint null,
MONEY smallint not null,
AUTO_INCREMENT smallint null,
LOCAL_TYPE_NAME sysname null,
charbin tinyint null, /* 0 for char/binary types, NULL for all others */
SQL_DATA_TYPE smallint not null,
SQL_DATETIME_SUB smallint null)
end
go
grant select on spt_datatype_info to public
go
/* Get case sensitivity */
if 'A' = 'A' /* create dummy begin block */
begin
declare @case smallint
begin tran
select @case = 0
select @case = 1 where 'a' <> 'A'
/* Local Binary */
insert into spt_datatype_info values
(45,null,null,'binary',-2,null,null,null,null,'0x',null,'length',1,0,2,null,0,null,'binary',0,-2,null)
/* Local Bit */
insert into spt_datatype_info values
(50,null,null,'bit',-7,1,0,null,1,null,null,null,0,0,2,null,0,null,'bit',null,-7,null)
/* Local Char */
insert into spt_datatype_info values
(47,null,null,'char',1,null,null,null,null,'''','''','length',1,@case,3,null,0,null,'char',0,1,null)
/* Local Datetime */
insert into spt_datatype_info values
(61,8,2,'datetime',11,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
insert into spt_datatype_info values
(61,8,3,'datetime',93,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
/* Local Smalldatetime */
insert into spt_datatype_info values
(58,4,2,'smalldatetime',11,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values
(58,4,3,'smalldatetime',93,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
/* Local Float */
insert into spt_datatype_info values
(62,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
insert into spt_datatype_info values
(62,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
/* Local Real */
insert into spt_datatype_info values
(59,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values
(59,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
/* Local Smallmoney */
insert into spt_datatype_info values
(122,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
/* Local Money */
insert into spt_datatype_info values
(60,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)
/* Local Int */
insert into spt_datatype_info values
(56,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
commit tran
end
go
if 'A' = 'A' /* create dummy begin block */
begin
declare @case smallint
begin tran
select @case = 0
select @case = 1 where 'a' <> 'A'
/* Local Smallint */
insert into spt_datatype_info values
(52,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
insert into spt_datatype_info values
(52,2,1,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
/* Local Tinyint */
insert into spt_datatype_info values
(48,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)
/* Local Text */
insert into spt_datatype_info values
(35,null,null,'text',-1,2147483647,null,null,2147483647,'''','''',null,1,@case,1,null,0,null,'text',0,-1,null)
/* Local Varbinary */
insert into spt_datatype_info values
(37,null,null,'varbinary',-3,null,null,null,null,'0x',null,'max length',1,0,2,null,0,null,'varbinary',0,-3,null)
/* Local Varchar */
insert into spt_datatype_info values
(39,null,null,'varchar',12,null,null,null,null,'''','''','max length',1,@case,3,null,0,null,'varchar',0,12,null)
/* Local Image */
insert into spt_datatype_info values
(34,null,null,'image',-4,2147483647,null,null,2147483647,'0x',null,null,1,0,0,null,0,null,'image',0,-4,null)
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
/* Local Decimal */
insert into spt_datatype_info values /* sql server type is 'decimaln' */
(55,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)
/* Local Numeric */
insert into spt_datatype_info values /* sql server type is 'numericn' */
(63,null,null,'numeric',2 ,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)
/* Identity attribute data types */
/* Identity Int */
insert into spt_datatype_info values
(56,null,null,'int identity',4,10,0,10,null,null,null,null,0,0,2,0,0,1,'int identity',null,4,null)
/* Identity Smallint */
insert into spt_datatype_info values
(52,null,null,'smallint identity',5,5,0,10,null,null,null,null,0,0,2,0,0,1,'smallint identity',null,5,null)
/* Identity Tinyint */
insert into spt_datatype_info values
(48,null,null,'tinyint identity',-6,3,0,10,null,null,null,null,0,0,2,1,0,1,'tinyint identity',null,-6,null)
/* Identity Numeric */
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(55,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)
/* Identity Numeric */
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(63,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
end
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Add nullable type for non-8.0 server */
/* Local Datetimn */
insert into spt_datatype_info values
(111,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values
(111,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values /* sql server type is 'datetimn' */
(111,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
insert into spt_datatype_info values
(111,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
/* Local Floatn */
insert into spt_datatype_info values /* sql server type is 'floatn' */
(109,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values
(109,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values /* sql server type is 'floatn' */
(109,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
insert into spt_datatype_info values
(109,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
/* Local Moneyn */
insert into spt_datatype_info values /* sql server type is 'moneyn' */
(110,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
insert into spt_datatype_info values /* sql server type is 'moneyn' */
(110,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)
/* Local Intn */
insert into spt_datatype_info values /* sql server type is 'intn' */
(38,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
insert into spt_datatype_info values /* sql server type is 'intn' */
(38,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
insert into spt_datatype_info values
(38,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
/* Local Decimaln */
insert into spt_datatype_info values /* sql server type is 'decimaln' */
(106,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(106,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)
/* Local Numericn */
insert into spt_datatype_info values /* sql server type is 'numericn' */
(108,null,null,'numeric',2,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(108,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
end
end
commit tran
end
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin
declare @ncase smallint
select @ncase = 0
select @ncase = 1 where N'a' <> N'A'
/* Local Timestamp */
insert into spt_datatype_info values
(0,null,null,'timestamp',-2,8,null,null,null,'0x',null,null,1,0,2,null,0,null,'timestamp',0,-2,null)
/* Local GUID */
insert into spt_datatype_info values
(0,null,null,'uniqueidentifier',-11,36,null,null,null,'''','''',null,1,0,2,null,0,null,'uniqueidentifier',NULL,-11,null)
/* Local NChar */
insert into spt_datatype_info values
(0,null,null,'nchar',-8,null,null,null,null,'N''','''','length',1,@ncase,3,null,0,null,'nchar',0,-8,null)
/* Local NVarchar */
insert into spt_datatype_info values
(0,null,null,'nvarchar',-9,null,null,null,null,'N''','''','max length',1,@ncase,3,null,0,null,'nvarchar',0,-9,null)
/* Local NText */
insert into spt_datatype_info values
(0,null,null,'ntext',-10,2147483646,null,null,2147483646,'N''','''',null,1,@ncase,1,null,0,null,'ntext',0,-10,null)
if (charindex('8.00', @@version) > 0)
begin
-- /* Local BIGINT */
insert into spt_datatype_info values
(127,8,null,'bigint',-5,19,0,10,null,null,null,null,1,0,2,0,0,0,'bigint',null,-5,null)
-- /* Identity BIGINT */
insert into spt_datatype_info values
(127,8,null,'bigint identity',-5,19,0,10,null,null,null,null,0,0,2,0,0,1,'bigint identity',null,-5,null)
-- /* sql_variant */
insert into spt_datatype_info values
(
98, --ss_dtype
null, --fixlen
null, --ODBCVer
'sql_variant', --TYPE_NAME
-150, --SQL DATA TYPE
8000, --data_precision
0, --numeric_scale
10, --RADIX
8000, --length
null, --PREFIX
null, --SUFFIX
null, --Create Params
1, --Nullable
0, --Case sensitive
2, --Searchable
null, --UNSIGNED_ATTRIBUTE
0, --MONEY
null, --AUTO_INCREMENT
'sql_variant', --LOCAL TYPE NAME
0, --CHARBIN
-150, --SQL_DATA_TYPE
null --SQL_DATETIME_SUB
)
end
update spt_datatype_info set NULLABLE = 1
where TYPE_NAME = 'bit'
update spt_datatype_info set ss_dtype = isnull((select distinct xtype from systypes
where TYPE_NAME like name+'%'),0)
end
go
create unique clustered index datatypeinfoclust on spt_datatype_info(ss_dtype,fixlen,ODBCVer,AUTO_INCREMENT)
go
dump tran master with no_log
go
print 'creating table spt_server_info'
go
create table spt_server_info (
attribute_id int NOT NULL,
attribute_name varchar(60) NOT NULL,
attribute_value varchar(255) NOT NULL)
go
create unique clustered index serverinfoclust on spt_server_info(attribute_id)
go
if (charindex('7.00', @@version) = 0 and charindex('8.00', @@version) = 0)
begin
drop procedure sp_add_server_sortinfo /* not used by other servers */
drop procedure sp_add_server_sortinfo75 /* not used by older servers */
dump tran master with no_log
end
go
insert into spt_server_info
values (1,'DBMS_NAME','Microsoft SQL Server')
insert into spt_server_info
values (2,'DBMS_VER',@@version)
insert into spt_server_info
values (10,'OWNER_TERM','owner')
insert into spt_server_info
values (11,'TABLE_TERM','table')
insert into spt_server_info
values (12,'MAX_OWNER_NAME_LENGTH','30')
insert into spt_server_info
values (13,'TABLE_LENGTH','30')
insert into spt_server_info
values (14,'MAX_QUAL_LENGTH','30')
insert into spt_server_info
values (15,'COLUMN_LENGTH','30')
if 'A' = 'a' /* If not case sensitive server */
begin
insert into spt_server_info
values (16,'IDENTIFIER_CASE','MIXED')
end
else
begin
insert into spt_server_info
values (16,'IDENTIFIER_CASE','SENSITIVE')
end
insert into spt_server_info
values (17,'TX_ISOLATION','2')
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0)
begin /* Add 6.0 collation sequence */
insert into spt_server_info
select 18,'COLLATION_SEQ',
'charset='+t2.name+' sort_order='+t1.name
+' charset_num='+rtrim(convert(char(4),t1.csid))+
' sort_order_num='+rtrim(convert(char(4),t1.id))
from syscharsets t1, syscharsets t2, sysconfigures t3
where t1.csid=t2.id and t1.id=t3.value and t3.config=1123
end
else
begin /* Add 4.2x collation sequence */
insert into spt_server_info
select 18,'COLLATION_SEQ',
'charset='+t2.name+' sort_order='+t1.name
+' charset_num='+rtrim(convert(char(4),t1.csid))+
' sort_order_num='+rtrim(convert(char(4),t1.id))
from syscharsets t1, syscharsets t2, sysconfigures t3
where t1.csid=t2.id and t1.id=t3.value and t3.config=123
end
insert into spt_server_info
values (19,'SAVEPOINT_SUPPORT','Y')
insert into spt_server_info
values (20,'MULTI_RESULT_SETS','Y')
insert into spt_server_info
values (22,'ACCESSIBLE_TABLES','Y')
insert into spt_server_info
values (100,'USERID_LENGTH','30')
insert into spt_server_info
values (101,'QUALIFIER_TERM','database')
insert into spt_server_info
values (102,'NAMED_TRANSACTIONS','Y')
insert into spt_server_info
values (103,'SPROC_AS_LANGUAGE','Y')
insert into spt_server_info
values (104,'ACCESSIBLE_SPROC','Y')
insert into spt_server_info
values (105,'MAX_INDEX_COLS','16')
insert into spt_server_info
values (106,'RENAME_TABLE','Y')
insert into spt_server_info
values (107,'RENAME_COLUMN','Y')
if (charindex('8.00', @@version) > 0)
begin
/* Columns may be dropped on 8.0 or later */
insert into spt_server_info
values (108,'DROP_COLUMN','Y')
end
else
begin
insert into spt_server_info
values (108,'DROP_COLUMN','N')
end
if (charindex('8.00', @@version) > 0)
begin
/* Columns size may be changed on 8.0 or later */
insert into spt_server_info
values (109,'INCREASE_COLUMN_LENGTH','Y')
end
else
begin
insert into spt_server_info
values (109,'INCREASE_COLUMN_LENGTH','N')
end
if (charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
insert into spt_server_info
values (110,'DDL_IN_TRANSACTION','N')
end
else
begin
insert into spt_server_info
values (110,'DDL_IN_TRANSACTION','Y')
end
if (charindex('8.00', @@version) > 0)
begin
/* Descending indexes allowed on 8.0 or later */
insert into spt_server_info
values (111,'DESCENDING_INDEXES','Y')
end
else
begin
insert into spt_server_info
values (111,'DESCENDING_INDEXES','N')
end
insert into spt_server_info
values (112,'SP_RENAME','Y')
insert into spt_server_info
values (113,'REMOTE_SPROC','Y')
insert into spt_server_info
values (500,'SYS_SPROC_VERSION',convert(varchar(9), serverproperty ('ProductVersion')))
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Update values for 8.0 server */
update spt_server_info set attribute_value = '128'
where attribute_id in (12,13,14,15,100)
end
go
grant select on spt_server_info to public
go
print 'creating sp_column_privileges'
go
/* Procedure for pre 6.50 server */
CREATE PROCEDURE sp_column_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(96) = null) /* 3*32 */
as
set nocount on
declare @table_id int
DECLARE @full_table_name varchar(65) /* 2*32 + 1 */
declare @low smallint /* range of userids to check */
declare @high smallint
declare @owner_uid smallint
select @low = 0, @high = 32767
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror 20001 '~~Rush_5~~'
return
end
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = @table_name
end
else
begin /* Qualified table name */
SELECT @full_table_name = @table_owner + '.' + @table_name
end
/* Get Object ID */
select @table_id = object_id(@full_table_name)
if (@@trancount <> 0)
begin /* If inside a transaction */
raiserror 20003 '~~Rush_6~~'
return
end
/*
** We need to create a table which will contain a row for every row to
** be returned to the client.
*/
create table #column_priv1(
COLUMN_NAME varchar(32) NOT NULL,
grantor smallint NOT NULL,
grantee smallint NOT NULL,
select_privilege bit,
select_grantable bit,
insert_privilege bit,
insert_grantable bit,
update_privilege bit,
update_grantable bit,
references_privilege bit,
references_grantable bit,
uid smallint NOT NULL,
gid smallint NOT NULL)
/*
** insert a row for the table owner (who has all permissions)
*/
select @owner_uid = (
select uid
from sysobjects
where id = @table_id)
if (charindex('6.00', @@version) > 0)
begin
insert into #column_priv1
select
c.name,
u.uid,
@owner_uid,
0,
1,
0,
1,
0,
1,
0,
1,
@owner_uid,
0
from syscolumns c, sysusers u
where id = @table_id
and c.number = 0
and u.uid = 1 /* grantor is dbo of database */
end
else
begin
insert into #column_priv1
select
c.name,
u.uid,
@owner_uid,
0,
1,
0,
1,
0,
1,
0,
0,
@owner_uid,
0
from syscolumns c, sysusers u
where id = @table_id
and c.number = 0
and u.uid = 1 /* grantor is dbo of database */
end
/*
** now stick in a row for every column for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/
insert into #column_priv1
select distinct
c.name,
o.uid,
u.uid,
0,
0,
0,
0,
0,
0,
0,
0,
u.uid,
u.gid
from sysusers u, syscolumns c, sysobjects o
where o.id = @table_id
and c.id = o.id
and c.number = 0
and u.gid <> u.uid
and u.uid <> @owner_uid
/*
** we need to create another temporary table to contain all the various
** protection information for the table in question
*/
create table #protects (
uid smallint NOT NULL,
grantor smallint NOT NULL,
action tinyint NOT NULL,
protecttype tinyint NOT NULL,
name varchar(32) NOT NULL)
insert into #protects
select
p.uid,
p.uid,
p.action,
p.protecttype,
isnull(col_name(id, c.number), '~All')
from
sysprotects p,
master.dbo.spt_values c,
master.dbo.spt_values a,
master.dbo.spt_values b
where
convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
& c.high <> 0
and c.number <= (
select count(*)
from syscolumns
where id = @table_id)
and c.type = 'P'
and a.type = 'T'
and a.number = p.action
and p.action in (193,195,197,26)
and b.type = 'T'
and b.number = p.protecttype
and p.id = @table_id
and p.uid between @low and @high
update #column_priv1
set select_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 193
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 193
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set insert_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 195
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 195
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set update_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 197
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where protecttype = 206
and action = 197
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set references_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 26
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where protecttype = 206
and action = 26
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set select_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 193
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 193
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set insert_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 195
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 195
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set update_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 197
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 197
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set references_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 26
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 26
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
create table #column_priv2(
COLUMN_NAME varchar(32) NOT NULL,
grantor smallint NULL,
grantee smallint NOT NULL,
PRIVILEGE varchar(32) NOT NULL,
IS_GRANTABLE varchar(3) NULL)
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'SELECT',
'NO'
from #column_priv1
where select_privilege = 1 and select_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'INSERT',
'NO'
from #column_priv1
where insert_privilege = 1 and insert_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'UPDATE',
'NO'
from #column_priv1
where update_privilege = 1 and update_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'REFERENCES',
'NO'
from #column_priv1
where references_privilege = 1 and references_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'SELECT',
'YES'
from #column_priv1
where select_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'INSERT',
'YES'
from #column_priv1
where insert_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'UPDATE',
'YES'
from #column_priv1
where update_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'REFERENCES',
'YES'
from #column_priv1
where references_grantable = 1
select
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(@owner_uid)) TABLE_OWNER,
@table_name TABLE_NAME,
COLUMN_NAME,
convert(varchar(32),user_name(grantor)) GRANTOR,
convert(varchar(32),user_name(grantee)) GRANTEE,
PRIVILEGE,
IS_GRANTABLE
from #column_priv2
where COLUMN_NAME like @column_name
order by 4, 7
go
if (charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.50 SQL Server.'
print 'Ignore the following errors.'
end
else
drop proc sp_column_privileges
go
/* Procedure for 6.50 server */
CREATE PROCEDURE sp_column_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(96) = null) /* 3*32 */
as
declare @table_id int
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin /* If unqualified table name */
select @table_id = object_id(@table_name)
end
else
begin /* Qualified table name */
select @table_id = object_id(@table_owner + '.' + @table_name)
end
select
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(varchar(32),c.name) COLUMN_NAME,
convert(varchar(32),user_name(p.grantor)) GRANTOR,
convert(varchar(32),user_name(u.uid)) GRANTEE,
convert (varchar(32),case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert (varchar(3),case when p.protecttype = 205 then 'NO'
else 'YES'
end) IS_GRANTABLE
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c
where
c.id = @table_id
and c.name like @column_name
and c.id = p.id
and c.id = o.id
and case substring(p.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p.columns, v.low, 1))
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0 /* permission applies to this column */
and v.number <= (select count(*) from syscolumns
where id = @table_id) /* ranges from 1 to # of columns in table */
and v.type = 'P'
and v.number = c.colid
/* expand groups */
and ((p.uid = u.uid and u.uid <> u.gid) or
(p.uid = u.gid and u.uid <> u.gid))
and p.protecttype <> 206 /* only grant rows */
and p.action in (26,193,195,197)
and o.uid <> u.uid /* no rows for owner */
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = p.action
and p1.id = p.id
and p1.uid = u.uid
and case substring(p1.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0) /* permission applies to this column */
union
select /* Add rows for table owner */
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(varchar(32),col_name(@table_id, c.colid)) COLUMN_NAME,
convert(varchar(32),user_name(u.uid)) grantor,
convert(varchar(32),user_name(o.uid)) grantee,
convert (varchar(32),case v.number
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert(varchar(3),'YES') IS_GRANTABLE
from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c
where
c.id = @table_id
and c.name like @column_name
and c.id = o.id
and u.uid = 1 /* grantor is dbo of database */
and v.type = 'P' /* cross product to get all exposed privileges */
and v.number in (26,193,195,197)
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = v.number
and p1.id = o.id
and p1.uid = o.uid)
order by 4, 7
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
else
drop proc sp_column_privileges
go
/* Procedure for 8.0 server */
CREATE PROCEDURE sp_column_privileges (
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null,
@column_name nvarchar(384) = null) /* 3*128 */
as
declare @table_id int
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin /* If unqualified table name */
select @table_id = object_id(quotename(@table_name))
end
else
begin /* Qualified table name */
if @table_owner = N''
begin /* If empty owner name */
select @table_id = 0
end
else
begin
select @table_id = object_id(quotename(@table_owner) +
'.' + quotename(@table_name))
end
end
select
convert(sysname,db_name()) TABLE_QUALIFIER,
convert(sysname,user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(sysname,c.name) COLUMN_NAME,
convert(sysname,user_name(p.grantor)) GRANTOR,
convert(sysname,user_name(u.uid)) GRANTEE,
convert (varchar(32),case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert (varchar(3),case when p.protecttype = 205 then 'NO'
else 'YES'
end) IS_GRANTABLE
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c, sysmembers m
where
c.id = @table_id
and c.name like @column_name
and c.id = p.id
and c.id = o.id
and case substring(p.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p.columns, v.low, 1))
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0 /* permission applies to this column */
and v.number <= (select count(*) from syscolumns
where id = @table_id) /* ranges from 1 to # of columns in table */
and v.type = N'P'
and v.number = c.colid
/* expand groups - AKUNDONE: only 1 level of group unrolling here. Need more?? */
and (u.uid > 0 and u.uid < 16384)
and ((p.uid = u.uid) or
(p.uid = m.groupuid and u.uid = m.memberuid))
and p.protecttype <> 206 /* only grant rows */
and p.action in (26,193,195,197)
and o.uid <> u.uid /* no rows for owner */
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = p.action
and p1.id = p.id
and p1.uid = u.uid
and case substring(p1.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
Voila le fichier en question:
/*
** INSTCAT.SQL
** Installs catalog stored procedures on the Microsoft SQL Server.
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
**
** Owners:
**
** <owner current="true" primary="true">iliak</owner>
*/
/****************************************************************************/
/* This portion sets up the ability to perform all the functions in this */
/* script */
/****************************************************************************/
go
use master
go
dump tran master with no_log
go
set quoted_identifier on
go
if (exists (select * from sysobjects
where name = 'sp_configure' and type = 'P '))
begin
execute sp_configure 'update',1
end
reconfigure with override
go
exec sp_MS_upd_sysobj_category 1 /*Capture datetime for use below.*/
go
/*
** If old versions of tables exist, drop them.
*/
if (exists (select * from sysobjects
where name = 'MSdatatype_info' and type = 'U '))
drop table MSdatatype_info
go
if (exists (select * from sysobjects
where name = 'MSdatatype_info_ext' and type = 'U '))
drop table MSdatatype_info_ext
go
if (exists (select * from sysobjects
where name = 'MStable_types' and type = 'U '))
drop table MStable_types
go
if (exists (select * from sysobjects
where name = 'MSserver_info' and type = 'U '))
drop table MSserver_info
go
if (exists (select * from sysobjects
where name = 'spt_table_types' and type = 'U '))
drop table spt_table_types
go
/*
** If tables or procs already exist, drop them.
*/
if (exists (select * from sysobjects
where name = 'spt_datatype_info' and type = 'U '))
drop table spt_datatype_info
go
if (exists (select * from sysobjects
where name = 'spt_datatype_info_ext' and type = 'U '))
drop table spt_datatype_info_ext
go
if (exists (select * from sysobjects
where name = 'sp_add_server_sortinfo' and type = 'P '))
drop proc sp_add_server_sortinfo
go
if (exists (select * from sysobjects
where name = 'sp_add_server_sortinfo75' and type = 'P '))
drop proc sp_add_server_sortinfo75
go
if (exists (select * from sysobjects
where name = 'spt_server_info' and type = 'U '))
drop table spt_server_info
go
if (exists (select * from sysobjects
where name = 'sp_tables' and type = 'P '))
drop proc sp_tables
go
if (exists (select * from sysobjects
where name = 'sp_statistics' and type = 'P '))
drop proc sp_statistics
go
if (exists (select * from sysobjects
where name = 'sp_columns' and type = 'P '))
drop proc sp_columns
go
if (exists (select * from sysobjects
where name = 'sp_fkeys' and type = 'P '))
drop proc sp_fkeys
go
if (exists (select * from sysobjects
where name = 'sp_pkeys' and type = 'P '))
drop proc sp_pkeys
dump tran master with no_log
go
go
if (exists (select * from sysobjects
where name = 'sp_stored_procedures' and type = 'P '))
drop proc sp_stored_procedures
go
if (exists (select * from sysobjects
where name = 'sp_sproc_columns' and type = 'P '))
drop proc sp_sproc_columns
go
if (exists (select * from sysobjects
where name = 'sp_table_privileges' and type = 'P '))
drop proc sp_table_privileges
go
if (exists (select * from sysobjects
where name = 'sp_column_privileges' and type = 'P '))
drop proc sp_column_privileges
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'sp_server_info' and type = 'P '))
drop proc sp_server_info
go
if (exists (select * from sysobjects
where name = 'sp_datatype_info' and type = 'P '))
drop proc sp_datatype_info
go
if (exists (select * from sysobjects
where name = 'sp_special_columns' and type = 'P '))
drop proc sp_special_columns
go
if (exists (select * from sysobjects
where name = 'sp_databases' and type = 'P '))
drop proc sp_databases
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'sp_ddopen' and type = 'P '))
drop proc sp_ddopen
go
if (exists (select * from sysobjects
where name = 'sp_tableswc' and type = 'P '))
drop proc sp_tableswc
go
if (exists (select * from sysobjects
where name = 'sp_tablecollations' and type = 'P'))
drop proc sp_tablecollations
go
if (exists (select * from sysobjects
where name = 'sp_bcp_dbcmptlevel' and type = 'P'))
drop proc sp_bcp_dbcmptlevel
go
dump tran master with no_log
go
if (exists (select * from sysobjects
where name = 'spt_provider_types' and type = 'U '))
begin
drop table spt_provider_types
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_catalogs_rowset' and type = 'P '))
begin
drop procedure sp_catalogs_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_column_privileges_rowset' and type = 'P '))
begin
drop procedure sp_column_privileges_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_columns_rowset' and type = 'P '))
begin
drop procedure sp_columns_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_check_constraints_rowset' and type = 'P '))
begin
drop procedure sp_check_constraints_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_check_constbytable_rowset' and type = 'P '))
begin
drop procedure sp_check_constbytable_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_foreign_keys_rowset' and type = 'P '))
begin
drop procedure sp_foreign_keys_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_indexes_rowset' and type = 'P '))
begin
drop procedure sp_indexes_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_primary_keys_rowset' and type = 'P '))
begin
drop procedure sp_primary_keys_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_procedure_params_rowset' and type = 'P '))
begin
drop procedure sp_procedure_params_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_procedures_rowset' and type = 'P '))
begin
drop procedure sp_procedures_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_provider_types_rowset' and type = 'P '))
begin
drop procedure sp_provider_types_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_schemata_rowset' and type = 'P '))
begin
drop procedure sp_schemata_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_statistics_rowset' and type = 'P '))
begin
drop procedure sp_statistics_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_rowset' and type = 'P '))
begin
drop procedure sp_tables_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_info_rowset' and type = 'P '))
begin
drop procedure sp_tables_info_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_tables_info_rowset_64' and type = 'P '))
begin
drop procedure sp_tables_info_rowset_64
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_constraints_rowset' and type = 'P '))
begin
drop proc sp_table_constraints_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_privileges_rowset' and type = 'P '))
begin
drop proc sp_table_privileges_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_linkedservers_rowset' and type = 'P '))
begin
drop proc sp_linkedservers_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_table_statistics_rowset' and type = 'P '))
begin
drop proc sp_table_statistics_rowset
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_column_constraints' and type = 'P '))
begin
drop proc sp_oledb_column_constraints
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_indexinfo' and type = 'P '))
begin
drop proc sp_oledb_indexinfo
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_ro_usrname' and type = 'P '))
begin
drop proc sp_oledb_ro_usrname
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_deflang' and type = 'P '))
begin
drop proc sp_oledb_deflang
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_defdb' and type = 'P '))
begin
drop proc sp_oledb_defdb
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_database' and type = 'P '))
begin
drop proc sp_oledb_database
dump tran master with no_log
end
go
if (exists (select * from sysobjects
where name = 'sp_oledb_language' and type = 'P '))
begin
drop proc sp_oledb_language
dump tran master with no_log
end
go
print 'creating table spt_datatype_info_ext'
go
if (charindex('6.00', @@version) = 0 and
charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Pre 6.0 server */
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.0 SQL Server.'
print 'Ignore the following error.'
create table spt_datatype_info_ext (
user_type smallint not null,
CREATE_PARAMS varchar(32) null,
AUTO_INCREMENT smallint null,
typename varchar(32))
end
go
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* 6.0 or later server */
create table spt_datatype_info_ext (
user_type smallint not null,
CREATE_PARAMS varchar(32) null,
AUTO_INCREMENT smallint null,
typename sysname) /* from systypes, to avoid xusertype hard-code */
end
go
grant select on spt_datatype_info_ext to public
go
insert into spt_datatype_info_ext
/* CHAR user_type, create_params, auto_increment */
values (1, 'length' ,0, 'char')
insert into spt_datatype_info_ext
/* VARCHAR user_type, create_params, auto_increment */
values (2, 'max length' ,0, 'varchar')
insert into spt_datatype_info_ext
/* BINARY user_type, create_params, auto_increment */
values (3, 'length' ,0, 'binary')
insert into spt_datatype_info_ext
/* VARBINARY user_type, create_params, auto_increment */
values (4, 'max length' ,0, 'varbinary')
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
insert into spt_datatype_info_ext
/* DECIMAL user_type, create_params, auto_increment */
values (26, 'precision,scale' ,0, 'decimal')
insert into spt_datatype_info_ext
/* NUMERIC user_type, create_params, auto_increment */
values (25, 'precision,scale' ,0, 'numeric')
insert into spt_datatype_info_ext
/* DECIMAL IDENTITY user_type, create_params, auto_increment */
values (26, 'precision' ,1, 'decimal')
insert into spt_datatype_info_ext
/* NUMERIC IDENTITY user_type, create_params, auto_increment */
values (25, 'precision' ,1, 'numeric')
end
else /* Pre 6.0 server, add SYSNAME create param */
begin
insert into spt_datatype_info_ext
/* SYSNAME user_type, create_param, auto_increments */
values (18, 'max length' ,0, 'sysname')
end
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Update usertypes for 8.0 server */
begin tran
insert into spt_datatype_info_ext
/* NCHAR user_type, create_params, auto_increment */
values (0, 'length' ,0, 'nchar')
insert into spt_datatype_info_ext
/* NVARCHAR user_type, create_params, auto_increment */
values (0, 'max length' ,0, 'nvarchar')
/* SET user_type TO SPHINX VALUES */
update spt_datatype_info_ext set user_type = xusertype
from spt_datatype_info_ext e, systypes t where t.name = e.typename
commit tran
end
go
create unique clustered index datatypeinfoextclust on spt_datatype_info_ext(user_type,AUTO_INCREMENT)
go
print 'creating table spt_datatype_info'
go
if (charindex('6.00', @@version) = 0 and
charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Pre 6.0 server */
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.0 SQL Server.'
print 'Ignore the following error.'
create table spt_datatype_info (
ss_dtype tinyint not null,
fixlen int null, /* datatype len for variable, else null */
ODBCVer tinyint null, /* version if needed, else null */
TYPE_NAME varchar(32) not null,
DATA_TYPE smallint not null,
data_precision int null,
numeric_scale smallint null, /* min scale if 6.0 */
RADIX smallint null,
length int null,
LITERAL_PREFIX varchar(32) null,
LITERAL_SUFFIX varchar(32) null,
CREATE_PARAMS varchar(32) null,
NULLABLE smallint not null,
CASE_SENSITIVE smallint not null,
SEARCHABLE smallint not null,
UNSIGNED_ATTRIBUTE smallint null,
MONEY smallint not null,
AUTO_INCREMENT smallint null,
LOCAL_TYPE_NAME varchar(32) null,
charbin tinyint null, /* 0 for char/binary types, NULL for all others */
SQL_DATA_TYPE smallint not null,
SQL_DATETIME_SUB smallint null)
end
go
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* 6.0 or later server */
create table spt_datatype_info (
ss_dtype tinyint not null,
fixlen int null, /* datatype len for variable, else null */
ODBCVer tinyint null, /* version if needed, else null */
TYPE_NAME sysname not null,
DATA_TYPE smallint not null,
data_precision int null,
numeric_scale smallint null, /* min scale if 6.0 */
RADIX smallint null,
length int null,
LITERAL_PREFIX varchar(32) null,
LITERAL_SUFFIX varchar(32) null,
CREATE_PARAMS varchar(32) null,
NULLABLE smallint not null,
CASE_SENSITIVE smallint not null,
SEARCHABLE smallint not null,
UNSIGNED_ATTRIBUTE smallint null,
MONEY smallint not null,
AUTO_INCREMENT smallint null,
LOCAL_TYPE_NAME sysname null,
charbin tinyint null, /* 0 for char/binary types, NULL for all others */
SQL_DATA_TYPE smallint not null,
SQL_DATETIME_SUB smallint null)
end
go
grant select on spt_datatype_info to public
go
/* Get case sensitivity */
if 'A' = 'A' /* create dummy begin block */
begin
declare @case smallint
begin tran
select @case = 0
select @case = 1 where 'a' <> 'A'
/* Local Binary */
insert into spt_datatype_info values
(45,null,null,'binary',-2,null,null,null,null,'0x',null,'length',1,0,2,null,0,null,'binary',0,-2,null)
/* Local Bit */
insert into spt_datatype_info values
(50,null,null,'bit',-7,1,0,null,1,null,null,null,0,0,2,null,0,null,'bit',null,-7,null)
/* Local Char */
insert into spt_datatype_info values
(47,null,null,'char',1,null,null,null,null,'''','''','length',1,@case,3,null,0,null,'char',0,1,null)
/* Local Datetime */
insert into spt_datatype_info values
(61,8,2,'datetime',11,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
insert into spt_datatype_info values
(61,8,3,'datetime',93,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
/* Local Smalldatetime */
insert into spt_datatype_info values
(58,4,2,'smalldatetime',11,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values
(58,4,3,'smalldatetime',93,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
/* Local Float */
insert into spt_datatype_info values
(62,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
insert into spt_datatype_info values
(62,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
/* Local Real */
insert into spt_datatype_info values
(59,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values
(59,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
/* Local Smallmoney */
insert into spt_datatype_info values
(122,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
/* Local Money */
insert into spt_datatype_info values
(60,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)
/* Local Int */
insert into spt_datatype_info values
(56,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
commit tran
end
go
if 'A' = 'A' /* create dummy begin block */
begin
declare @case smallint
begin tran
select @case = 0
select @case = 1 where 'a' <> 'A'
/* Local Smallint */
insert into spt_datatype_info values
(52,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
insert into spt_datatype_info values
(52,2,1,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
/* Local Tinyint */
insert into spt_datatype_info values
(48,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)
/* Local Text */
insert into spt_datatype_info values
(35,null,null,'text',-1,2147483647,null,null,2147483647,'''','''',null,1,@case,1,null,0,null,'text',0,-1,null)
/* Local Varbinary */
insert into spt_datatype_info values
(37,null,null,'varbinary',-3,null,null,null,null,'0x',null,'max length',1,0,2,null,0,null,'varbinary',0,-3,null)
/* Local Varchar */
insert into spt_datatype_info values
(39,null,null,'varchar',12,null,null,null,null,'''','''','max length',1,@case,3,null,0,null,'varchar',0,12,null)
/* Local Image */
insert into spt_datatype_info values
(34,null,null,'image',-4,2147483647,null,null,2147483647,'0x',null,null,1,0,0,null,0,null,'image',0,-4,null)
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
/* Local Decimal */
insert into spt_datatype_info values /* sql server type is 'decimaln' */
(55,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)
/* Local Numeric */
insert into spt_datatype_info values /* sql server type is 'numericn' */
(63,null,null,'numeric',2 ,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)
/* Identity attribute data types */
/* Identity Int */
insert into spt_datatype_info values
(56,null,null,'int identity',4,10,0,10,null,null,null,null,0,0,2,0,0,1,'int identity',null,4,null)
/* Identity Smallint */
insert into spt_datatype_info values
(52,null,null,'smallint identity',5,5,0,10,null,null,null,null,0,0,2,0,0,1,'smallint identity',null,5,null)
/* Identity Tinyint */
insert into spt_datatype_info values
(48,null,null,'tinyint identity',-6,3,0,10,null,null,null,null,0,0,2,1,0,1,'tinyint identity',null,-6,null)
/* Identity Numeric */
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(55,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)
/* Identity Numeric */
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(63,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
end
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin /* Add nullable type for non-8.0 server */
/* Local Datetimn */
insert into spt_datatype_info values
(111,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values
(111,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3)
insert into spt_datatype_info values /* sql server type is 'datetimn' */
(111,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
insert into spt_datatype_info values
(111,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3)
/* Local Floatn */
insert into spt_datatype_info values /* sql server type is 'floatn' */
(109,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values
(109,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null)
insert into spt_datatype_info values /* sql server type is 'floatn' */
(109,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
insert into spt_datatype_info values
(109,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null)
/* Local Moneyn */
insert into spt_datatype_info values /* sql server type is 'moneyn' */
(110,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null)
insert into spt_datatype_info values /* sql server type is 'moneyn' */
(110,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null)
/* Local Intn */
insert into spt_datatype_info values /* sql server type is 'intn' */
(38,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null)
insert into spt_datatype_info values /* sql server type is 'intn' */
(38,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null)
insert into spt_datatype_info values
(38,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null)
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Add 6.0 data types */
/* Local Decimaln */
insert into spt_datatype_info values /* sql server type is 'decimaln' */
(106,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null)
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(106,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null)
/* Local Numericn */
insert into spt_datatype_info values /* sql server type is 'numericn' */
(108,null,null,'numeric',2,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null)
insert into spt_datatype_info values /* sql server type is 'decmaln' */
(108,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null)
end
end
commit tran
end
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin
declare @ncase smallint
select @ncase = 0
select @ncase = 1 where N'a' <> N'A'
/* Local Timestamp */
insert into spt_datatype_info values
(0,null,null,'timestamp',-2,8,null,null,null,'0x',null,null,1,0,2,null,0,null,'timestamp',0,-2,null)
/* Local GUID */
insert into spt_datatype_info values
(0,null,null,'uniqueidentifier',-11,36,null,null,null,'''','''',null,1,0,2,null,0,null,'uniqueidentifier',NULL,-11,null)
/* Local NChar */
insert into spt_datatype_info values
(0,null,null,'nchar',-8,null,null,null,null,'N''','''','length',1,@ncase,3,null,0,null,'nchar',0,-8,null)
/* Local NVarchar */
insert into spt_datatype_info values
(0,null,null,'nvarchar',-9,null,null,null,null,'N''','''','max length',1,@ncase,3,null,0,null,'nvarchar',0,-9,null)
/* Local NText */
insert into spt_datatype_info values
(0,null,null,'ntext',-10,2147483646,null,null,2147483646,'N''','''',null,1,@ncase,1,null,0,null,'ntext',0,-10,null)
if (charindex('8.00', @@version) > 0)
begin
-- /* Local BIGINT */
insert into spt_datatype_info values
(127,8,null,'bigint',-5,19,0,10,null,null,null,null,1,0,2,0,0,0,'bigint',null,-5,null)
-- /* Identity BIGINT */
insert into spt_datatype_info values
(127,8,null,'bigint identity',-5,19,0,10,null,null,null,null,0,0,2,0,0,1,'bigint identity',null,-5,null)
-- /* sql_variant */
insert into spt_datatype_info values
(
98, --ss_dtype
null, --fixlen
null, --ODBCVer
'sql_variant', --TYPE_NAME
-150, --SQL DATA TYPE
8000, --data_precision
0, --numeric_scale
10, --RADIX
8000, --length
null, --PREFIX
null, --SUFFIX
null, --Create Params
1, --Nullable
0, --Case sensitive
2, --Searchable
null, --UNSIGNED_ATTRIBUTE
0, --MONEY
null, --AUTO_INCREMENT
'sql_variant', --LOCAL TYPE NAME
0, --CHARBIN
-150, --SQL_DATA_TYPE
null --SQL_DATETIME_SUB
)
end
update spt_datatype_info set NULLABLE = 1
where TYPE_NAME = 'bit'
update spt_datatype_info set ss_dtype = isnull((select distinct xtype from systypes
where TYPE_NAME like name+'%'),0)
end
go
create unique clustered index datatypeinfoclust on spt_datatype_info(ss_dtype,fixlen,ODBCVer,AUTO_INCREMENT)
go
dump tran master with no_log
go
print 'creating table spt_server_info'
go
create table spt_server_info (
attribute_id int NOT NULL,
attribute_name varchar(60) NOT NULL,
attribute_value varchar(255) NOT NULL)
go
create unique clustered index serverinfoclust on spt_server_info(attribute_id)
go
if (charindex('7.00', @@version) = 0 and charindex('8.00', @@version) = 0)
begin
drop procedure sp_add_server_sortinfo /* not used by other servers */
drop procedure sp_add_server_sortinfo75 /* not used by older servers */
dump tran master with no_log
end
go
insert into spt_server_info
values (1,'DBMS_NAME','Microsoft SQL Server')
insert into spt_server_info
values (2,'DBMS_VER',@@version)
insert into spt_server_info
values (10,'OWNER_TERM','owner')
insert into spt_server_info
values (11,'TABLE_TERM','table')
insert into spt_server_info
values (12,'MAX_OWNER_NAME_LENGTH','30')
insert into spt_server_info
values (13,'TABLE_LENGTH','30')
insert into spt_server_info
values (14,'MAX_QUAL_LENGTH','30')
insert into spt_server_info
values (15,'COLUMN_LENGTH','30')
if 'A' = 'a' /* If not case sensitive server */
begin
insert into spt_server_info
values (16,'IDENTIFIER_CASE','MIXED')
end
else
begin
insert into spt_server_info
values (16,'IDENTIFIER_CASE','SENSITIVE')
end
insert into spt_server_info
values (17,'TX_ISOLATION','2')
if (charindex('6.00', @@version) > 0 or
charindex('6.50', @@version) > 0 or
charindex('7.00', @@version) > 0)
begin /* Add 6.0 collation sequence */
insert into spt_server_info
select 18,'COLLATION_SEQ',
'charset='+t2.name+' sort_order='+t1.name
+' charset_num='+rtrim(convert(char(4),t1.csid))+
' sort_order_num='+rtrim(convert(char(4),t1.id))
from syscharsets t1, syscharsets t2, sysconfigures t3
where t1.csid=t2.id and t1.id=t3.value and t3.config=1123
end
else
begin /* Add 4.2x collation sequence */
insert into spt_server_info
select 18,'COLLATION_SEQ',
'charset='+t2.name+' sort_order='+t1.name
+' charset_num='+rtrim(convert(char(4),t1.csid))+
' sort_order_num='+rtrim(convert(char(4),t1.id))
from syscharsets t1, syscharsets t2, sysconfigures t3
where t1.csid=t2.id and t1.id=t3.value and t3.config=123
end
insert into spt_server_info
values (19,'SAVEPOINT_SUPPORT','Y')
insert into spt_server_info
values (20,'MULTI_RESULT_SETS','Y')
insert into spt_server_info
values (22,'ACCESSIBLE_TABLES','Y')
insert into spt_server_info
values (100,'USERID_LENGTH','30')
insert into spt_server_info
values (101,'QUALIFIER_TERM','database')
insert into spt_server_info
values (102,'NAMED_TRANSACTIONS','Y')
insert into spt_server_info
values (103,'SPROC_AS_LANGUAGE','Y')
insert into spt_server_info
values (104,'ACCESSIBLE_SPROC','Y')
insert into spt_server_info
values (105,'MAX_INDEX_COLS','16')
insert into spt_server_info
values (106,'RENAME_TABLE','Y')
insert into spt_server_info
values (107,'RENAME_COLUMN','Y')
if (charindex('8.00', @@version) > 0)
begin
/* Columns may be dropped on 8.0 or later */
insert into spt_server_info
values (108,'DROP_COLUMN','Y')
end
else
begin
insert into spt_server_info
values (108,'DROP_COLUMN','N')
end
if (charindex('8.00', @@version) > 0)
begin
/* Columns size may be changed on 8.0 or later */
insert into spt_server_info
values (109,'INCREASE_COLUMN_LENGTH','Y')
end
else
begin
insert into spt_server_info
values (109,'INCREASE_COLUMN_LENGTH','N')
end
if (charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
insert into spt_server_info
values (110,'DDL_IN_TRANSACTION','N')
end
else
begin
insert into spt_server_info
values (110,'DDL_IN_TRANSACTION','Y')
end
if (charindex('8.00', @@version) > 0)
begin
/* Descending indexes allowed on 8.0 or later */
insert into spt_server_info
values (111,'DESCENDING_INDEXES','Y')
end
else
begin
insert into spt_server_info
values (111,'DESCENDING_INDEXES','N')
end
insert into spt_server_info
values (112,'SP_RENAME','Y')
insert into spt_server_info
values (113,'REMOTE_SPROC','Y')
insert into spt_server_info
values (500,'SYS_SPROC_VERSION',convert(varchar(9), serverproperty ('ProductVersion')))
go
if (charindex('7.00', @@version) > 0 or
charindex('8.00', @@version) > 0)
begin /* Update values for 8.0 server */
update spt_server_info set attribute_value = '128'
where attribute_id in (12,13,14,15,100)
end
go
grant select on spt_server_info to public
go
print 'creating sp_column_privileges'
go
/* Procedure for pre 6.50 server */
CREATE PROCEDURE sp_column_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(96) = null) /* 3*32 */
as
set nocount on
declare @table_id int
DECLARE @full_table_name varchar(65) /* 2*32 + 1 */
declare @low smallint /* range of userids to check */
declare @high smallint
declare @owner_uid smallint
select @low = 0, @high = 32767
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror 20001 '~~Rush_5~~'
return
end
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = @table_name
end
else
begin /* Qualified table name */
SELECT @full_table_name = @table_owner + '.' + @table_name
end
/* Get Object ID */
select @table_id = object_id(@full_table_name)
if (@@trancount <> 0)
begin /* If inside a transaction */
raiserror 20003 '~~Rush_6~~'
return
end
/*
** We need to create a table which will contain a row for every row to
** be returned to the client.
*/
create table #column_priv1(
COLUMN_NAME varchar(32) NOT NULL,
grantor smallint NOT NULL,
grantee smallint NOT NULL,
select_privilege bit,
select_grantable bit,
insert_privilege bit,
insert_grantable bit,
update_privilege bit,
update_grantable bit,
references_privilege bit,
references_grantable bit,
uid smallint NOT NULL,
gid smallint NOT NULL)
/*
** insert a row for the table owner (who has all permissions)
*/
select @owner_uid = (
select uid
from sysobjects
where id = @table_id)
if (charindex('6.00', @@version) > 0)
begin
insert into #column_priv1
select
c.name,
u.uid,
@owner_uid,
0,
1,
0,
1,
0,
1,
0,
1,
@owner_uid,
0
from syscolumns c, sysusers u
where id = @table_id
and c.number = 0
and u.uid = 1 /* grantor is dbo of database */
end
else
begin
insert into #column_priv1
select
c.name,
u.uid,
@owner_uid,
0,
1,
0,
1,
0,
1,
0,
0,
@owner_uid,
0
from syscolumns c, sysusers u
where id = @table_id
and c.number = 0
and u.uid = 1 /* grantor is dbo of database */
end
/*
** now stick in a row for every column for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/
insert into #column_priv1
select distinct
c.name,
o.uid,
u.uid,
0,
0,
0,
0,
0,
0,
0,
0,
u.uid,
u.gid
from sysusers u, syscolumns c, sysobjects o
where o.id = @table_id
and c.id = o.id
and c.number = 0
and u.gid <> u.uid
and u.uid <> @owner_uid
/*
** we need to create another temporary table to contain all the various
** protection information for the table in question
*/
create table #protects (
uid smallint NOT NULL,
grantor smallint NOT NULL,
action tinyint NOT NULL,
protecttype tinyint NOT NULL,
name varchar(32) NOT NULL)
insert into #protects
select
p.uid,
p.uid,
p.action,
p.protecttype,
isnull(col_name(id, c.number), '~All')
from
sysprotects p,
master.dbo.spt_values c,
master.dbo.spt_values a,
master.dbo.spt_values b
where
convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
& c.high <> 0
and c.number <= (
select count(*)
from syscolumns
where id = @table_id)
and c.type = 'P'
and a.type = 'T'
and a.number = p.action
and p.action in (193,195,197,26)
and b.type = 'T'
and b.number = p.protecttype
and p.id = @table_id
and p.uid between @low and @high
update #column_priv1
set select_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 193
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 193
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set insert_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 195
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 195
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set update_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 197
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where protecttype = 206
and action = 197
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set references_privilege = 1
from #protects p
where
p.protecttype = 205
and p.action = 26
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where protecttype = 206
and action = 26
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and (uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set select_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 193
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 193
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set insert_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 195
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 195
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set update_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 197
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 197
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
update #column_priv1
set references_grantable = 1
from #protects p
where
p.protecttype = 204
and p.action = 26
and (p.name = #column_priv1.COLUMN_NAME
or name = '~All')
and (p.uid = 0
or p.uid = #column_priv1.gid
or p.uid = #column_priv1.uid)
and not exists (
select * from #protects
where
protecttype = 206
and action = 26
and (name = #column_priv1.COLUMN_NAME
or name = '~All')
and ( uid = 0
or uid = #column_priv1.gid
or uid = #column_priv1.uid))
create table #column_priv2(
COLUMN_NAME varchar(32) NOT NULL,
grantor smallint NULL,
grantee smallint NOT NULL,
PRIVILEGE varchar(32) NOT NULL,
IS_GRANTABLE varchar(3) NULL)
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'SELECT',
'NO'
from #column_priv1
where select_privilege = 1 and select_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'INSERT',
'NO'
from #column_priv1
where insert_privilege = 1 and insert_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'UPDATE',
'NO'
from #column_priv1
where update_privilege = 1 and update_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'REFERENCES',
'NO'
from #column_priv1
where references_privilege = 1 and references_grantable = 0
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'SELECT',
'YES'
from #column_priv1
where select_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'INSERT',
'YES'
from #column_priv1
where insert_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'UPDATE',
'YES'
from #column_priv1
where update_grantable = 1
insert into #column_priv2
select
COLUMN_NAME,
grantor,
grantee,
'REFERENCES',
'YES'
from #column_priv1
where references_grantable = 1
select
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(@owner_uid)) TABLE_OWNER,
@table_name TABLE_NAME,
COLUMN_NAME,
convert(varchar(32),user_name(grantor)) GRANTOR,
convert(varchar(32),user_name(grantee)) GRANTEE,
PRIVILEGE,
IS_GRANTABLE
from #column_priv2
where COLUMN_NAME like @column_name
order by 4, 7
go
if (charindex('6.50', @@version) = 0 and
charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 6.50 SQL Server.'
print 'Ignore the following errors.'
end
else
drop proc sp_column_privileges
go
/* Procedure for 6.50 server */
CREATE PROCEDURE sp_column_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(96) = null) /* 3*32 */
as
declare @table_id int
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin /* If unqualified table name */
select @table_id = object_id(@table_name)
end
else
begin /* Qualified table name */
select @table_id = object_id(@table_owner + '.' + @table_name)
end
select
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(varchar(32),c.name) COLUMN_NAME,
convert(varchar(32),user_name(p.grantor)) GRANTOR,
convert(varchar(32),user_name(u.uid)) GRANTEE,
convert (varchar(32),case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert (varchar(3),case when p.protecttype = 205 then 'NO'
else 'YES'
end) IS_GRANTABLE
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c
where
c.id = @table_id
and c.name like @column_name
and c.id = p.id
and c.id = o.id
and case substring(p.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p.columns, v.low, 1))
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0 /* permission applies to this column */
and v.number <= (select count(*) from syscolumns
where id = @table_id) /* ranges from 1 to # of columns in table */
and v.type = 'P'
and v.number = c.colid
/* expand groups */
and ((p.uid = u.uid and u.uid <> u.gid) or
(p.uid = u.gid and u.uid <> u.gid))
and p.protecttype <> 206 /* only grant rows */
and p.action in (26,193,195,197)
and o.uid <> u.uid /* no rows for owner */
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = p.action
and p1.id = p.id
and p1.uid = u.uid
and case substring(p1.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0) /* permission applies to this column */
union
select /* Add rows for table owner */
convert(varchar(32),db_name()) TABLE_QUALIFIER,
convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(varchar(32),col_name(@table_id, c.colid)) COLUMN_NAME,
convert(varchar(32),user_name(u.uid)) grantor,
convert(varchar(32),user_name(o.uid)) grantee,
convert (varchar(32),case v.number
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert(varchar(3),'YES') IS_GRANTABLE
from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c
where
c.id = @table_id
and c.name like @column_name
and c.id = o.id
and u.uid = 1 /* grantor is dbo of database */
and v.type = 'P' /* cross product to get all exposed privileges */
and v.number in (26,193,195,197)
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = v.number
and p1.id = o.id
and p1.uid = o.uid)
order by 4, 7
go
if (charindex('7.00', @@version) = 0 and
charindex('8.00', @@version) = 0)
begin
print ''
print ''
print 'Warning:'
print 'you are installing the stored procedures '
print 'on a pre 8.0 SQL Server.'
print 'Ignore the following errors.'
end
else
drop proc sp_column_privileges
go
/* Procedure for 8.0 server */
CREATE PROCEDURE sp_column_privileges (
@table_name sysname,
@table_owner sysname = null,
@table_qualifier sysname = null,
@column_name nvarchar(384) = null) /* 3*128 */
as
declare @table_id int
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin /* If unqualified table name */
select @table_id = object_id(quotename(@table_name))
end
else
begin /* Qualified table name */
if @table_owner = N''
begin /* If empty owner name */
select @table_id = 0
end
else
begin
select @table_id = object_id(quotename(@table_owner) +
'.' + quotename(@table_name))
end
end
select
convert(sysname,db_name()) TABLE_QUALIFIER,
convert(sysname,user_name(o.uid)) TABLE_OWNER,
@table_name TABLE_NAME,
convert(sysname,c.name) COLUMN_NAME,
convert(sysname,user_name(p.grantor)) GRANTOR,
convert(sysname,user_name(u.uid)) GRANTEE,
convert (varchar(32),case p.action
when 193 then 'SELECT'
when 195 then 'INSERT'
when 197 then 'UPDATE'
else 'REFERENCES'
end) PRIVILEGE,
convert (varchar(3),case when p.protecttype = 205 then 'NO'
else 'YES'
end) IS_GRANTABLE
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c, sysmembers m
where
c.id = @table_id
and c.name like @column_name
and c.id = p.id
and c.id = o.id
and case substring(p.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p.columns, v.low, 1))
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
end
& v.high <> 0 /* permission applies to this column */
and v.number <= (select count(*) from syscolumns
where id = @table_id) /* ranges from 1 to # of columns in table */
and v.type = N'P'
and v.number = c.colid
/* expand groups - AKUNDONE: only 1 level of group unrolling here. Need more?? */
and (u.uid > 0 and u.uid < 16384)
and ((p.uid = u.uid) or
(p.uid = m.groupuid and u.uid = m.memberuid))
and p.protecttype <> 206 /* only grant rows */
and p.action in (26,193,195,197)
and o.uid <> u.uid /* no rows for owner */
and not exists ( /* exclude revoke'd privileges */
select *
from sysprotects p1
where
p1.protecttype = 206
and p1.action = p.action
and p1.id = p.id
and p1.uid = u.uid
and case substring(p1.columns, 1, 1) & 1
when NULL then 255 /* all columns have permission */
when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
Regis59
Messages postés
21143
Date d'inscription
mardi 27 juin 2006
Statut
Contributeur sécurité
Dernière intervention
22 juin 2016
1 320
25 mars 2007 à 10:04
25 mars 2007 à 10:04
Salut
Laisse le, il est ok. :)
Ou en sont tes soucis?
A+
Laisse le, il est ok. :)
Ou en sont tes soucis?
A+
loic42
Messages postés
26
Date d'inscription
mardi 20 mars 2007
Statut
Membre
Dernière intervention
22 mai 2009
25 mars 2007 à 21:32
25 mars 2007 à 21:32
salut!!!
Bon je ne voudrais pas vendre la "peau de l'ours..." mais j'ai redemarré quelque fois depuis la derniere etape et mon PC s'eteint correctement!!!!
J'espere que le probleme est reglé mais en tous cas ca en a l'air!!!!
Merci pour ton acharnement sur ce probleme,ca m'a bien evité un formatage qui fait pas plaisir!
Je sais pas comment tu as trouvé chaque elements a supprimer dans ces listes interminable, mais en tous les cas : "Chapeau"!!!
Je te souhaite une bonne continuation!Et encore une fois g te dis un grand merci!!!Ta ete super!!!!
A+
Loïc
Bon je ne voudrais pas vendre la "peau de l'ours..." mais j'ai redemarré quelque fois depuis la derniere etape et mon PC s'eteint correctement!!!!
J'espere que le probleme est reglé mais en tous cas ca en a l'air!!!!
Merci pour ton acharnement sur ce probleme,ca m'a bien evité un formatage qui fait pas plaisir!
Je sais pas comment tu as trouvé chaque elements a supprimer dans ces listes interminable, mais en tous les cas : "Chapeau"!!!
Je te souhaite une bonne continuation!Et encore une fois g te dis un grand merci!!!Ta ete super!!!!
A+
Loïc
Regis59
Messages postés
21143
Date d'inscription
mardi 27 juin 2006
Statut
Contributeur sécurité
Dernière intervention
22 juin 2016
1 320
26 mars 2007 à 10:45
26 mars 2007 à 10:45
Salut
Y'as pas de quoi :)
Surtout, si tu as besoin, n hesites pas a revenir !
Merci pour ta patience et ta sympathie !
Bonne continuation :)
Y'as pas de quoi :)
Surtout, si tu as besoin, n hesites pas a revenir !
Merci pour ta patience et ta sympathie !
Bonne continuation :)
