Logins - SQL Server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Logins - SQL Server 2005

Author

Topic

Jerid
Starting Member

10 Posts

Posted - 2006-09-18 : 16:29:57

I need to create a new login that only has access to 2 Views.

I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-18 : 16:40:51

Create the login, then add it to the correct database. After that:

CREATE VIEW SomeView
AS

...

GRANT SELECT ON SomeView TO YourUser
...

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-18 : 16:45:42

Make sure the tables and other objects do not have permissions granted to public. Make sure the user is not in any privledged role, like db_datareader. Make sure the login is not in any fixed server role.

CODO ERGO SUM

Jerid
Starting Member

10 Posts

Posted - 2006-09-18 : 19:28:39

First, Thanks for your help.

I still can't get this to work correctly.

I created Login zzz (w/ SQL Ser Auth)
Set the default database to database abc
Nothing is selected in Server Roles
I set User Mapping to database abc, user = zzz
Database roll membership is public. (It won't let me change it)
Added user to my two Views (Granted Select only)

Now when I connect to the server from Excel
Select new database query, add new data source (connection tests successful)

When I go to select the default table I can see the two views, but I can also see a whole list of other tables
all_columns, all_objects, etc.. It looks like it's everything in the System Views Folder.

What am I missing? I don't want anything in that list except the two views.

Thanks Again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-18 : 19:54:32

But can that user query those table that it can see? It should only be able to query the views even if it can "see" the other tables.

Tara Kizer

Jerid
Starting Member

10 Posts

Posted - 2006-09-18 : 19:59:46

Yes, I just queried the all_columns view and it let me.

Any thoughts?

OK, I just removed the Public User from that view and it disappeared from the list.. Do I need to do that for each item in the system views folder. There has to be upwards of 200 views. Anyway to change them all?

And then there is the views listed under Information_Schema, the public user isn't setup on those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 01:30:44

Set DENY permissions for all object for the new user. Then GRANT read permissions on the view.

Peter Larsson
Helsingborg, Sweden

mr_mist
Grunnio

1870 Posts

Posted - 2006-09-19 : 05:17:58

I reckon the easiest way would probably be to create a schema for the user that Denies access to everything except the two views. Dealing with multiple objects is what schemas are for, after all.

-------
Moo. :)

Jerid
Starting Member

10 Posts

Posted - 2006-09-19 : 06:44:33

Thanks, I will give those suggestions a try today.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-19 : 14:28:51

Why does it matter that a user can select from system views and tables? They can't get to any other user data except what is defined in the views.

Tara Kizer

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-19 : 15:01:21

>> "Now when I connect to the server from Excel
Select new database query, add new data source (connection tests successful)

When I go to select the default table I can see the two views, but I can also see a whole list of other tables
all_columns, all_objects, etc.. It looks like it's everything in the System Views Folder."

What will happen when the user is denied on ALL metadata views?
I doubt then that excel will be able to retreive the list of the 2 views + columns that the user IS allowed to see.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-19 : 15:06:27

They certainly won't be able to perform SELECT * FROM ViewName when denied all of those permissions.

Tara Kizer

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-19 : 15:26:39

I envision a combobox with
sys.objects
sys.columns
viewA
viewB

depending on what the excel metadata API uses

rockmoose

Jerid
Starting Member

10 Posts

Posted - 2006-09-19 : 20:03:28

I need to setup a user that people will use in conjuction with OutlookSoft, these will be financial users, not techy. I was trying to make it as simple as posiable for them. Sign in, see 2 views, pick one.

Thanks for everyones help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources