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 |
|
staplebottom
Starting Member
29 Posts |
 Posted - 2007-10-24 : 11:34:20
|
| Hi,I am looking for some advice on securing a sql 2005 database.I have a database to which I want to grant 3 different users access. The first users, who we will call 'public' should have access to a set of stored procedures, and no access to tables without using the stored procedures they have access to.The second users have again got access to a certain set of stored procedures, along with the stored procedures for 'public'. These should be called 'moderator'.Finally the third set of users 'admin' should have access to all stored procedures, but again no access to the database unless its through a stored procedure.Questions :Using SQL 2005, how do I assign access to particular stored procedures to a user?How do I ensure that a 'user' cannot access stored procedures I don't want them to access?Some of the stored procedure use dynamic sql within them, will this affect my setup. If I deny direct access to the tables to all three users, but allow them access the stored procedures, can the stored procedures access the tables?If schemas are the answer, how on earth do I set them up, Ive never seen something so badly documented online in my experience! In my development environment im using sql express, so will probably working through the query analyzer.Thanks in advance.Conor |
|
|
staplebottom
Starting Member
29 Posts |
Posted - 2007-10-24 : 12:54:56
|
| Hi,One more thing, When I have granted or revoked rights on a table/stored procedure to a user in sql express, where can I see this graphically, or in the simplest way. Is there a matrix view on permissions?Thanks,Conor |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-24 : 22:21:12
|
| You can see that in ssms express. |
|
|
|
|
|
|
|
|
Questions on Users/Schemas