June 10, 2008 at 6:31 am
I have a cross tab sp - which makes a table to report back the crosstab results.
When my users run it - it fails cos they don't have persmissions to create tables. How can I give them permissions temporarily to create the table? In 2005 I coul use EXECUTE as could I not. But in 2000, what do I do?
The sp is as below.
CREATE PROCEDURE dbo.sp_Crosstab
@DBFetch varchar(4000),
@DBWhere varchar(2000) = NULL,
@DBPivot varchar(4000) = NULL,
@DBField varchar(100),
@PCField varchar(100),
@PCBuild varchar( 20),
@PCAdmin varchar( 20) = NULL,
@DBAdmin int = 0,
@DBTable varchar(100) = NULL,
@DBWrite varchar(160) = NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @status int
SET @status = 0
DECLARE @TPre varchar(10)
DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint
SET @TPre = 'tbl'
SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1
DECLARE @DBAE varchar(40)
DECLARE @Task varchar(8000)
DECLARE @Bank varchar(4000)
DECLARE @cash varchar(2000)
DECLARE @rich-2 varchar(2000)
DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(8000)
DECLARE @DBAU varchar(2000)
DECLARE @Name varchar(100)
DECLARE @Same varchar(100)
DECLARE @Home varchar(160)
DECLARE @some varchar(20)
DECLARE @Work int
DECLARE @wink int
SET @DBAE = '##Crosstab' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)
SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE
EXECUTE (@Task)
CREATE TABLE #DBAT (Work int IDENTITY(1,1), Name varchar(100))
SET @Bank = @TPre + @DBFetch
IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)
BEGIN
SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39))
SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))
END
IF @DBWhere IS NOT NULL
BEGIN
SET @cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32))
SET @cash = REPLACE(@Cash, CHAR(94),CHAR(39))
SET @cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
SET @cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))
END
SET @DBField = REPLACE(@DBField,CHAR(32),CHAR(95))
SET @PCField = REPLACE(@PCField,CHAR(32),CHAR(95))
SET @PCBuild = REPLACE(@PCBuild,CHAR(32),CHAR(95))
SET @PCAdmin = REPLACE(@PCAdmin,CHAR(32),CHAR(95))
SET @DBTable = REPLACE(@DBTable,CHAR(32),CHAR(95))
SET @DBWrite = REPLACE(@DBWrite,CHAR(32),CHAR(95))
SET @DBWhere = CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @cash + ') AND 0 = 0' END
SET @some = ISNULL(@PCAdmin,'NA')
SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM ' + @Bank + ' AS T WHERE 0 = 1'
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
IF @DBPivot IS NOT NULL
BEGIN
IF LEFT(@DBPivot,6) <> 'SELECT'
BEGIN
SET @wink = 1
SET @Work = CHARINDEX('|',(@DBPivot)+'|')
WHILE @Work > 0
BEGIN
SET @Name = SUBSTRING(@DBPivot,@Wink,@Work-@Wink)
INSERT #DBAT (Name) VALUES (@Name)
SET @wink = @Work + 1
SET @Work = CHARINDEX('|',(@DBPivot)+'|',@Wink)
END
END
ELSE
BEGIN
SET @Task = 'INSERT #DBAT (Name) ' + @DBPivot
SET @Task = REPLACE(@Task, CHAR(94),CHAR(39))
SET @Task = REPLACE(@Task,CHAR(45)+CHAR(45),CHAR(32))
SET @Task = REPLACE(@Task,CHAR(47)+CHAR(42),CHAR(32))
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
ELSE
BEGIN
SET @Task = ' INSERT #DBAT (Name)'
+ ' SELECT CONVERT(varchar(100),' + @DBField + ')'
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY CONVERT(varchar(100),' + @DBField + ')'
+ ' ORDER BY 1'
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
UPDATE #DBAT SET Name = @some WHERE Name IS NULL
SET @DBAI = ''
SET @DBAO = ''
SET @rich-2 = ''
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT C.name
FROM tempdb.dbo.sysobjects AS O
JOIN tempdb.dbo.syscolumns AS C
ON C.id = O.id
AND C.name != @DBField
AND C.name != @PCField
AND O.name = @DBAE
ORDER BY C.colid
SET @Retain = @@ERROR IF @status = 0 SET @status = @Retain
OPEN Fields
SET @Retain = @@ERROR IF @status = 0 SET @status = @Retain
FETCH NEXT FROM Fields INTO @Same
SET @Retain = @@ERROR IF @status = 0 SET @status = @Retain
WHILE @@FETCH_STATUS = 0 AND @status = 0
BEGIN
SET @DBAI = @DBAI + ', ' + @Same
FETCH NEXT FROM Fields INTO @Same
SET @Retain = @@ERROR IF @status = 0 SET @status = @Retain
END
CLOSE Fields DEALLOCATE Fields
SET @DBAI = SUBSTRING(@DBAI,3,4000)
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT Name
FROM #DBAT
ORDER BY Work
OPEN Fields
FETCH NEXT FROM Fields INTO @Same
WHILE @@FETCH_STATUS = 0 AND @status = 0
BEGIN
IF LEN(@DBAO) < 7900 - LEN(@DBField) - LEN(@PCField) - LEN(@Same) - LEN(@Same)
BEGIN
SET @DBAO = @DBAO + ', ' + @PCBuild + '(CASE WHEN ISNULL(CONVERT(varchar(100),' + @DBField + '),'
+ CHAR(39) + @some + CHAR(39) + ') = '
+ CHAR(39) + @Same + CHAR(39) + ' THEN '
+ @PCField + ' ELSE NULL END) AS '
+ CHAR(91) + @Same + CHAR(93)
END
ELSE
BEGIN
SET @status = 50000
END
FETCH NEXT FROM Fields INTO @Same
END
CLOSE Fields DEALLOCATE Fields
IF @DBAdmin IN (1,3) SET @rich-2 = @rich-2 + ', ' + @PCBuild + '(' + @PCField + ') AS All_' + @PCBuild
IF @DBAdmin IN (2,3) SET @rich-2 = @rich-2 + ', COUNT(' + @PCField + ') AS All_COUNT'
IF @DBAdmin IN (2,3) SET @rich-2 = @rich-2 + ', MIN(' + @PCField + ') AS All_MIN'
IF @DBAdmin IN (2,3) SET @rich-2 = @rich-2 + ', MAX(' + @PCField + ') AS All_MAX'
SET ANSI_WARNINGS OFF
SET @Home = ''
SET @Name = ''
IF @DBTable IS NOT NULL
BEGIN
SET @Name = @DBTable
IF LEFT(@Name,2) = '##'
BEGIN
IF @DBUltra = 0
BEGIN
SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Name
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
ELSE
BEGIN
IF @DBWrite IS NOT NULL SET @Home = @DBWrite + '.dbo.'
IF @DBUltra = 0
BEGIN
SET @Task = 'IF EXISTS (SELECT * FROM ' + @Home + 'sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Home + @Name
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
END
IF @DBTable IS NOT NULL
BEGIN
IF @DBUltra = 0
BEGIN
IF @status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @rich-2
+ ' INTO ' + @Home + @Name
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
ELSE
BEGIN
IF @status = 0 EXECUTE ( ' INSERT ' + @Home + @Name
+ ' SELECT ' + @DBAI + @DBAO + @rich-2
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
ELSE
BEGIN
IF @status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @rich-2
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
SET ANSI_WARNINGS ON
SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE
EXECUTE (@Task)
DROP TABLE #DBAT
SET NOCOUNT OFF
RETURN (@Status)
Thanks
June 10, 2008 at 12:54 pm
How about something like,
GRANT EXEC ON yourProc TO PUBLIC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply