Granting Execute permission on All Stored Procedure

How to grant Execute permission on All Stored Procedure for a new User in SQL Server?


Most of the application have users who are not creator (owner) of the Stored Procedures. Hence the users don't have EXECUTE permission on any of the Stored Procedures by default, considering that they are not part of any existing Role that has execute permission.
 
I have created a small script which returns a string that grants permission to the new User to all the Stored Procedures. Here is the script:
 
SELECT 'GRANT EXECUTE ON ' + name + ' [UserName]'
FROM sysobjects WHERE xtype IN ('P')
 
You need to copy the script generated by above query and execute. Alternatively, you can use below script - where you need not to copy the script generated by above query. Just execute the below script in required database.

DECLARE @UserName [varchar](128), @SQL [varchar](1000)
SET @UserName = '[UserName]'


DECLARE Cur CURSOR FOR
   SELECT 'GRANT EXEC ON ' +
   '[' + u.[Name] + ']' + '.' +
   '[' + o.[Name] + ']' +
   ' TO ' + @UserName  AS SQLCommand
FROM dbo.sysobjects o
JOIN dbo.sysusers u
  ON o.[uid] = u.[uid]
WHERE o.[Type] = 'P'

OPEN Cur
FETCH NEXT FROM Cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SQL
   EXEC (@SQL)
   FETCH NEXT FROM Cur INTO @SQL
END
CLOSE Cur
DEALLOCATE Cur

Cheers !!!

What is NEWSEQUENTIALID

NEWSEQUENTIALID() creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

What is the use UPDATE_STATISTICS command?

We add statistics on the columns that don't have statistics in order to boost query performance. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan but in some cases you can improve query performance by using UPDATE_STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

UPDATE_STATISTICS command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

Here is T-SQL Command:
1. Updating All Statistics with sp_updatestats
EXEC sp_updatestats

2. Update all statistics on a table
UPDATE STATISTICS TableName

3. Update the statistics for an index
UPDATE STATISTICS TableName, IndexName

For more information, click here

The SQL Server Query Optimizer has the ability to use statistics on columns that are not indexed in order to create more optimal execution plans. Doing so can increase performance without having the overhead of indexes. Column statistics are generated automatically by SQL Server when queries are being optimized by the Query Optimizer. For example, column statistics are automatically created when there are currently no statistics available for the column being used as a search argument in a WHERE clause or a JOIN clause. There are two cases when SQL Server will not automatically create column statistics, even when they fit the criteria described above. These include: when the costs needed to create the column statistics are larger than the costs of the query plan; and when SQL Server is too busy.

If you want to find out if an index has had its indexes updated or not, you can use the DBCC SHOW_STATISTICS command, like this:

DBCC SHOW_STATISTICS(table_name, index_name)

This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining


Reference: MSDN