Re: SMS View Tables access through ODBC - Help!

"Maria" <Maria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D0A903E-251B-4101-80D9-7499BC311E92@xxxxxxxxxxxxxxxx

> Thank you very much for your quick response. Unfortunately, the access to
> view tables is required for a feed to a third party asset management tool.
> smsschm_users role has most of the v tables selected and the articles on
the
> Microsoft web site recommends creating a userid or group and give it the
> smsschm_users role. However, when I added a user id to SMS db and
> SMSschm_users role, I noticed this user is automatically given the public
> role to the db. Although I cannot remove the public role, I can uncheck
the
> access box.
> My question is, Is public role necessary to access view tables?

Yes, by default it is. However, you could create a new group/user account
view permission, then revoke public access from that new group/user.

> Can I just
> give the smsschm.users role and uncheck all the access in the public role?

Hmm, you may cause some undesirable side effect with that step, that role is
used for the SMS web reports. So, if you try that -- test your web reports,
with a standard user account to make sure that nothing bad happened. A
database role is basically a mechanism that allows an application to connect
without the need for specific accounts... I double checked the smsschm_users
role, by default, it only grants select permissions to the sms database
views.

I think you are better off using a new group/account for the access, only
enable the select permissions to the objects you want them to view.

Another option that you might consider, use DTS (Data Transformation
Service) to export the data (tables/views) to a location that the third
party can read/import.

Steve


.

Relevant Pages

  • Re: change user1 access changes user2 access
    ... Using the public role is your problem. ... All users, whether it be User1, ... permission you are granting All users the permissions. ... > name, a password, I select a database to login to, then go ...
    (microsoft.public.sqlserver.security)
  • Re: change user1 access changes user2 access
    ... > Using the public role is your problem. ... >permission you are granting All users the permissions. ... I click on New Login. ... >> name, a password, I select a database to login to, then ...
    (microsoft.public.sqlserver.security)
  • SQL user permissions problem
    ... I have run into an unusual error for one specific user who's ... permissions have been granted via the public role. ... and permissions to all relevant stored procedures have been set. ...
    (microsoft.public.sqlserver)
  • Re: Public Role in SQL Server 2000 SP3
    ... The default permissions of the public role allow SELECT from sysobjects and ... other meta-data objects. ... A security best practice is to create your own roles and grant object ... permissions to those roles as needed. ...
    (microsoft.public.sqlserver.server)
  • Re: Public Role in SQL Server 2000 SP3
    ... >The default permissions of the public role allow SELECT ... control security by user ... >> If I add a user to a database with Public Database ...
    (microsoft.public.sqlserver.server)