SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • Training
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Performance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
SQL Azure
Developer
General DBA
ASP.NET / ADO.NET
SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...
Latest Articles

A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server

More     
 
Latest FAQ's

Add Node to A SQL Server failover Cluster failed with invalid ...
SQL Server Destination remote server error
Setting Up Data And Log Files For SQL Server
Will Check Constraints Improve Database Performance?

More     
   
Latest Software Reviews

dbForge Review
Spotlight on ApexSQL Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Data Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Doc 2008

More     

articles >> general dba >> Using Stored Procedures to Manage SQL Server ...

Using Stored Procedures to Manage SQL Server Security

By : Brian Lockwood
Feb 13, 2003

Page 2 / 2

5. To help automate this process you can run the following stored procedure. This will automatically add Execute permissions to all non-system/VSS procedures in your database. You can tweak this code to process only a subset of your stored procedures.

CREATE PROCEDURE prc_gen_CreateGrants AS

/* ------------------------------------------------------------
PROCEDURE: prc_gen_CreateGrants

DESCRIPTION: Grants Execute permissions on all procs in database
for Login MyLogin

AUTHOR: Brian Lockwood 3/15/00 5:38:48 PM
------------------------------------------------------------ */

DECLARE @ExecSQL varchar(100)

DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs


OPEN curGrants

FETCH NEXT FROM curGrants

INTO @ExecSQL


WHILE @@FETCH_STATUS = 0

BEGIN -- this will loop thru all your own procs and grant Execute privileges on each one

Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END

Print @ExecSQL

FETCH NEXT FROM curGrants INTO @ExecSQL

END

CLOSE curGrants
DEALLOCATE curGrants

 

Published with the express written permission of the author. Copyright 2002


<< Prev Page         








C# Help and Tutorials | PHP MySQL Tutorial | Sharepoint Tutorial | Azure Tutorial | Cloud Hosting Magazine | ASP.NET Tutorials | Windows Server Help | Windows Phone Pro | Silverlight Ace | Visual Studio Tutorials | Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | SQL Server Training Videos | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views


              © 2010 Jude O'Kelly. All rights reserved