July 28, 2008 at 8:25 am
Hi together,
i am searching for a way to prevent users see the sql2005 system schemas like information_schema and sys when retrieving metadata from a database in e.g. Crystal Reports using ODBC or OLE DB.
How it is 🙁
User sees the userdatabase, sys and information_schema
How it should be 🙂
User sees only userdatabase
user has server role public
user has database role db_datareader
i set up a ODBC DSN with native client
SQL2005, SP2
Thx in advance, Stephan
July 28, 2008 at 8:38 am
From BOL regarding the db_datareader database role:
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
😎
July 29, 2008 at 12:36 am
Thx for the answer.
The user has the db_datareader role, but this is to much as he is also able to see sys and INFORMATION_SCHEMA tables and views. What is the common way to remove access rights on this "schemas"?
BR, Stephan
January 2, 2009 at 8:52 am
I'm getting the same problem. What is the solution to this?
January 3, 2009 at 7:48 am
Roy Matthews (1/2/2009)
I'm getting the same problem. What is the solution to this?
All data access via stored procedures, grant only execute permissions on the procs and no rights to the base tables or views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2009 at 7:31 am
With ms access and crystal reports users do not call stored procedures they see the entire tables and do their own coding i.e select * from table.
I tried to make all bi reports use sp but they won't so i guess im asking the same question how to prevent them seeing all system tables too.
In ms access you do a link to the database and it gives all tables, i did do all views for some companies and they still wanted to see the table definition which you can't do in views either same with sp.
Any other options be appreciated.
January 4, 2009 at 5:08 pm
To prevent users from being able to read schema information you have to deny view definition right.
There are several ways you could organize it, depending on your needs. One of these is to create a database role and add users to it instead of db_datareader role, grant it select right and deny view definition. This would be equivalent of slightly trimmed db_datareader. You can also deny view definition from particular users but this may become a bit cumbersome if there are many of them.
Then you can create a separate role and add to it only users that should have not access to schema information, like in the example:
create user testu without login
go
create role [no_schema_view_role]
go
--deny schema access to members of this role
deny view definition to no_schema_view_role
go
--if user is not to have access to schema, add it here
sp_addrolemember 'no_schema_view_role', 'testu'
go
sp_addrolemember 'db_datareader', 'testu'
go
execute as user='testu'
go
--no data should be returned
select top 1 * from information_schema.tables
select top 1 * from sys.tables
go
revert
go
drop user testu
go
drop role [no_schema_view_role]
You can have a look of the Troubleshooting Metadata Visibility topic in BOL.
Regards
Piotr
...and your only reply is slàinte mhath
March 10, 2009 at 6:34 pm
I have a weird situation. On a network with 4 SQL servers, I was asked to prevent 3rd party from connecting to 3 of the SQL servers, they may only connect/logon to 1 SQL 2000 server. To make the issue even more hilarious, the 3rd party has the sa (SQL admin account) password for one of the SQL 2000 servers they are not supposed to connect/logon to, changing the sa password is not possible since the application and DTS's all use the sa password on that server.
Searching via google, I found 2 solutions to the problem,
1. use logon triggers
2. use IPSEC
Can anyone provide a statisfactory solution.
Regards
Kevin
March 11, 2009 at 1:12 am
clive (3/10/2009)
Can anyone provide a statisfactory solution.
On SQL 2000?
If I may be blunt, the first thing I would look at is changing the DTS so that they use a username/password specifically for DTS, then I'd look at the apps, see if they can be modified to not use sa. Anything else is just patching holes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2009 at 12:50 pm
Thanks, but unfortunately we have over 800 DTS, they include data source/connections and most of them ActiveX VBScripts which use sa account and password (some DTS have up to 20 or more ActiveX VBScripts), not to mention that clients connect remotely to server/s to access application - the application has the sa password embedded inside it (server side and client side), we can always change the server side, but the client side means travelling to remote sites and modifying client side application, many clients are in neighbouring countries.
So I need a temporary solution until the sa password has been changed (could be several months).
Regards
Kevin
March 11, 2009 at 1:36 pm
SQL 2000 or SQL 2005?
This is the 2005 forum, but you mentioned SQL 2000. Which is it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply