Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steven.nbg
Starting Member
5 Posts |
 Posted - 2008-01-08 : 04:31:26
|
| Hello everybody, I have a database, where all tables and stored procedures look like this:dbo.tabledbo.spIs this naming because of the db owner creating the tables and stored procedures?The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...How can i solve my problem!Using the user without db_owner rights brings in my application the error:The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.Thanks for help!!!!Steven****--------------------------------------Solution:I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005! |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-01-08 : 10:36:08
|
Granting db_owner to the application user that the AsP application logs in as can be a big security risk. Have a look around for SQL Injection Vulnerabilities on Google, and you will see what I mean. A better way would be to set up a Role for the login, and assign execute permissions on the stored procedures to that role. You may also need to assign select, insert, uipdate, and delete permissions for some of the tables, as well, but that is really easily done. You can generate the grant statements with something like:select 'grant execute on ' + name + ' to rolename'from sys.procedures Run the result of that query, and you never have to worry about a hacker dropping your customer table. |
 |
|
|
|
|
|
|
|
Problem with roles and rights (solved)