SQL 2005 - security and visiblity of objects

[TWillard]

I have a sql 2005 instance. There are going to be multiple end users accessing a database on the server via windows authentication. I would like to restrict the access onto which objects they have visibility to. If I create odbc connections, by default the users see all the tables/views in the sys and information_schemea schemas. I want to limit the visiblity to just the specific objects that that I grant privelages to. Help would be appreciated?

 

[mrdenny]

SQL 2005 does that automatically.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TWillard]

Ok - I am missing something. This is a new install with default settings of SQL 2005, so no crazy configuration have been built, yet.

--If I add in a windows-nt users then the user has visiblity to the sys and information_schemea schemas objects via Access ODBC Linked tables.

--If I try and deny all permissions for that user on the system object/view of INFORMATION_SCHEMA.check_constraints an error is thrown: "Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master. (Microsoft SQL Server, Error: 4629)"

--If I add the user to the database guest schema and deny access to everything, the user can still see the sys and information_schemea schemas objects via Access ODBC Linked tables.

--If I go to the database sys and information_schema's > permissions tab and add the user, but deny access to everything, the user can still see the sys and information_schemea schemas objects via Access ODBC Linked tables.

--If I map the login to only one user database and set the default schema to db_denydatareader, the user can still see the sys and information_schemea schemas objects via Access ODBC Linked tables.

--If there are no extended properties or securables, but the user is only a owner of the default schema db_denydatareader, the user can still see the sys and information_schemea schemas objects via Access ODBC Linked tables.

I am sure there is a way, I am just not finding it. Thanks.

 

[TWillard]

I changed the select permissions to deny for the master database role of public on all the views belonging to the schemas of sys and information_schema. This only generated errors on connecting via Access ODBC Linked Tables or SQL Server Managment Studio.

 

[mrdenny]

You can't change the permissons on the objects in the sys or INFORMATION_SCHEMA schemas.

When the user queries the tables they should only see the objects that they have access to. If you have set a deny on an object it may show up in the system view.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TWillard]

I understand now that you can not change the permissions on the sys or information_schema schemas. As these schemas are an integral part of the database system. I also understand that the data is "filtered" in that they can only see the objects they have permissions to.

My one caveat, is that the sys or information_schema's have select permissions via public. If I create an odbc connection or establish any connection to the database, then any user can query these objects. They are also in the list of available tables to select from via an odbc connection. Ideally, what I am looking for is to block the visiblity to these objects to a specific type of user. I will just call the user an information reporter. I do not want any and all connections to have the ability to query these schemas or have visibility to them.

I think that it would be nice and helpful for the beginer user to only have visiblity to the objects that they really need. The other stuff is just noise and a distraction.

However, it sounds like it can not be done via sql server 2005. I am sure that it could be done through an application or connection abstraction, but it is not worth it at this time.

Thanks mrdenny

 

[mrdenny]

Yep, you'd have to do this via a GUI that you provide to the users.

When you open the database in the SQL 2005 GUI these objects are all listed under Views > System Views, so odds are most users won't find them.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com