How do I grant EXECUTE permissions on all stored procedures in a database?

At ReDoc - Rehab Documentation Company, where I am employed, we distribute our software to thousands of sites and once in a while we would get a support call and after tracking it down we found that our standard user had no permissions to execute this or that Stored Procedure.

So it fell to me to make sure that when our packages left our walls that our standard user would have these permissions.  In that effort I developed a SQL that would grant EXECUTE permission to a certain user for ALL Stored Procedures in the database.

here it is:



-- Author: Thomas Ross
-- Date: 10/20/2009
-- desc: Grant EXEC on all stored procedures to User X for the currently selected database.

declare @user varchar(100); set @user='ReDocUser'

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = @user)
begin

declare @st varchar(max)

DECLARE @name varchar(max)
DECLARE db_cursor CURSOR FOR

SELECT name From sys.procedures order by name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
set @st='grant exec on '+@name+' to '+@user
exec (@st)
print 'Granting EXEC on '+@name + ' to '+@user

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
end
else
print 'The user specified does not exist.'

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.

How to make your Stored Procedures reentrant, or in a format that can be executed any number of times on any database.

If your SQL development is like mine, then delivering your Stored Procedures can require some special formatting.

The company I am privileged to work for has many more than 1,000 live customers, each with their own SQL Server.  So when I write a Stored Procedure I need to produce it in a format for a brand new install and for an upgrade.

This upgrade process can be tricky.  So the method that we have developed will require that my SQL run on a database that has a copy of my Stored Procedure already and a database without it.  This means that I need to start my Stored Procedure with CREATE or ALTER, but which do I choose?

You may now be thinking, "why not just drop the old (if it exists) and always perform a CREATE?"  Well, the customer may have altered permissions on the Stored Procedure that need to be preserved.  Retrieving, saving and reestablishing these permissions can get complex very quickly.  Especially since I have some SQL servers that I am not given enough RIGHTS to do that.

So one easy method that I have used for a number of years is to submit all my Stored Procedure work beginning like this:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'NewSP')
EXEC ('CREATE PROC NewSP AS SELECT ''Mini SP Version.''')
GO

ALTER Procedure NewSP

AS

select 'Full SP SQL Code' as [My wonderful SQL Code]

GO

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = N'My_DB_User1')

Grant Execute on NewSP to My_DB_User1

GO

IF EXISTS (SELECT * FROM sys.schemas WHERE [name] = N'My_DB_Schema')

Grant Execute on NewSP to My_DB_Schema

GO

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.

How do you drop all constraints on a table in Microsoft SQL?

For various applications I have needed to drop all constraints on a given table, so I developed this tool that I have often used.  This tool however does NOT save the SQL to recreate the constraints.  So if you are looking to unshackle a table from all of its constraints you might try this.





-- Author: Thomas Ross RosSQL RosSQL.Blogspot.com
-- Date: 5/30/2012
-- Desc: Drop all constraints on a given table.
-- Instructions: Change the name of the table on the line given.
-- Turn the Safety OFF


DECLARE @Safety datetime
set @Safety='5/30/2012' --<<***** You MUST set this to today's date to modify the database.

declare @TblName varchar(1024); set @tblName = 'ApptSchedule' --<<***** Set to '' to see a complete list or choose a table name.
declare @st varchar(max)
set @st=''

select so.name as TableName, def.name as [These Constraints Dropped]
from sys.columns SC
join sysobjects so on so.id=sc.object_id
join sysobjects def on def.id=sc.default_object_id
where so.name=@TblName or @TblName = ''

select @st = @st + 'alter table ['+so.name+'] Drop Constraint [' +def.name+'];'
from sys.columns SC
join sysobjects so on so.id=sc.object_id
join sysobjects def on def.id=sc.default_object_id
where so.name=@TblName or @TblName = ''

if (@Safety between getdate()-1 and getdate()+1)
begin
select @st
exec (@st)
end
else
select 'Constraints NOT dropped because SAFETY is ON.' as [@Safety]

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.

Have you ever wanted to test the speed of SQL command but after the initial execute, the SQL returns much faster because all the data has been cached into memory?  So how do I release all the memory cache and force SQL Server to reload all that data and work as hard as I can make it?




-- Date: 9/15/2010
-- Clean memory to run a proc again without the use of memory cache.

DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE ( 'ALL' )
dbcc dropcleanbuffers
dbcc freeproccache

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.

SQL Count of Rows and a FAST Count, that returns immediately even on large database tables.

In SQL you can find out how many rows are in a table by issuing the command:

Select count(1) from TableName

This however can take quite a while on a large table, especially if the Calculate Statistics has not been done in a while.

You may have noticed that this command has Count(1) and not Count(*).  This way the Count command does not have to go to the database disc file to retrieve the information.  But even with this method, the Count(1) command can take up to a few minutes to return.

One way to return the number of rows in a table with a very good level of accuracy, almost immediatly is to use this command:

SELECT rowcnt FROM sysindexes WHERE object_name(id) = 'TableName' AND indid IN (9,1)

Give this a try and find another excuse to go get a cup of coffee.

(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.

SQL format of date and time, the way I like it!

Often when delivering a date in one of your Stored Procedures, you will want to format it to look like what your Product Owner calls for.  Of course this should be done in the application, but you're reading this post because you have an occasion where you need to reformat that date, don't you?!  So you might try the following.








-- Time
SELECT ltrim(right(convert(varchar(25), getdate(), 100), 7)) as [HH:MIAM]

-- Favorites
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

-- Date
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) as [Mon DD YYYY HH:MMAM]
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) as [HH:MM:SS]
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) as [Mon DD YYYY HH:MI:SS:MMMAM]

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

SELECT CONVERT(VARCHAR(24), GETDATE(), 113) as [DD Mon YYYY HH:MM:SS:MMM]
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

SELECT CONVERT(VARCHAR(19), GETDATE(), 120) as [YYYY-MM-DD HH:MI:SS(24h)]
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) as [YYYY-MM-DD HH:MI:SS.MMM(24h)]
SELECT CONVERT(VARCHAR(23), GETDATE(), 126) as [YYYY-MM-DDTHH:MM:SS:MMM]

SELECT CONVERT(VARCHAR(26), GETDATE(), 130) as [DD Mon YYYY HH:MI:SS:MMMAM]
SELECT CONVERT(VARCHAR(25), GETDATE(), 131) as [DD/MM/YYYY HH:MI:SS:MMMAM]


(c) 2014 RosSQL, all rights reserved.  This publication may not be republished without the expressed written permission of RosSQL.