Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Tuesday, July 7, 2009

Granting Execute Permissions to SQL Users

Granting exectution permission to users in SQL used to mean giving them DBO or granting permission to each stored proc. individually.

Last year I found a stored proc that would grant permissions when called but when new stored procs are created, you have to re-run it.

Today I found out that there is a way in SQL 2005 and later to create a new role that has execute permissions to all with the following Code:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor



After you have executed that script you can then see the role in the permissions list and assign it there. The user will then have permission to stored procedures added later.

Thursday, January 22, 2009

Modifying a table in SQL 2008 Mgt Studio

When adding a field or making other changes that requires a table be dropped and re-added, you may receive an error like 

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

If that happens, you need to update the management studio options as described here.

Saturday, November 15, 2008

Setting up SQL server 2008 (to host Dynamics NAV)

I installed SQL 2008 Developer edition because the SQL 2005 Express edition I had been using isn't able to be accessed from web applications running outside the VS IIS instance and I needed to test an IIS hosted web service application.

The install took nearly 2 hours!  Becuase I was installing SSIS, SSRS, and the other tools.  It required an upgrade to .Net 3.5 SP1, VS 2008 SP1, in addition to 2.5 gb of HD space.

After the install, SQL 2005 Express could no longer access the databases so I had a momentary freak-out until I could mount them into SQL 2008, at which point I uninstalled SQL 2005 Express.

Upon attempting to connect from Dynamics NAV, I was reminded of the SQL setup you have to do for Dynamics NAV.  I always seem to forget that, and I am not the only one as I have been onsite to implement behind another analyst a few times and found that the setup hadn't been completed.  Here are the SQL modifications required for Dynamics NAV:

You have to set a trace flag by adding "-T 4616" to the startup parameter string:

You have to add two extended stored procedures (xp_ndo_enumusersids and xp_ndo_enumusergrous) to the master database and grant execute permissions to the public role  as explained here:


As a side note, all clients from 4.0 SP3 and later are compatible with SQL 2008, so there is no reason to use SQL 2005 with new installs.



Wednesday, November 12, 2008

Inserting into SQL table with Identity field

Sometimes you want to insert records into a table that has an Identity field but you need to insert a value into that field (normally the system assigns identity values).

The following code allows you to do that:


SET IDENTITY_INSERT BasicAttribute ON

insert into BasicAttribute (rid, typeid, code, description) values (0,0,'DEFAULT','Default')

SET IDENTITY_INSERT BasicAttribute OFF

Tuesday, October 21, 2008

SSIS Scripts

I have been working with SSIS for a couple weeks, and while I understand a few of the common tools I am really a novice in many areas.

Today I needed to prevent the Available Quantity on inventory from going negative since we don't want to expose that to users of the site I am working on. There are no build in commands to do that in the "Derived Column" object, so I had to use a script object.

For some reason VB is the only language supported, but it was really easy. After identifying which columns were available and whether they were read-only or not, the only code I had to write was:Row.AvailableQty = Max(Row.AvailableQty, 0).

The entire script follows, all but one line was generated by the system.

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Row.AvailableQty = Max(Row.AvailableQty, 0)

End Sub

End Class

Monday, October 20, 2008

SQL Case

Select
CASE [World Wide Service]
WHEN 1 then [Shipping Agent Code] + '-W'
ELSE [Shipping Agent Code]
end,
Description, [World Wide Service] from [COMPANYNAME$E-Ship Agent Service]

Backslashes in SQL Queries

I keep forgetting this one so I better write it down:

Brackets ([]) work on string constants (forgive the non sql terminology) as well as fieldnames.

When passing a fully qualified domain name into a sql query 'domain\userid', the backslash will cause errors and, at lease in the SQL Management studio, it can not be escaped via double-backslash 'domain\\userid'.

To pass a backslash in, surround the string in brackets inside the ticks: '[domain\userid]'