Using the 'sa' login

[johnc83]

Hi all,

In the creation of my database (SQL Express 2005) and my application (VB.NET) I have used the 'sa' login for SQL without exception. I am now starting to heed the warnings of not using this login and have set up a new user.

I would just like a little help with a couple of things if someone would be so kind...

1. The first stored procedure called by my app no longer works..

The EXECUTE permission was denied on the object 'spCheckPassword', database 'MyDatabase', schema 'dbo'.

While running the risk of providing not enough information on my situation, could anyone advise as to why this isn't working now pls.

2. Now that I have moved onto my other login(which has very limited privelages), should I continue to add/modify objects in my database using 'sa' through SQL Server Management Express or increase privelages using my new login and use that instead?

Thanks for any help

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Jamfool]

--CREATE A ROLE
CREATE ROLE [my_proc_exec] AUTHORIZATION [dbo]
GO

--ASSIGN EXPLICIT PERMISSIONS TO ROLE
-- in your case you need execute permissions on procs

GRANT EXECUTE ON [dbo].[someproc] TO [my_proc_exec]
GO
--ADD USER INTO ROLE
EXEC sp_addrolemember N'my_proc_exec', N'youruser'
GO


never use sa.

 

[johnc83]

Hi Jamfool, thanks for that.

If you advise 'never use sa', what do you recommend as the alternative?

Set up 2 different users, one with slightly higher privelages (Create/modify tables) and a basic one for my application (Run SP's, UPDATE's etc..)

Thanks again

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Jamfool]

if you need to create modify tables then you can assign dbo to the higher priv account.

The other lesser acc could probably be handled just by using a role for permissions on the procs as previous reply and adding the user in to the reader role to access the tables.