Table Permissions On MSDE - SQL Server Forums

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

SQL Server Development (2000)

Table Permissions On MSDE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-03 : 08:19:02

Andy writes "Hi,
I am new to MSDE but I have managed to write a series of oSQL scripts to attach a database, create the login, grant access etc and all seems fine EXCEPT there are no table permissions set for my user. I therefore wrote the attached script to try and set the permissions but with no success. Is there a quicker way than below to set the permissions or where have I gone wrong with the attached. This is my last hurdle so any help grately appreciated.

Declare @Tablename varChar(100)

DECLARE MainCursor CURSOR FOR

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME NOT LIKE 'sys%'
AND TABLE_NAME <> 'dtproperties'
AND TABLE_SCHEMA <> 'INFORMATION_SCHEMA'

OPEN MainCursor

FETCH NEXT FROM MainCursor into @Tablename

WHILE @@FETCH_STATUS = 0

BEGIN
grant All on @Tablename to public

FETCH NEXT FROM MainCursor into @Tablename
END
CLOSE MainCursor
DEALLOCATE MainCursor

ERROR BELOW
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '@Tablename'."

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-03 : 10:11:50

why not add the permissions as part of the table creation script ?

liverpoolandy
Starting Member

1 Post

Posted - 2004-06-04 : 05:02:02

I kept trying with this one and found the answer in the end

EXEC ('GRANT ALL ON ' + @tablename + ' TO TS2000')

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-06-05 : 01:36:58

Yes, exactly, if you want to use a variable in a command like that, you have to use Dynamic SQL (an EXEC or sp_executesql statement). But gpl is right on the money in terms of a best practice for ongoing work. Grant rights when you create the table, or have a script later that runs the specific grants for specific tables rather than looping through a list of every table after-the-fact.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources