Solved

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

Posted on 2004-10-18
10
404 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

772 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