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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2007-02-13 : 05:53:32
|
| is there an easy way I can give a specific user complete insert,update, and delete permissions on all tables,view, and sp in a db without having to set individually for all? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-13 : 07:11:28
|
| db_DataRead and db_DataWriter permissions will do it for the tables.You will have to allocate SProc EXECUTE individually to each Sproc (unless there is a new Role that covers this in SQL 2005)Best bet is to set up a ROLE, grant that Role to each appropriate user, and then make sure that the Role has the permissions you want.Kristen |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-02-13 : 07:56:53
|
| i tried this but i still get select permission denied for all the tables when accessing from my asp script.any sql sytax to write to assign access to all the tables to this user or do I have to do it one by one.also can you confirm if I give access to a stored procedure -- do I need to give access to a table that sp writes to or is the sp enough? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-13 : 08:47:27
|
| "any sql sytax to write to assign access to all the tables to this user or do I have to do it one by one."db_DataRead and db_DataWriter will give access to all tables - unless they are explicitly Deny'dFor SProc EXECUTE you have to Grant Execute one-by-one - although you could script the task."do I need to give access to a table that sp writes to or is the sp enough?"The Sproc runs with the permissions of the user that created it. So the user just needs EXECUTE on that Sproc.Kristen |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-02-13 : 09:04:02
|
| well it doesn't work -- so I guess the only choice is to check each one... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-13 : 10:54:46
|
| Dunno any thing about SQL 2005 I'm afraid, and I expect there is a more "slinky" way of doing this.In SQL 2000:sp_helprotect @name = 'MyObject', -- NULL = All@username = 'MySecurityAccount'will tell you want access MySecurityAccount has to MyObjectIt might reveal a DENY or something else unexpected?Kristen |
|
|
|
|
|
|
grant full insert,update,delete permissions