Solved

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

Posted on 2004-10-18
10
423 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_
[X]
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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

705 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