Grant Permission in Store Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grant Permission in Store Procedure

Author

Topic

icy111
Starting Member

1 Post

Posted - 2002-04-22 : 06:28:19

Hi,

I was writing the store procedure in granting permission to different tables in SQL Server 7.0
I always getting syntax error near the "TO" statement.
I can't hardcode the "@db_cc" as i have few tables to run through this
store procedure.
Below are my codes.
Can anyone helped me this as i am quite new to store procedure.

Thanks in advance.


CREATE PROCEDURE sp_table
@dbname nvarchar(30)
AS 
Declare 
@db_cc varchar(15), 
@db_dd varchar(15)

Select @db_cc = @dbname plus '_xxx1'
Select @db_dd =  @dbname plus '_xxx2'

  GRANT SELECT,INSERT, UPDATE, DELETE
  ON table
  TO @db_cc
  GO

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-22 : 06:55:26

To get around this, create a string containing the whole SQL statement, and then execute it using the EXEC statement.

SET @cSQL = 'GRANT SELECT,INSERT, UPDATE, DELETE ON table TO ' + @db_cc
EXEC @cSQL

There's loads of stuff on the site for dynamic SQL, check out the FAQ's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources