Solved

SQL Script to set permissions for a user on all views in a database?

Posted on 2004-10-18
10
386 Views
Last Modified: 2008-01-16
Hi:

IS there any sort of SQL script I can run that will give a user all 4 permissions for every custom made view in a database?

Thanks,
Mitch
0
Comment
Question by:Mitchell_
  • 5
  • 3
  • 2
10 Comments
 
LVL 2

Expert Comment

by:NaderYacoub
ID: 12339209
Yes you can... the syntax is such to grant all for guest on the view "Orders Qry" in the Northwind database.


GRANT ALL ON [Orders Qry] TO GUEST

0
 

Author Comment

by:Mitchell_
ID: 12339224
no but what I am asking is, if I have 50 views in my database I don't want to run this query for each view..

0
 
LVL 2

Expert Comment

by:NaderYacoub
ID: 12339369

you are going to have to write a while loop looping thru sysobjects. If you need help writing that, let me know.
0
 

Author Comment

by:Mitchell_
ID: 12339378
I will bump the points to 500 if you could paste some code in.

0
 
LVL 4

Expert Comment

by:mikejrobison
ID: 12339624
Multiple ways to do this...  will this work for you..
You can limit the views by using a where clause to include/exclude certain schemas...


spool gen_grants.sql
set heading off
select ' grant insert,update,delete,select '   || ' on '  || owner ||  '.' || view_name || ';'
from dba_views;


Then run the gen_grants.sql file...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:mikejrobison
ID: 12339638
opps.... typo...


spool gen_grants.sql
set heading off
select ' grant insert,update,delete,select '   || ' on '  || owner ||  '.' || view_name || ' to put_use_here ;'
from dba_views;


Then run the gen_grants.sql file...
0
 
LVL 2

Expert Comment

by:NaderYacoub
ID: 12339800

-- THIS WILL GRANT ALL PERMISSIONS TO ALL USER VIEWS TO A SPECIFIC USER
-- JUST CHANGE THE VARIABLE @USER TO WHATEVER USER YOU WANT
-- RUN THIS IN THE DATABASE YOU NEED TO AFFECT
DECLARE @user varchar(50)
SET @user = 'guest'

DECLARE @tmpName varchar(500)
DECLARE @sql varchar(8000)

DECLARE Objects_Cursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'V' AND status > 0 ORDER BY name
OPEN Objects_Cursor
FETCH NEXT FROM Objects_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
      --SELECT @tmpName =
      FETCH NEXT FROM Objects_Cursor INTO @tmpName
      --PRINT @tmpName
      SET @sql = 'REVOKE ALL ON [' + @tmpName + '] TO ' + @user
      EXEC (@sql)
END
CLOSE Objects_Cursor
DEALLOCATE Objects_Cursor
0
 

Author Comment

by:Mitchell_
ID: 12348677
mikejrobison - that doesn't appear to loop in any way?

NaderYacoub  - that didn't work. It granted all on the TABLES, and revoked on all the views except for 1.

0
 
LVL 2

Accepted Solution

by:
NaderYacoub earned 500 total points
ID: 12361052
sorry.... I put revoke instead of grant... the adjusted code is below. also, this script will not even touch tables.

-- THIS WILL GRANT ALL PERMISSIONS TO ALL USER VIEWS TO A SPECIFIC USER
-- JUST CHANGE THE VARIABLE @USER TO WHATEVER USER YOU WANT
-- RUN THIS IN THE DATABASE YOU NEED TO AFFECT
DECLARE @user varchar(50)
SET @user = 'guest'

DECLARE @tmpName varchar(500)
DECLARE @sql varchar(8000)

DECLARE Objects_Cursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'V' AND status > 0 ORDER BY name
OPEN Objects_Cursor
FETCH NEXT FROM Objects_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
     --SELECT @tmpName =
     FETCH NEXT FROM Objects_Cursor INTO @tmpName
     --PRINT @tmpName
     SET @sql = 'GRANT ALL ON [' + @tmpName + '] TO ' + @user
     EXEC (@sql)
END
CLOSE Objects_Cursor
DEALLOCATE Objects_Cursor
0
 
LVL 2

Expert Comment

by:NaderYacoub
ID: 12373990
I'm glad my solution worked for you

- Nader Yacoub
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now