Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Building Queries based on results

Posted on 2006-01-12
5
Medium Priority
?
714 Views
Last Modified: 2008-02-01
I have a process that has to drop a table and rebuild it.  It at that point losses all the permissions associated with that table.  I want to build a stored procedure that will return a list of grant statements based on the sp_helprotect results.  Is it possible to loop through each of those permissions and dynamically build the queries that can be ran after the process is complete.

Example sp_Helprotect results:
Owner      Object       Grantee                     Grantor     ProtectType   Action
dbo      FDRCodes      MyCMCUser      dbo      Grant               Insert      
dbo      FDRCodes      MyCMCUser      dbo      Grant               Select      
dbo      FDRCodes      MyCMCUser      dbo      Grant               Update      

The stored procedure would return:

GRANT INSERT ON FDRCodes TO MyCMCUser
GRANT SELECT ON FDRCodes TO MyCMCUser
GRANT UPDATE ON FDRCodes TO MyCMCUser

FYI this is SQL Server
0
Comment
Question by:tw_chase
[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
  • 3
5 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 15687207
rather than dropping and then re-building the table, why don't you simple TRUNCATE the table (that will delete all of the records, leaving the table completely empty) and then re-fill it?  That way all of the permissions are unchanged.
0
 
LVL 1

Expert Comment

by:wzard
ID: 15690810
The sql statement to generate the several SQL-statements from the table you described should be as following:

select protecttype & ' ' & action & ' on ' & [owner] & '.' & [object] & ' to ' grantor & '.' & grantee
from [table described above]

as you can build stored procs, pub the results in a cursor and let them execute separately by the SQL-server execute statement
0
 

Author Comment

by:tw_chase
ID: 15691067
WZard,

You solution doesn't work.  You can't run a select statement against a stored procedure (sp_Helprotect).  Run sp_Helprotect in Query Analyzer against any DB and you will see what I am talking about.  It is a system stored procedure.  What I need and I don't now if it is possible is to run that stored procedure and go down the list of results and dynamically build a script of "GRANT" statements to be ran once that drop process takes place.  There is no other way around the drop.

0
 
LVL 1

Accepted Solution

by:
wzard earned 2000 total points
ID: 15691352
ofcourse, i interpreted it as a function, also the query was not entirely correct i saw.

however a more solid solution:
The stored procedure executes a select statement. this can be changed (as explained below) to fill a temporary table.
On this, a select statement as the above can be executed and the sql can be generated.

As the code of the procedure is probably copyrighted, i will not publish it here, unless asked for. however i will give you the changes i made.

in the sql query analyzer, you can right-click the sp_helprotect procedure and say script to new window as CREATE.
(within the tree: database Master, Stored Procedures)

in the first part of the script youll see the line:

CREATE PROCEDURE sp_helprotect

change this name to give it a reasonable name for yourself

in the last part of the script, you'll see the following lines:

/*  Output the report      */
EXECUTE(
'Set nocount off

SELECT      ''Owner''

just before EXECUTE , add this line

create table #mytmp ([owner] varchar(255), [object] varchar(255), grantee varchar(255), grantor varchar(255), protecttype varchar(255), [action] varchar(255), [column] varchar(255))

just before SELECT... add this line:
INSERT INTO #mytmp

One of the last lines of the script reads:
Return (0) -- sp_helprotect

BEFORE this line, add the following

select [protecttype] + ' ' + [action] + ' on [' + [owner] + '].[' + [object] + '] to [' + [grantor] + '].[' + [grantee] + ']'
from [#mytmp]

Now run this script which will create the procedure and try it, the select statements will come out like they were in a table

When you right-click the resultset and click copy, you can paste the sql-script into the sql-window and execute it
0
 
LVL 1

Expert Comment

by:wzard
ID: 15691404
i found out the produces statements are rejected because of the dots. therefore the last addition should be:

select [protecttype] + ' ' + [action] + ' on [' + [object] + '] to [' + [grantee] + ']' from [#mytmp]
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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