SQL Srv 2000 - Apply Permissions via a Parameter

[naxy]

I have a set of permissions which can potentially apply to about 15 different tables. What I want to do is be able to call these permissions dynamically so that I don't have to split a block of logic into two Stored Procedures.

Basically, I have a block of logic in which a "working" table is dropped and recreated (and permissions are part of that process). Once recreated, I do a BCP and then manipulate the new data as needed. Currently, in order to do this, I have to split the logic over two Stored Procedures (split after the GRANT).

I was thinking that I could just create a dynamic Stored Procedure to handle the GRANT and pass it in the name of the table to grant the permissions on. My problem is that I can't seem to get past a 'Syntax Error' when I try and put my parameter into the GRANT statement.

Am I trying to do the impossible?

 

[ptheriault]

naxy,
You are going to have to post your code and provide us with as much information as you can so we can help you.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[naxy]

Not a problem. Just didn't think I had enough to really make a benefit though.

So here is the first part of the original logic Stored Procedure (where the DROP/recreate is):
SET NOCOUNT ON

--------------------------------------------------------------
--DROP EXISTING PREPAID WORKING TABLE
--------------------------------------------------------------
IF EXISTS (
SELECT *
FROM SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[Claims_Source_Pre]')
And OBJECTPROPERTY(id, N'IsUserTable') = 1
)

DROP TABLE [dbo].[Claims_Source_Pre]


--------------------------------------------------------------
--RECREATE PREPAID WORKING TABLE
--------------------------------------------------------------

CREATE TABLE [dbo].[Claims_Source_Pre] (
[DCN] [char] (18) NOT NULL ,
[PROCDATE] [char] (10) NOT NULL ,
[STATEID] [char] (2) NOT NULL ,
[OPID] [char] (8) NOT NULL ,
[DEPT] [char] (3) NOT NULL ,
[STATUSCD] [char] (2) NOT NULL ,
[STATRSN] [char] (3) NOT NULL ,
[SYSTEMSOURCE] [char] (2) NOT NULL ,
[TOTPAID] [decimal](11, 2) NOT NULL ,
[DOLSTRAT] [char] (1) NOT NULL ,
[AMTBILL] [decimal](11, 2) NOT NULL ,
[GRP] [char] (6) NOT NULL ,
[SUBNO] [char] (9) NOT NULL ,
[MEDICARE] [char] (1) NOT NULL ,
[WKTITEM] [char] (3) NOT NULL ,
[IMAGE] [char] (1) NOT NULL,
[PRODLINE] [char] (1) NOT NULL,
[HCSCIND] [char] (1) NOT NULL,
[MODEREC] [char] (2) NOT NULL ,
[ITEMTYPE] [char] (1) NOT NULL ,
[PAYEE] [char] (1) NOT NULL ,
[TBC] [char] (1) NOT NULL ,
[ADJUD] [char] (1) NOT NULL ,
[PROGRAM] [char] (1) NOT NULL ,
[POWENTRYQ] [char] (1) NOT NULL ,
[PERFGUAR] [char] (1) NOT NULL ,
[NMISTRAT] [char] (4) NOT NULL ,
[Operator_Cost_Center] [char] (5) NOT NULL DEFAULT(' ') ,
[Site_Id] [char] (50) NOT NULL DEFAULT (' ') ,
[SIP_Sample_Flag] [char] (1) NOT NULL DEFAULT ('N') ,
[Power_Entry_PQC_Sample_Flag] [char] (1) NOT NULL DEFAULT ('N') ,
[NMIS_Sample_Flag] [char] (1) NOT NULL DEFAULT ('N') ,
[EPPS_Sample_Flag] [char] (1) NOT NULL DEFAULT ('N') ,
[Random_Value] [float] NOT NULL DEFAULT 0 ,
[SIP_Strata_Code] [char] (4) NOT NULL DEFAULT(' ') ,
[Power_Entry_Strata_Code] [char] (4) NOT NULL DEFAULT (' ') ,
[Claim_Adjusted_Flag] [char] (1) NOT NULL DEFAULT ('N') ,
[Operator_Job_Category] [char] (30) NOT NULL DEFAULT (' ') ,
[Claims_ID] int NULL
)
ON [PRIMARY]

--------------------------------------------------------------
--GRANT PERMISSIONS FOR PREPAID WORKING TABLE
--------------------------------------------------------------
--call USP_Grant_Feed_Permissions
exec dbo.USP_Grant_Feed_Permissions '[dbo].[Claims_Source_Pre]'
GO



And here is the USP_Grant_Feed_Permissions as it is now (just trying to get the parameter pass working before I apply all the of the GRANT):
CREATE PROCEDURE dbo.USP_Grant_Feed_Permissions
@inp_table_name varchar(255)
AS

SET NOCOUNT ON

GRANT
SELECT ,
INSERT ,
DELETE ,
UPDATE
ON @inp_table_name TO [PUBLIC]
GO

 

[mrdenny]

You can't put a variable in a GRANT statement. You have to use dynamic SQL.

Question...

Why not truncate the table instead of dropping it and recreating it? This way the permissions don't need to be reset every time?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

If you can I would follow Denny's suggestion of just truncating the table. The you don't loose permissions.

If you can. you need to use dynamic sql.

Like this

declare @inp_table_name  varchar(255)    
declare @sql varchar(max)

SELECT @inp_table_name = 'test'

SELECT @sql = 'GRANT SELECT,INSERT,DELETE,UPDATE ON '+ @inp_table_name +' TO [PUBLIC]'

exec(@sql)

Also,
You should never, ever grant permissions to the public group.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[naxy]

For whatever reason the original business requirements were drawn, the business owners must be able to alter the table/data at any time. This is something that could easily be done if they knew how to use Views and/or Joins (or let us template the reports within the actual application), but truth of the matter is that they ultimately want to hold the control (we don't live on a very good relationship between IT and the business owner right now). Due to troubles in the past, they feel that they can get something done quicker, with better quality, if they are able to circumvent the developers and the DBA's. Of course, this will all change as our department continues on locking down and securing the backend elements, but that's for another story...

Thanks for the continuation Paul. Like I said, the GRANT was just a quick GRANT for testing before I pulled in the correct one. It's all on my personal sandbox environment anyway.