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.

grant create_table permission?

Greetings,
So I am just starting out with DBA duties. I have created
a few new accounts. I checked the list of roles, but I
cannot find anywhere how you grant create_table
permissions to a login ID. I looked at all the
permissions/properties, but I just don't know how this is
done. May I request if someone could explain how this is
done?
Thanks,
RonYou need to do this through T-SQL:
GRANT CREATE TABLE TO user
"Ron" wrote:

> Greetings,
> So I am just starting out with DBA duties. I have created
> a few new accounts. I checked the list of roles, but I
> cannot find anywhere how you grant create_table
> permissions to a login ID. I looked at all the
> permissions/properties, but I just don't know how this is
> done. May I request if someone could explain how this is
> done?
> Thanks,
> Ron
>

GK-Permissions on all database objects

Hi,
I'm looking for a way to view / report all permissions on all database objec
ts ( logins / roles / users and all object permissions ( select, delete, upd
ate execute etc. ) ).
If you can help please do...
Gkramer
The NetherlandsHi,
Please refer books online for the system procedure,
sp_helprotect
Thanks
Hri
MCDBA
"Gkramer" <anonymous@.discussions.microsoft.com> wrote in message
news:8626D21C-7192-4313-8CC1-0EDE6CCC2B43@.microsoft.com...
> Hi,
> I'm looking for a way to view / report all permissions on all database
objects ( logins / roles / users and all object permissions ( select,
delete, update execute etc. ) ).
> If you can help please do...
> Gkramer
> The Netherlands

Giving roles access to perspectives

In BOL, the page on creating perspectives says

"Dimension and cell security for a perspective are derived from the source cube. However, roles can be given or denied access to a perspective. A role can be denied access to a cube and given access to a perspective of the cube."

However, I can't find out how to grant access to a perspective. There doesn't seem to be anything about it on BOL, and the perspectives don't appear anywhere in the role editor.

Can anybody tell me how to do this? Sorry if I'm being stupid and missing something obvious!

Unfortunately in this version of Analysis Services it is not possible to assign security permission to perspective.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Is this a feature that is likely to be implemented in the future? Is there any other way to grant access to specific attributes of a dimension?|||

Of cource you can't expect any public commitment to any particular feature :). It has been requested by many customers.

You can create a role object and use BI Dev Studio to grant/deny permissions to dimension members of specific attribute.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.