October 10, 2007 at 11:46 am
i have one user which i gave only data_reader permission only. i am suprise that he can update certain tables (sql 2005 is on SP-2.)
is it possible whether i can get information about this user for all tables within that database from privilege\permission standpoint. so that can help me to understand about his write permission on some or all tables. any help is appreciated. thanking in advance.
October 10, 2007 at 12:17 pm
see http://www.sqlservercentral.com/articles/Administering/dumpsqlpermissions/1314/
or the following example lists all permissions that user BILL has in the current database.
EXEC sp_helprotect NULL, 'BILL'
Francis
October 10, 2007 at 12:33 pm
You'll need to query sys.database_permissions to get the full list of permissions. The system stored procedure sp_helprotect will not return information on securables that weren't present in SQL Server 2000. Therefore, anything at a schema level, for instance, won't be shown by said stored procedure.
Louis Davidson has a good blog post on how to use the DMVs to get all the permissions in SQL Server 2005:
Using the catalog views to see table permissions
K. Brian Kelley
@kbriankelley
October 10, 2007 at 1:19 pm
aftger going through all systems views & function i am seeing that this user as action = Connect & Select. this one is really confusion to me, when i used Function PERMISSIONS for that user for e.g.
(you need to connect SSMS using that user credentials & run below within that Database where user as access)
IF PERMISSIONS(OBJECT_ID('xyz.dbo.table','U'))&2=2
PRINT 'The current user can update data into table.'
ELSE
PRINT 'ERROR: The current user cannot update data into table.';
I also run following (sys.sp_table_privileges @table_name = 'table') but that didn't helped.
Also i ran (select * from sys.database_principals ) but that information is accurate for that user. so not sure where to look for.
October 10, 2007 at 1:27 pm
Louis' blog has a SQL script which will give you all the explicit permissions. From there you're going to need to check role membership. Also, if you're dealing with a Windows login, you're going to have to take into account the groups the user is a member of and determine if any of those groups are granted access to the SQL Server and permissions into the database.
K. Brian Kelley
@kbriankelley
October 10, 2007 at 2:21 pm
This is what i get wheni ran Louis query in that database.
I see all public & no user name under database_principal.
e.g
permission_name object_type Object_name Database_principal grant_state
UPDATE USER_TABLEViewDef public GRANT
DELETE USER_TABLEViews public GRANT
October 10, 2007 at 3:02 pm
If the PUBLIC role has been granted, then all users will be able to update. To fix this, you would need to Revoke Update to Public. But BE CAREFUL - any users who rely on that to access the data will now be shut out.
October 10, 2007 at 10:04 pm
public role is something that i cannot REVOKE for UPDATE,INSERT,DELETE etc................ I am not sure that this PUBLIC role as to do with this issue.
There as to be better way to prevent this user doing any update, insert or delete.
I did couple of TESTs on SQL Server 2000 it is working fine but there is some issue on SQL Server 2005. infact my both the server 2000 & 2005 are on latest SP & patches.
Once again thanks for your all inputs, replies & efforts.
October 11, 2007 at 6:39 am
sqldba (10/10/2007)
public role is something that i cannot REVOKE for UPDATE,INSERT,DELETE etc................ I am not sure that this PUBLIC role as to do with this issue.
Everyone is a member of the public role. This cannot be changed. That means whatever permissions the public role has, everyone has. If you can't remove permissions from public, than you can use explicit DENY for that particular user. However, the best practice is to avoid the DENY route and fix the public permissions.
K. Brian Kelley
@kbriankelley
October 11, 2007 at 5:09 pm
Eventually i found that the root cause of this issue is Public Server Role. by default this ROLE should have 'SELECT' but i found that this ROLE as Insert, Update, Delete...........etc. I agree Deny is not good idea but at the same time i cannoy do that for all 200+Tables. i have to see if there is any storeprocedure that can be executed within MASTER DB to FIX this.
Any feedback is appreciated.
Once again thanks for your all time & efforts.
October 17, 2007 at 1:32 pm
sqldba (10/11/2007)
Eventually i found that the root cause of this issue is Public Server Role.
You mean public database role. There is a difference between the two.
The public server role does not give permissions within the user databases. The public database role does for that particular user database. If you look within any user database, you'll see the public role. That is at the database level. Any user within the database is a member of the public database role (just as all logins are a member of the public server role).
Where the distinction comes in is if you have a login that does not have access to a particular database. In this case it's still a member of the public server role and has all the permissions assigned to it. It is not a member of that particular database's public database role. It will therefore not have any permissions within the database.
K. Brian Kelley
@kbriankelley
October 17, 2007 at 6:04 pm
i agree to that, thanks for that your input.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply