USER PERMISSIONS/SECUIRTY ISSUE

Question

USER PERMISSIONS/SECUIRTY ISSUE

YSLGuru

SSC-Insane

Points: 21661
More actions
February 14, 2014 at 1:06 pm

#304424

Any idea why the domain user MyDomainUser1 (that exists on the domain MYDOMAIN ) can still update data in the table dbo.INVOICE in the database MYDB after the below has been executed?
NOTE: The user does not belong to any Server role. The user is not part of any DB Role. What am I missing? What T-SQl should I use to deny this domain user the ability to update data in the table dbo.INVOICE?
USE MYDB
GO
REVOKE UPDATE ON dbo.INVOICE to [MYDOMAIN\MyDOmainUser1]
GO
BTW - I also tried the below and the the user is still able to update data in the INVOICE table. I even had the user disconnect from SQL Server and connect back in again.
DENY UPDATE ON INVOICE to [MYDOMAIN\MyDOmainUser1]
GO
Kindest Regards,
Just say No to Facebook!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sounds like the user inherits a sysadmin permission due toi the windows groups the login belongs to.

this will enumerate all users in windows groups, and then join them to see what server roles they inherit (typically sysadmin)

is your user in this list?

IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL

DROP TABLE [dbo].[#TMP]

CREATE TABLE [dbo].[#TMP] (

[ACCOUNT NAME] NVARCHAR(256) NULL ,

[TYPE] VARCHAR(8) NULL ,

[PRIVILEGE] VARCHAR(8) NULL ,

[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

[PERMISSION PATH] NVARCHAR(256) NULL )

DECLARE @cmd VARCHAR(MAX);

SELECT @cmd = s.Colzs

FROM(SELECT

Colzs = STUFF((SELECT ';' + 'INSERT INTO #TMP EXEC master..xp_logininfo @acctname = ''' + name +''',@option = ''members'' '

FROM master.sys.server_principals

WHERE type_desc = 'WINDOWS_GROUP'

AND name NOT LIKE '%$%' --avoid errors like Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQL$MSSQLSERVER1', error code 0x8ac.

FOR XML PATH('')

),1,1,'')

) s

SET @cmd = REPLACE(@cmd,';',';' + CHAR(13) + CHAR(10))

print @cmd

exec(@cmd)

SELECT T1.*,

T2.type_desc,

T4.name

FROM #tmp T1

inner join sys.server_principals T2

ON T1.[Permission Path] = T2.name

inner join sys.server_role_members T3

on T2.principal_id = member_principal_id

inner join sys.server_principals T4

on T3.role_principal_id = T4.principal_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

Lowell,

That user is in the results. It was my understanding that when you had conflicting permissions the most restrictive permission would win out meaning that if the group this domain user belonged to had full access to a DB and its objects but the individual user had been restricted from updating a table in that DB then the most restricve would win out and the user woudl not be able to udpate that table but would be able to update in any other table in that DB. Is this not true or is this just a bug in SQL Server?

Thanks

Kindest Regards,

Just say No to Facebook!

Lowell SSC Guru Points: 323518 More actions · Answer 3

sysadmin overrules all; any restrictions that you add on a sysadmin are ignored/cancelled out by that ring of power.

normal users, then yes, the most restrictive wins, but you can't add restrictions against a sysadmin.