Getting rid of DBO and SA access

Question

Getting rid of DBO and SA access

Bakr Ben Ayad

Old Hand

Points: 325
More actions
July 16, 2007 at 9:10 am

#175403

Hi
our applications use generic database IDs that have SA and DBO access. for security reasons, we want to create specific application IDs that don't have SA and DBO access.
What is the best way to do this? is there a better way besides granting permissions to every sql object accessed by the new specific application ID?
please help,

Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

If your application is .NET it is not good practice to remove DBO but you can create a user with DBO permissions that the application can use, but removing SA is not an issue. Hope this helps.

Kind regards,
Gift Peddie

noeld SSC Guru Points: 96590 More actions · Answer 2

you should create "roles" and then add users to roles as appropriate.

* Noel

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

ASPNETDB creates the stored procedures in DBO schema, we used that in my last contract for a GM Winform application so in .NET application you need DBO permissions granted to whatever executes the stored procs.

Kind regards,
Gift Peddie

noeld SSC Guru Points: 96590 More actions · Answer 4

I am not sure what ASPNETDB is but you can definitely call an stored procedure in the DBO schema without being db_owner just make sure that the account (role) used have EXECUTE permissions on the stored procedures.

* Noel

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

In a .NET web application it will take a developer four months to do the work ASPNETDB will create for the application so you are just adding to employer's cost of creating the application. Yes in Win2003 you can use the Network service account for it but if not execute is too limited to perform the task. I will not take a job where I will develop an application in .NET with execute permission because you just added more work to me that i don't need to do.

Kind regards,
Gift Peddie

Bakr Ben Ayad Old Hand Points: 325 More actions · Answer 6

Thank you guys for your replies.

the application that I'm working on is using classic asp. what I'm doing is that I created a user login in sql server for this application and I created a role that is linked to thIS user login. when I try to give permissions to every single SQl object, the application works fine except for some system Stored procedures like 'sp_makewebtask'. even if I give execute permissions on this latter, the application still complains about it.

I think it has to do with SA access

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

If you are in Win2003 you can use the Network Service account and use the DBO schema and you will be fine, if you are in Win2k then I will look for what you need and post again. The problem is IIS6 runtime expect DBO because it can use the Network service account.

Kind regards,
Gift Peddie

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Use Database roles
Don't use the public role
Put the users into database roles
Assign permissions against the roles, not the users
Assign permissions at the Schema level if they need to be able to say, execute every stored procedure. For instance:

GRANT EXECUTE ON SCHEMA::dbo TO MyDBRole

This will ensure you don't have to give permissions to every last object due to securables/scope hierarchy.

K. Brian Kelley
@kbriankelley