January 27, 2011 at 12:40 am
Dear All,
i have more than 20 Databases in MY SQL 2005 .i want to give permissoins to Users according the Datades, i mean to say if a user can use a database than he never to see or access the other databases. Schema is created for that type of secuirty and i have no idea to create schema .
please help me ..........
Vimal Kumar
9999797952
January 31, 2011 at 9:18 am
I'm sure but I think I understand what you mean. To clarify, are you asking if there is way to restrict users so that they can only see those databases that they have permissions to access? Eg, if you have databases a, b and c, if I only have permissions on database b, when I connect to SQL I will only see that one database listed in SSMS, and not a and c?
If so then the only way I know of is to either use :
revoke view any database from public
which will stop all databases from being listed unless your user is the database owner.
or you could use either :
deny view any database to MyUser
or
deny view MyDatabase to MyUser
which would prevent a single user from viewing all databases not owned by them, or a single specific database.
Unfortunately using either of these methods does mean that unless the user is the database owner, they will not see it from within SSMS even if they have permissions to it. They can still access it like before, but they cannot view it via the UI.
In SQL 2000 there was a stored procedure which could be amended to properly get this working (http://support.microsoft.com/kb/889696/en-us), but SQL 2005 uses a different method to enumerate the list of databases so that doesn't work, and as far as I can see Microsoft don't see it as an issue so haven't resolved it in 2005 / 2008.
There are a couple of discussions on this, but neither come up with much of a resolve :
http://sql-server-performance.com/Community/forums/t/26005.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply