Granting Permissions to Roles in SQL Server 2005

I have several years experience working with SQL 2000 and we are now
also working with SQL Server 2005. I have found Management Studio a
bit tricky to work with, MS seems to have gone backwards in a few
areas.
In Enterprise Manager, permission granting was not the best but it was
simple and worked well. In Management Studio granting permissions to
a new role now seems to be very slow and tedious.
First of all it is about six mouse clicks to browse views only, then
select views one at a time (no ctrl or shift + click allowed), click
OK then you need to click through each view and then click Select (or
whatever rights). Clicking through each view for the first time takes
a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
I personally try and use the mouse as little as possible, keyboard
shortcuts are much faster. The other problem is that frequently the
role does not save correctly, ie. grant rights then click OK, load the
role again and my changes are gone!
We can't use Schemas because our app references dbo.ObjectName
everywhere and it would be impractical to change this to
Schema.ObjectName, or just ObjectName in all of our code. We did this
because referencing objects as dbo.ObjectName was optimised faster
than just ObjectName.
Has anyone else experienced the same problems?Others have mentioned some problems with using the GUI for
managing and viewing security. There is a lot more
complexity to SQL Server 2005 security than there was in
2000. Some of the "views" people were used to in 2000 just
aren't worth as much or as practical in 2005.
But...writing a T-SQL statement worked in 2000, works just
the same in 2005 - you just have more options as to what you
can grant and in what scope in 2005. If you use T-SQL and
scripts for your changes, you have a record which documents
to some degree what you executed, can keep the changes in
source control. In the same regard, you can write a lot
better queries to obtain security information in 2005
compared to 2000.
-Sue
On 12 Feb 2007 16:23:05 -0800, stevo1980@.gmail.com wrote:

>I have several years experience working with SQL 2000 and we are now
>also working with SQL Server 2005. I have found Management Studio a
>bit tricky to work with, MS seems to have gone backwards in a few
>areas.
>In Enterprise Manager, permission granting was not the best but it was
>simple and worked well. In Management Studio granting permissions to
>a new role now seems to be very slow and tedious.
>First of all it is about six mouse clicks to browse views only, then
>select views one at a time (no ctrl or shift + click allowed), click
>OK then you need to click through each view and then click Select (or
>whatever rights). Clicking through each view for the first time takes
>a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
>I personally try and use the mouse as little as possible, keyboard
>shortcuts are much faster. The other problem is that frequently the
>role does not save correctly, ie. grant rights then click OK, load the
>role again and my changes are gone!
>We can't use Schemas because our app references dbo.ObjectName
>everywhere and it would be impractical to change this to
>Schema.ObjectName, or just ObjectName in all of our code. We did this
>because referencing objects as dbo.ObjectName was optimised faster
>than just ObjectName.
>Has anyone else experienced the same problems?|||(stevo1980@.gmail.com) writes:
> First of all it is about six mouse clicks to browse views only, then
> select views one at a time (no ctrl or shift + click allowed), click
> OK then you need to click through each view and then click Select (or
> whatever rights). Clicking through each view for the first time takes
> a few seconds on my laptop (1 user, core 2 duo, 2GB ram!).
> I personally try and use the mouse as little as possible, keyboard
> shortcuts are much faster. The other problem is that frequently the
> role does not save correctly, ie. grant rights then click OK, load the
> role again and my changes are gone!
The purpose of the more complex dialogue is that will encourage you
to type your statements! As Sue said, this pays off in the long run.
Joking aside, note that there is a Script button in every window, so
you can use the GUI for the first guy to get a template, if you
are not up to pace with the syntax.

> We can't use Schemas because our app references dbo.ObjectName
> everywhere and it would be impractical to change this to
> Schema.ObjectName, or just ObjectName in all of our code. We did this
> because referencing objects as dbo.ObjectName was optimised faster
> than just ObjectName.
No one forces you to use schemas. With "dbo." or not, adding
schemas to an existing application, is a major undertaking. For
the system I work with, schemas would fit in perfectly with what
we call subsystems, but given the size of our app, it's not going
to happen any time soon.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 14, 9:56 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> (stevo1...@.gmail.com) writes:
>
> The purpose of the more complex dialogue is that will encourage you
> to type your statements! As Sue said, this pays off in the long run.
> Joking aside, note that there is a Script button in every window, so
> you can use the GUI for the first guy to get a template, if you
> are not up to pace with the syntax.
>
> No one forces you to use schemas. With "dbo." or not, adding
> schemas to an existing application, is a major undertaking. For
> the system I work with, schemas would fit in perfectly with what
> we call subsystems, but given the size of our app, it's not going
> to happen any time soon.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Scripting admin tasks seems the way to go, having an audit trail can
come in handy. There is definitely some nice security functionalty
available with SQL 2005, just the GUI seems to be a bit flawed.
Thanks for your responses.