To upload a file to field with a type of varbinary(max), use the following:
To update a record
update <TABLE>
set <FIELD> = (Select * FROM OPENROWSET(BULK '<PATH>', SINGLE_BLOB) <FIELD>)
where <FINDRECORD>
To insert a record
insert into <TABLE>
Select
<FIELDS>,
<FIELDNAME>.*
FROM OPENROWSET
(BULK '<PATH>', SINGLE_BLOB) <FIELDNAME></code>
The path has to be accessible by the SERVER to update. Make sure the varbinary is set to varbinary(max).
The following code creates a login for a user and assigns it datareader and datawriter access to the database under which this script is run.
Note: This does NOT create permissions to execute stored procedures. To do this, you need to either give the user ‘dbo’ role, or use GRANT EXECUTE statements for each stored procedure.
--Add New User Permissions for X.
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'XAppUser')
BEGIN
EXEC master.dbo.sp_addlogin @loginame = N'XAppUser', @passwd = N'pwd#', @deflanguage = N'us_english'
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'XAppUser')
EXEC dbo.sp_grantdbaccess @loginame = N'XAppUser', @name_in_db = N'XAppUser'
GO
EXEC sp_addrolemember N'db_datareader', N'XAppUser'
GO
EXEC sp_addrolemember N'db_datawriter', N'XAppUser'
GO
We are at the final stages of development and preparing the database scripts to be built. We test on a staging server and realise the user we created doesn’t have permissions to use the stored procedures we created! Ahh!
This means we forgot to grant our stored procedures execute permissions for our user. Normally we put this at the bottom of our stored procedure script:
GO
GRANT EXECUTE ON [dbo].[spAddressInsert] TO [OurUser]
GO
We are at the end of development and it will take way too long to add this to each script. What to do!
We can query the database to find all stored procedures in the database, and create a list of grant commands. Note they must be separated with GO’s. Here is some sample code to do this:
SELECT 'GRANT EXECUTE ON [dbo].['+[name]+’] TO [OurUser]
GO’
FROM sys.objects WHERE TYPE=’P’
Run this in a SQL management program (SQL management studio or Toad) and output the result to text. You will have a list of grant commands for all of your stored procedures!