Granting privileges to stored procedures

I've just encountered a user who wanted to run a stored procedure or 3 against as SQL Server database on which he ws not the owner of the stored procedures. This was a bit of a surprise because although added to the database after creation he is in there as dbo. Never mind needed a solution sooner rather than later.

It's simple really in that Securables can be manually editted for that user, unfortunately there are a large number of procedures potentially required - in fact it is a 3rd party product so we won't have a full list of required procedures until we've run the process repeatedly weeding out each issue as it arises.

The answer :

select 'Grant Execute on ' + name + ' to "username"'
from sysobjects where xtype in ('P')

This works nicely by outputting a full list of statements that can be copied from the output and executed in the query window in SSMS. The important feature here is the username - this is the full username ie if you are using Windows accounts it will be DOMAIN\user enclosed in the shown speech marks to ensure spaces are handled properly.

Restoring SQL Server databases to a different machine

SQL Server is extremely flexible and allows databases to be backed up and restored very easily and independently of hardware requirements.

There is, however, one nagging problem when restoring a database to a different machine(say copying a development database to a live environment) and that is SQL Servers security system.

SQL Server has logins against the instance and then users against each database - so each database has security independent of all the other databases unless logins have been granted access to any server roles.

When the database restore process completes there might be users listed on the database who do not have logins on that instance or even if the user has a login the guids might not match.

So for SQL Server 2000 and above firstly identify what the problem is by running :

exec sp_change_users_login @Action='Report'

Then for each UserName identified you can either match existing logins to database user using :

exec sp_change_users_login 'Auto_Fix', @UserNamePattern ='username'

(replacing username with the UserName from the first query).

To save having to check which to do manually it does no harm to run that command for users that don't exist yet - just run the first command again to identify who is still not sorted and then run the following to create logins for each remaining user :

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Just make sure you are connected as an admin user and you are connected to the correct database before running the commands.

It's also worth pointing out that this isn't news - I was passed most of this info when I started the role and have modified as I've gone along and there are plenty of postings out there with the same /similar information.

Abstract Rose

I loaded up the hoverfly photo yesterday and had some positive feedback so I thought I would start to upload other pictures. This one is one of a series taken of roses from my then garden and was taken with a Fuji Finpix S602 zoom when it had just been released. I just popped the roses in a vase in my patio doors and snapped away testing the settings that the camera could provide. The resolution is low in today's terms - especially as this was a crop of the larger picture but I'm still happy with the result.

When is a bumble bee a hoverfly?

I was showing Alex all the bumble bees busily collecting pollen from the thistle flowers in the garden - at least 4 different types of bumble bee and a bog standard bee all buzzing away and decided to take some pictures. Yes, lots of pictures of bumble bees eveyone of them at least a little disappointing but the sole picture I took of a hoverfly came out beautifully. Taken with Panasonic TZ5 in macro mode - so pretty much point & press. The specs on the flower are specs of pollen not bad pixels!

Changing timings of SQL server agent jobs on mass

This is an example of how to change the timing of a series of jobs on the SQL Server agent - in this instance we needed to change all of our full backups to run every third day (well Monday then Thursday, Tuesday then Friday and Wednesday and Sunday leaving Saturday for other maintenance activities).

-- Routine to set the times of all jobs named "Full backup%" on a machine, splitting the jobs into 3 batches, Mon&thur, Tue & Fri and Wed & Sun
-- log backups should be occuring the rest of the time which leaves saturday for maintenance work
-- output is a series of statements which when executed to text output can be copied and executed in a new query window.
set nocount on
create table results (dsp varchar(500))
declare cJobs cursor fast_forward for
select schedule_uid from msdb..sysschedules where name like 'Full Backup%' order by name, schedule_uid

declare @UID varchar(128)
declare @count int
declare @MT int
declare @TF int
declare @WS int
declare @MTStart int
declare @TFStart int
declare @WSStart int

set @MT = 0
set @TF = 0
set @WS = 0
set @MTStart = 40000
set @TFStart = 40000
set @WSStart = 40000

set @count = 1
open cJobs
fetch next from cJobs into @UID
while @@fetch_status = 0
begin
if @count%3 = 0
begin
set @MT = @MT+1 -- Mon & Thur = freq_interval = 18
insert into results select 'update msdb..sysschedules set freq_interval = 18, active_start_time='+cast(@MTStart as varchar)+' where schedule_uid = '''+@UID+''''
set @MTStart = @MTStart + 200
if @MTStart = 46000 begin
set @MTStart = 50000
end else if @MTStart = 56000 begin
set @MTStart = 60000
end else if @MTStart = 66000 begin
set @MTStart = 70000
end else if @MTStart = 76000 begin
set @MTStart = 80000
end
end else if @count%2 = 0 begin
set @TF = @TF+1 -- Tue & Fri = 36
insert into results select 'update msdb..sysschedules set freq_interval = 36, active_start_time='+cast(@TFStart as varchar)+' where schedule_uid = '''+@UID+''''
set @TFStart = @TFStart + 200
if @TFStart = 46000 begin
set @TFStart = 50000
end else if @TFStart = 56000 begin
set @TFStart = 60000
end else if @TFStart = 66000 begin
set @TFStart = 70000
end else if @TFStart = 76000 begin
set @TFStart = 80000
end
end else begin
set @WS = @WS+1 -- Wed & Sun = 9
insert into results select 'update msdb..sysschedules set freq_interval = 9, active_start_time='+cast(@WSStart as varchar)+' where schedule_uid = '''+@UID+''''
set @WSStart = @WSStart + 200
if @WSStart = 46000 begin
set @WSStart = 50000
end else if @WSStart = 56000 begin
set @WSStart = 60000
end else if @WSStart = 66000 begin
set @WSStart = 70000
end else if @WSStart = 76000 begin
set @WSStart = 80000
end
end
fetch next from cJobs into @UID
set @count = @count +1
end
close cJobs
deallocate cJobs
print 'Mon Tue:'+cast(@MT as varchar) +', Tue Fri: '+cast(@TF as varchar)+'Wed Sun : '+ cast(@WS as varchar)
select * from results order by 1
drop table results

Update : we have discovered that the table sysjobschedules hold start times too but cycling the agent should bring everything back into step.

SQL Server 2005 BIDS Installation Problem

Here’s a new one – I’ve just installed SQL Server 2005 and selected everything including client tools and Business Intelligence and the install completes perfectly only to find that BIDS is not installed, it is missing, no trace of devenv.exe or related components on disk.

 

After several attempts via Google I finally found an answer that works here http://weblogs.asp.net/kdente/archive/2007/03/07/the-case-of-the-missing-business-intelligence-studio.aspx, it seems from MSDN that lots of people have the problem so I’ll repeat the answer :

 

Goto the installation CDs, find disk 2 and in \setup run firstly vs_Setup.exe – you’ll get absolutely no notification that anything is happening for what seems like an age and then it will be finished. Then run from the same location sqlrun_tools.msi and select the components you want – in my instance they were shown as missing by it seems that is not always the case so just go and reselect everything you want and run the install.

 

Job done.

 

Another MS feature to make my life painful.