Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-10-18
10
410 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
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…

808 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