• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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

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
Mitchell_
Asked:
Mitchell_
  • 5
  • 3
  • 2
1 Solution
 
NaderYacoubCommented:
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
 
Mitchell_Author Commented:
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
 
NaderYacoubCommented:

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

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mitchell_Author Commented:
I will bump the points to 500 if you could paste some code in.

0
 
mikejrobisonCommented:
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
 
mikejrobisonCommented:
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
 
NaderYacoubCommented:

-- 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
 
Mitchell_Author Commented:
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
 
NaderYacoubCommented:
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
 
NaderYacoubCommented:
I'm glad my solution worked for you

- Nader Yacoub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now