db_datareader allow alter permissions

Question

db_datareader allow alter permissions

Viewing 15 posts - 16 through 30 (of 32 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you remove the denys and check if the user still has those permissions?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

try this: maybe the login is a sysadmin:

you'll need to change the name of the login to your specific login/sql user in question:

EXECUTE AS LOGIN='ThatSpecificSQLUser';

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid;

REVERT;

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!

ajsnyman Right there with Babe Points: 732 More actions · Answer 3

GilaMonster (1/5/2012)
Can you remove the denys and check if the user still has those permissions?

Tried it, then right back to updates and altering ;(

ajsnyman Right there with Babe Points: 732 More actions · Answer 4

Lowell (1/5/2012)
try this: maybe the login is a sysadmin:
you'll need to change the name of the login to your specific login/sql user in question:

I get

'Msg 297, Level 16, State 1, Line 6

The user does not have permission to perform this action.'

when i run it.

Thank you

Perry Whittle SSC Guru Points: 234024 More actions · Answer 5

ajsnyman (1/5/2012)
Tried it, then right back to updates and altering ;(

remove the denials and the re run the script below and post the exact results

select dp.name, dbp.state_desc, dbp.permission_name, dbp.class_desc

from sys.database_principals dp inner join sys.database_permissions dbp

on dp.principal_id = dbp.grantee_principal_id

where dp.name = 'yoursqlacct'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ajsnyman Right there with Babe Points: 732 More actions · Answer 6

Ok, here is the results:

namestate_descpermission_nameclass_desc

horizonReaderGRANTCONNECTDATABASE

horizonReaderDENYALTERSCHEMA

I am unable to remove the last entry due to an exception occurring.

But this deny also does not do anything, I am still able to alter.

Thanks you for your help.

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 7

db_datareader is granted for only Select

check out this : http://msdn.microsoft.com/en-us/library/ms189612(v=SQL.105).aspx

Perry Whittle SSC Guru Points: 234024 More actions · Answer 8

also, what does this return

select dp.state_desc, dp.permission_name, dp.class_desc, sch.name

from sys.database_permissions dp

inner join sys.schemas sch on dp.grantor_principal_id = sch.principal_id

where dp.class = 3

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ajsnyman Right there with Babe Points: 732 More actions · Answer 9

Thank you Ankit Shah, it seems that I'm having a funny variant of this role, for mine allow alter and update.

ajsnyman Right there with Babe Points: 732 More actions · Answer 10

Perry, it returns the following:

state_descpermission_nameclass_descname

GRANTCONTROLSCHEMAdbo

GRANTCONTROLSCHEMAthys

GRANTCONTROLSCHEMApvn

GRANTCONTROLSCHEMAa

GRANTCONTROLSCHEMAutility

GRANTCONTROLSCHEMAutil

GRANTCONTROLSCHEMAmdt

DENYALTERSCHEMAdb_datareader

Hmm, i guess this should not be a factor, but this db was ported from 2005 where it was created to now 2008 R2.

Richard Moore-400646 SSCrazy Points: 2384 More actions · Answer 11

And there you have your answer. GRANT CONTROL ON SCHEMA dbo essentially gives full control to the dbo schema and supercedes the db_datareader database role.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ajsnyman (1/5/2012)
GRANTCONTROLSCHEMAdbo

That'll do the job... That's full control over the schema

Could it be an explicit grant (not a role)?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ajsnyman Right there with Babe Points: 732 More actions · Answer 13

Thank you all so much, can't believe how many hours whent into this silly thing.

I now looked at the dbo schema's permissions and there is a public user with explicit grant permission allowed for user 'public'.

To be honest, it seems I do not understand the function of the CONTROL permission.

I guess this will also mean that all the db users will have this permission?

I should not have any problems removing this permission? Other than that I will have to check up on my current users still work 🙂

Thanks again :w00t::w00t:

Perry Whittle SSC Guru Points: 234024 More actions · Answer 14

ajsnyman (1/5/2012)
To be honest, it seems I do not understand the function of the CONTROL permission.

CONTROL is the highest available within the database. CONTROL implies all other permissions.

Granting CONTROL at the schema level is quite brave IMHO 🙂

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

ajsnyman (1/5/2012)
To be honest, it seems I do not understand the function of the CONTROL permission.

Full and complete permissions to the object and anything within it. (for example, a sysadmin has the permission CONTROL SERVER). So control on the schema means that the users could do anything to the schema (including drop it) and do anything to any of the objects within the schema (read, update, alter, create, drop, etc)

I guess this will also mean that all the db users will have this permission?

Yes. Every single user (and maybe even logins that don't explicitly have access to this DB) will have it

I should not have any problems removing this permission? Other than that I will have to check up on my current users still work 🙂

Maybe not that simple... Reducing permissions means you'll need to test all the apps to make sure nothing breaks,

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass