Sql-server – Only allow ability to read the views for a particular SQL Server user over ODBC

odbcpermissionssql serversql-server-2008

Newbie question: I have a designer who reads information from a SQL Server [2008] database. I've setup a few views for him to pull his data from and granted his user a member of the db_dataread role. His program uses an ODBC connection to grab the data.

However, this allows him read access to all of the tables and system views and tables, requiring unnecessary searching and confusion for the user.

Is there a way to restrict what he can see to only the handful of views I've created for him?

[EDIT: I've created a new test user, and a new test ODBC connection, giving no permissions at all with no change in results. From what I've read, it is because all users are part of the PUBLIC server role. The public role appears to give SELECT privilege to all system objects. Anyone know otherwise?]

Best Answer

Remove the user from the db_datareader role and just give it SELECT rights against the views you created for it. You don't mention which version of SQL Server, but the start point is invariably opening the user's properties screen and looking for something that says Permissions.

Related Solutions

Security – SQL Server 2005 Accidentally removed a user from public role, can’t add user back into role

To help with additional details, here is how it should all look so you can say exactly which portion isn't correct. You have a login for the server, and a user for the database for the account you're having trouble with. On the server and database levels, you'll see a public role (server role and database role). The properties of the public server role won't show users, but the properties of the login will show the public database role for all of the databases (it will be checked, and you can't uncheck it). The properties of the public database role will not show any members either, and the properties of the database user will also not show the public role. Because of all of this, I don't believe you removed the user from the public role.

If you want to try to delete and recreate the user, you can first try to go into the database and just delete the database user. This will leave the login, which you can go into the properties of, and, under User Mapping, there should be no database checked. You should be able to check the database now (because the database user has been deleted), and choose whatever roles you need. If this doesn't work, you can also delete the login along with the database user to try and clear everything out. For one last check on the user, you can run this SQL:

use database_name
exec sp_change_users_login 'Report'

This will show you any orphaned users, which could mean there is still an issue with your user. This can usually be fixed with:

use database_name
exec sp_change_users_login 'Auto_Fix', 'username'

To try and wrap this up before it gets any longer, there could be something else altogether causing your connection issue for this user. If none of this works, can you post up the error message you get when logging in?

Sql-server – Windows authentication in SQL Server allows user to see ANY database

Sounds like Joe is a member of a domain group that has more rights on the SQL Server than you want, or you've been messing with the rights to the public roles in the other databases on the server. In either case you'll need to find out where the extra rights are coming from in order to fix them.

You can use the stored procedure xp_logininfo to see what domain groups Joe is able to access the database through.

exec xp_logininfo 'YourDomain\JoesUserName'

If that doesn't point you in the right direction you'll need to look into the rights granted to the public role in the other databases.

Best Answer

Related Solutions

Security – SQL Server 2005 Accidentally removed a user from public role, can’t add user back into role

Sql-server – Windows authentication in SQL Server allows user to see ANY database

Related Topic