SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Granting permission
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

YM
Starting Member

Singapore
3 Posts

Posted - 10/08/2006 :  23:29:16  Show Profile  Reply with Quote
Hi

I need to grant SELECT permission for ALL TABLES for tables which are tag to role (eg. ABC).

How do i grant them "permanently"? as the tables o/p by role (ABC) will be dropped and recreated when another users rerun the tables?

Appreciate any help.

Thanks very much!!

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 10/08/2006 :  23:33:31  Show Profile  Visit timmy's Homepage  Reply with Quote
Why are you dropping and recreating tables on the fly??

If you must do this, you should issue a GRANT SELECT command after you've created the table.
Go to Top of Page

YM
Starting Member

Singapore
3 Posts

Posted - 10/08/2006 :  23:36:08  Show Profile  Reply with Quote
It's actually done thru the front end application.

On the front end, user can run report which recreates tables (i.e. drop and recreate) at the backend SQL.

But i need to restrict user to perform SELECT only when login thru the backend SQL.

Any idea?

Many Thanks!

Edited by - YM on 10/08/2006 23:36:40
Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 10/09/2006 :  00:53:12  Show Profile  Visit timmy's Homepage  Reply with Quote
If the user created the tables, then surely they will be able to read from them?

If not, you just need to issue the GRANT SELECT statement to alter the permissions.

Or you could design it so you don't need to dynamically create tables.....
Go to Top of Page

YM
Starting Member

Singapore
3 Posts

Posted - 10/09/2006 :  02:00:22  Show Profile  Reply with Quote
that's the problem. The ID logs thru the application and uses another role (ABC) to o/p those tables.

But logging going thru the backend, the ID is tagged with another role which do not have SELECT.
I can set SELECT for once so ID can do SELECT. However, as the tables are dropped and recreated, i cannot be performing
the GRANT SELECT "automatically" or on the fly.

Is there a command that i can always set GRANT for the IDs (assume its role is VIEW) where tables are o/p by ROLE=ABC?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000