October 1, 2008 at 6:16 pm
Hi all,
First I'll say that we're a midsized company, and in a perfect world we'd have our development/production model set to 'best practices', but this is the real world, cows can't fly, and we don't follow all best practices.
With that said, I often have the need to quickly give a read-only login a few seconds of higher privileges so I can add/update some data.
We have a sql-2005 server with many MS Access apps as front-ends for the dbs. So I'll be in an Access app and there is some data I need to add to, say, a lookup table. The sql-server tables are odbc-linked via a user without update/insert permissions on these tables. In the past (back in the Sql-2000 days), within EM I could right-click on the table, menu-in to the permissions tab, check the appropriate permission, then click Apply--and most importantly--leave the dialog box open so I don't forget to toggle it back. Then I'd go to Access, do my inserts, and toggle off the permissions. It was ultra-easy and painless.
Now with sql-2005 and SMS, it takes numerous clicks just to get to the permissions, and then once I get there and click the permissions, there is no "Apply". I click OK and the whole thing closes! So to toggle back off the permissions I have to go through that laborious path again. Plus, now my 'reminder' dialog box is gone--and I get so many interruptions that it is very important to have that dialog box stay open. And, if it's a case where I've got to temporarily link a table which the DSN login has no select rights, then I have to click yet another button to Add the user, several more to *find* the user, then add it, then do the permissions, etc, etc.
Bottom line is that this is *so* backwards as far as user-friendlyness. I know I could do a script but it was so much easier with the old EM dialog box--there is a big reason GUI's became so popular--I don't care what people say--I like pointing and clicking better than typing. This new GUI is as cumbersome as anything I've seen.
Am I missing something here? Is there an easier way to do a quick grant/revoke with the gui?
On the same note, does anyone else find the SMS interface more difficult and clunky? I've worked with it for around a year now so it's not a case of not being used to it--I still find that in the old EM it's much easier to get stuff like that done.
Thanks for any comments, suggestions,
--Jim
October 1, 2008 at 7:16 pm
SSMS is definitely more clunky than EM, but honestly the permissions dialog in either one isn't made for what you're doing.
First when you need to repeat things, you should use a script. And you should save it off and pop it open.
Second, you should create a role that has the permissions you need. You can easily add someone to the role and then take it away. Right click the user, select User Mappings, and then pick the db and role.
Honestly the quickest way to do this is set up two SQLCMD batch files. Call SQLCMD with a query of sp_addrolemember and the user. Set up another one with sp_removerolemember. You can set these up as short cuts and then double click them on your desktop.
Scripting doesn't mean typing, it means doing things programatically.
http://msdn.microsoft.com/en-us/library/ms187750(SQL.90).aspx
October 2, 2008 at 2:45 pm
Steve,
Thanks, that does make more sense. I'm thinking I'll just have a script to temporarily add the user to the dbDataWriter role, then remove it when my changes are done. It's usually only a few seconds. One of these days I'll have to create an admin front-end for doing this type of data manipulation, but as I say--we're not a big company and it's just me doing these changes.
Thanks,
--Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply