SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert Existing Stored Procedure To CLR Stored Pr
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

azinyama
Starting Member

7 Posts

Posted - 11/05/2012 :  03:34:21  Show Profile  Reply with Quote
Good day all!!!

I have just started learning about CLR Stored Procedures and I wanted to know how to convert my existing stored procedures to CLR Stored Procedures. Could you show me, with code, what this stored procedure should look like. I'm using VS 2010, VB.Net, MSSQL Server 2008.



ALTER PROCEDURE [dbo].[user_login] 
	-- Add the parameters for the stored procedure here
	@User_UserName VARCHAR(50), 
	@User_Password VARCHAR(50),
	@Station VARCHAR(50), 
	@Users_RowID INT OUTPUT,
	@Users_Name VARCHAR(100) OUTPUT, 
	@Success BIT OUTPUT, 
	@Default_Message VARCHAR(200) OUTPUT
AS

BEGIN TRANSACTION
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

	DECLARE @rowsaffected INT
	DECLARE @User_Status_RowID INT
	DECLARE @User_Cursor CURSOR

    -- Insert statements for procedure here
	SET @Success = 0
	SET @Users_RowID = 0
	SET @Users_Name = ''
	SET @User_Status_RowID = 0
	
	IF EXISTS (SELECT * FROM [dbo].[user] WHERE User_UserName = @User_UserName AND CONVERT(varbinary, User_Password) = CONVERT(varbinary, @User_Password))
		BEGIN 			
			SELECT @Users_RowID = User_RowID, @User_Status_RowID = User_Status_RowID 
				FROM dbo.[user] 
				WHERE User_UserName = @User_UserName AND User_Password = @User_Password
				
			SELECT @Users_Name = (User_SName + ', ' + User_FName + '. ' + Title_Descr) 
				FROM dbo.[user] 
					INNER JOIN dbo.title ON (dbo.[user].Title_RowID = dbo.title.Title_RowID) 
				WHERE User_UserName = @User_UserName AND User_Password = @User_Password

			IF ((LOWER(@User_UserName) <> 'administrator') AND (@User_Status_RowID = 1))
				BEGIN
					SET @Default_Message = 'User ''' + @Users_Name + ''' is already logged in.'
					SET @Success = 0
					RETURN
				END
			ELSE
				BEGIN					
					EXEC [dbo].[user_status_update] @User_UserName, @Station, 1, @rowsaffected OUTPUT
					
					IF ((@rowsaffected = 2) AND (@@ERROR = 0))
						BEGIN
							SET @Default_Message = 'You have been logged in successfully'
							SET @Success = 1
						END
					ELSE
						BEGIN
							ROLLBACK TRANSACTION
							SET @Default_Message = 'An error occured while attempting to log you in. Please try again'
							SET @Success = 0
							RETURN
						END
				END
		END
	ELSE 
		BEGIN
			ROLLBACK TRANSACTION
			SET @Default_Message = 'Invalid username and/or password. Try again'
			SET @Success = 0
			RETURN
		END

	SET NOCOUNT ON;

COMMIT TRANSACTION


Edited by - azinyama on 11/05/2012 03:35:14

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/05/2012 :  04:10:59  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Dont.

CLR code should be used only in exceptional circumstance -- when there isn't a good way of achieving the same goal using sql -- or when there is a real performance benefit to such.

The stored procedure you posted does some simple checking of entries in a table and updates same under certain conditions.

There would be absolutely no point writing this using the CLR.

CLR routines generally are used for implementing logic that is hard or impossible to do well in vanilla tsql -- a good example is a string splitting:
While good sql methods exist (number table splitters)
they are still poor compared to a good clr splitter for big strings.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

azinyama
Starting Member

7 Posts

Posted - 11/05/2012 :  09:01:55  Show Profile  Reply with Quote


I am developing an application that is going to be setup on a server that is in a public area, that anyone will have access to. My thinking was that CLR Stored Procedures are more secure than the regular stored procedures because they can't be edited from the server. The stored procedure I posted is the log in procedure I'm using. However the other procedures I have are more complex, carrying out billing and remittance logic, etc. So I wanted to hide the logic from the anyone who might try to make unauthorized changes. I posted the simplest stored procedure so that I could learn how to implement it starting with the basics.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  09:09:36  Show Profile  Reply with Quote
Stored procedures can be set up to be very secure. For example, you can grant someone (users or roles) permissions to only execute the stored procedure - and nothing else. You will need to make sure that the rules about ownership chaining, and some other aspects are taken care of. None of it too hard, and is something that people routinely do to secure their databases and servers.

That assumes, of course, that the public who has access to the server/database do not have sysadmin role on the server. If they do, they can do pretty much anything. In any case, you should not set it up to have everyone sysadmin roles. Microsoft recommendation is that you give a user the least amount of privileges required for them to do what they need to do and nothing more.

You can also encrypt stored procedures - Encrypting makes it non-readable to everyone. Also, I recall reading somewhere that the encryption used is not very secure.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/05/2012 :  10:49:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
As Sunitabeck has mentioned -- you should secure this using roles. You shouldn't try to get obfuscation using the CLR. that's not it's purpose

If you really need the stored procedure to be unreadable then you should encrypt it. Just remember to keep an unscripted copy in your source control repo.



Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

azinyama
Starting Member

7 Posts

Posted - 11/05/2012 :  11:13:40  Show Profile  Reply with Quote

Thank for the replies and advise guys. Will look into the permissions option.

Another question though...

What would I then do about preventing people from using Windows Authentication to log in???
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  11:52:41  Show Profile  Reply with Quote
You don't necessarily have to prevent people from using Windows Authentication as long as they are not administrators on the server box. Other windows users won't have access to SQL Server unless you add them (or an AD group that they are member of) as authorized users.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000