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 |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-11-20 : 17:32:47
|
| We have a situation where users need to perform any action against a specified schema (create/drop/alter any object [table, view, proc, function, etc]). We can let them do this by giving them the db_owner role but this role gives more permissions than is acceptable in this situation. If I want to give a login all the permissions to do anything they want to a specified schema but not allow them to do anything to the database itself, how would I accomplish this? We use mixed security so users can authenticate via a domain group, domain account or sql account. Is it as simple as a GRANT ALTER ON <schema> TO <account> - assuming the correct database context is already established? |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-21 : 09:46:20
|
| Any takers? :) |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-24 : 06:41:20
|
| Do this by creating stored procedures to carry-out tasks, e.g sDeleteTable 'tableName', sInsertTable 'tableName', etc. Set the the criteria of what are acceptable table names inside procedure, e.g. If left(@tableName,4) ='xxx.' then ..... delete @tableName.Then give permissions to the procedures. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-24 : 09:27:43
|
| dark, you completely missed the question. I want them to be able to create/drop/alter their own objects in the database but nothing more. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-24 : 11:12:35
|
| The "e.g. If left(@tableName,4) ='xxx.'" is a fairly crude way of finding the table schema. You would need to modify this to match the schema names you use. If you have examples of the schema names you use we could give you full check.Alternatively (probably better):Create a new database role. Give it database owner rights, then add Deny permissions.E.g Deny SELECT etc. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-24 : 11:49:27
|
| dark, let's put it this way... I know the schema I want to give the permissions to, and I know the login accounts that require this access. |
|
|
|
|
|
|
|
|
Permission question