November 11, 2013 at 11:53 am
I have a three db's on one SQL Server 2012 instance and one on another server for which I need to manage access for a selection of users. All users have Windows log in's in our AD structure. There are hundreds of tables and sp's. I need to grant them all SELECT on the tables and EXECUTE on the sp's. One of the db's does a lot of queries into the other two db's and executes some of their sp's. Ideally, I'd be able to establish different sets of permissions that I could grant or revoke as I need to.
Doing it all be hand based on server login's and assigning users to each db with specific permissions has gotten totally out of hand. I've never really found a good way to do this, but I figure there must be one: can AD groups help? Database Roles? Mapping of some sort? Other ideas?
November 13, 2013 at 7:09 am
If you right-click a database in Management Studio and go to the Properties of the database, there is a Permissions menu choice.
You can Grant Permissions for a login to Delete or Insert or Reference or Select or Update or Execute for the whole database.
The Execute will be good for executing SPs and SELECT for readonly access to tables.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply