July 16, 2008 at 1:00 am
One Web Application is using SQL server via user 'abc'. I want to restrict this user.
My requirement is :
abc should not select, insert, update, delete on any table using any sql query.
abc should run specific Stored Procedure and insert or delete or update or select.
I have procedures for all this operations.
What steps I have to follow to create a login, role, user?
what rights i have to give to database, table?
please guide in detail.
July 16, 2008 at 1:24 am
You have to create the database roles which sets the permission for users on objects.
Create one database role set the permissions using DENY / GRANT.
Mapp the user and database role created using sp_addrolemember.
July 16, 2008 at 1:54 am
Since you have just one user abc, you can
1) create a new login named abc under security
2) map the new login to the database you want to give the access.
3) go to the database and select the user account.
4) select the permissions of the objects as you require, like select, insert or update.
😉
July 16, 2008 at 3:23 am
Can I Restrict all permissions to a table and give access to that table via a stored procedure? How?
July 16, 2008 at 4:00 am
you have the SP sp_grantlogin..
July 16, 2008 at 12:03 pm
Once you've created a user in the database mapped to the login, GRANT EXECUTE on each stored procedure that you want the user to run.
In SSMS, you can open the user properties, click on securables, click the "Add..." button, select "All objects of the types..." and select "Stored Procedures". Highlight each stored procedure you want the user to be able to execute and check the "Grant" checkbox for the permission "Execute".
Greg
July 16, 2008 at 11:13 pm
SSCrazy,
can i deny all permissions on all tables after granting execute permission to required procedures.
what user role i have to create or assign/remove for this user?
thanks
July 17, 2008 at 9:54 am
Sure. You can use DENY ALL on each table although you'll get the same result if you don't explicitly grant table permissions to a user. For example, you can grant EXECUTE on a stored procedure that selects from a table and not grant any object permissions on the table. The user would be able to select from the table by executing the stored procedure but not by issuing an explicit SELECT statement.
BTW, you'll also want to avoid making users members of the fixed database roles like db_datareader and db_datawriter which allow a user to read and write any table in the database.
Greg
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply