access cmd to sql server "grant insert on.."

  • Thread starter Thread starter Alison
  • Start date Start date
A

Alison

Hello
I'm trying to send a "grant insert on..." command to a
databse on sqlserver from access. I have the syntax ready
to go, can anyone help with how to send it to the sql
server database?
thanks much
alison
 
You need to create a SPT query.
(SQL Pass Through.)
This uses the syntax of the SERVER (not Access).
So for Oracle commands you use Oracle syntax.
Ditto for SQL Server.

Connect to the server as a user that has rights to grant permissions to
other users. (sa or dbo).
Then write a new query and use the menu options:
Query
SQL Specific
Pass Through

In the Property window for your query enter a connection string as the power
user noted above.
The you can use normal Transact-SQL syntax:
grant select on table1 to someuser

Then run the query.
someuser should now be able to select records from table1.

To prove it, try to link to that table as someuser - it should now be in the
list of available tables.
(try linking before granting and you should not see it.)
 
Back
Top