Granting select to tables via Management Studio

Question

Granting select to tables via Management Studio

Summer90

SSC-Dedicated

Points: 32891
More actions
January 24, 2007 at 7:53 am

#118632

For the life of me I cannot find in the Management Studio how to grant select, insert, delete, update to application tables for a user. I see how to grant create table and that type of thing. I see nothing in BOL as to how to use Management Studio, just SQL statements....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Rich Hansell SSCrazy Points: 2346 More actions · Answer 1

Open up SSMS and then use the object browser to drill down in to the Server\Database\Tables.. then right click on the table(s) in question and select properties from the context menu. A new box will appear, on the left hand side you will see Permissions. Click on that and then click on the "Add" button that will appear to the right (mid box) You can then select the DB users\logins to add to the table. Once you select them you will be able to assign select/delete etc etc.

Thanks

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 2

OK, thanks, I see it now. Man, what a cumbersome way to do this. I see no way to do several tables at a time either. I don't know about you but they have certainly taken a very easy to use GUI in Ent. Manager and made it time consuming in Mgt. Studio for lots of things... especially Maint. plans and now this...

Rich Hansell SSCrazy Points: 2346 More actions · Answer 3

If this is for a particular application, why not create an DB role in the DB... that set up will allow you to add several tables at a time or any number of DB objects (procs, tables, views etc)with the required permissions..

I agree it is quite a change.. and takes some getting used to. The maint plans setup is quite different but very very flexible.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

We have different db's/schemas that have specific ID's tied to them. So, most of them are unique in what access to db's and tbls they need. I/We attempt to only give access to specific ID's to the tables they need. However, sometimes a specific ID needs access to 10-20 tbls... if every table grant is a seperate action in the new MGT Studio it is cumbersome. However, manually creating the grants doesn't really help much as it is a one time grant. In 2000 it was much easier as it presented an ID and then you selected what grants you wanted all on one screen. Very simple, easy to use. This way the 10 to 20 grants have to be done one at a time.

The issue with maint. plans is that now to cleanup old backups or old maint plan text files you have to create specific tasks now seperate. In 2000 you simply checked a box on the db backup screen and supplied # of days. The other thing about Maint. plans I don't like is that in a specific maint plan that has db backup, Integrity check, update stats in 2005 now for each task I have to manually tell it all three times what dbs to perform this action for. In 2000 you selected that first and it applied to the entire maint plan.

I like the new reports, new tuning stuff, and love that there is a refresh avail. for each screen. However, everything else is more combersome and time consuming. Overall, I don't like it, Ent. manager was much easier to work with.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

I don't know if SP2 will make grants easier but Maintenance Plans have been added to SSMS that will have a similar look and functionality to EM

At least, that's what I have read so far.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 6

Ok, do you have a database ( ABC ) with tables a,b,c,d,e,f,g,h.

User1 needs access to a,b,c

user2 needs access to a,b,c,d,e,f

user3 needs access to d,e,f

etc.....

so create a database role. ( from object explorer iin MSSMS ) select: databases, database name, security ( right click ) add a database role

add role DBRABC and give it select, insert, update , delete to tables ABC

add role DBRDEF and give it select, insert, update , delete to tables DEF

you can assign your user security at thsame time or assign it later....so that user1 is a member of DBRABC, user3 is a member of DBRDEF and user2 is a member of both.

Hope that this helps

Eric