Permissions....I must be losing my mind....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Permissions....I must be losing my mind....

Author

Topic

MuadDBA

628 Posts

Posted - 2003-05-07 : 13:32:08

I am trying to DENY the delete permisions for a user, and I can't seem to get it to work. Here's the scoop:

SQL 7.0, SP3
User was formerly a member of the Sysadmin server role.
User was formerly of the DBO database role.
Was removed from both of these roles.
Was added to db_datareader and db_datawriter roles.

Edited permissions, through EM, for the table in question (table is owned by DBO), and put a little red X in the delete box. No effect.

Executed the DENY statement: DENY DELETE ON IMPORTLOADS TO SMEAD
No effect.

This user can still delete from that table to his or her heart's content. What am I doing wrong? BOL suggests that DENY is all you ever should have to do, even going so far as to say it should supercede role abilities...so what am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 13:49:52

I just tested out this scenario and it works fine for me. Just create an account called aa and put it in the db_datareader and db_datawriter roles in your user database. Then run this following script in your user database:

set nocount on

create table deletetest
(
column1 varchar(50) not null
)

insert into deletetest values ('Tara')
insert into deletetest values ('crazyjoe')
insert into deletetest values ('robvolk')
insert into deletetest values ('graz')

select * from deletetest

deny delete on deletetest to aa

Now login into Query Analyzer as aa, then perform DELETE:

delete from deletetest where column1 = 'tara'

I get:
Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'deletetest', database 'DTS', owner 'dbo'.

Now drop deletetest in the first session:

drop table deletetest

If you get the same error as me, then DENY works fine but then something must be superceding the DENY permission for SMEAD. Is this user a member of the administrators group (not sure if that would supercede DENY though)?

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 13:52:51

The user was formerly a member of the sysadmin role.

According to BOL, DENY supercedes everything...can you tell me where I should be looking for something that would supercede the DENY?

I am not getting any errors when I execute the statement, nor do I see any reason this user should cause me such grief anymore.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 13:59:31

The sysadmin role is a SQL Server thing. The administrators group is a Windows thing. Check the administrators group in Computer Management (right click on my computer on your desktop and go to manage) to see if SMEAD is in there.

quote:

I am not getting any errors when I execute the statement

Did you run the test that I have in the previous post or did you run some other statement?

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 14:05:38

Sorry, the User does not exist in Windows NT, it is a SQLServer user only.

I ran your test and something wierd happened. OK, I didn't run your test exactly. I added that user and tried to have it delete from the Importloads table....the permissions were denied before I even set any of them.

I checked the public role and it has no defined permissions there, I checked object-level permissions and it has nothing defined there for any user other than SMEAD.

Geez this is wierd.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 14:15:25

Run sp_helprotect (doesn't require any parameters) in your user database. Post the results here or weed through the results and figure out what the conflict is.

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 14:35:29

Well here is what I have related to that table:

Owner Object Grantee Grantor ProtectType Action Column

dbo ImportLoads SMEAD dbo Deny Delete .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 14:44:45

I have been running the test on a SQL Server 2000 box with sp2. I wonder if your problem is SQL Server 7.0 with sp3 related. Do you have another SQL Server 7.0 box that you can test this on (one that has sp4 or sp2 on it)?

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 14:50:00

I suppose I could try to rig one up, but I am not sure I could duplicate the problem first and then upgrade the server to SP4...I suspect there's something wierd going on with this server, hence the strange problems I am having where it will deny a new user with no permission criteria but not a user who previously had permissions.

I can't imaging something like this slipping through the cracks at Microsoft (OK, yes I can, but still...) for this long. There has to be something I am not seeing or doing right.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 14:53:30

Have you tried REVOKE instead of DENY? REVOKE removes a previously granted permissions. I can't remember if REVOKE is available in SQL Server 7.0 though. I also don't have any SQL Server 7.0 machines here to help you test this.

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 15:02:03

REVOKE is a SQL 7.0 command as well....but alas, it also failed.

{sigh}

MuadDBA

628 Posts

Posted - 2003-05-07 : 15:09:26

This is stupid....

The changing of permission didn't take effect until I stopped and started the server. That is

SOOOOOO

STUUUPIIIIID!!!!

And that's all i have to say about that.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-07 : 15:13:06

I don't think you have to stop and start the server...you just have to kill the connections, it seems like the connection obtains the security "properties" when it's established...I think

But then again...this is microsoft

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 15:14:48

Well that is strange. I agree with Brett that a restart should not have had to occur but probably SMEAD hadn't disconnected yet.

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 16:12:21

Yeah that could be my problem, I was doing everything with all the users connected.

Thanks so much for your patient agonizing with me. It's been fun. Or maybe not, but you guys were nice, so thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 16:25:47

Hey no problem. Just curious, what did this guy do to your system that would require the DELETE permission removed from his account?

Tara

MuadDBA

628 Posts

Posted - 2003-05-07 : 16:53:20

Well, this is a user act that is used for everyone who uses an application. Yes I know this is a bad thing, however it was decided by the software designers to do it this way....then bring in the DBA, right?

Anyhow, mosto f the DB security is actually handled through the application by the application level login (I know, more bad stuff, but this was the least worst option of software we could find). However, there is no security on the import jobs, so anyone can create, run or (here it comes) remove a job to import data into the DB. Someone out there (and thanks to the global login I can't tell who, even with profiler) keeps removing one of the import jobs (and a popular one to boot, which means I get a trouble ticket almost every day)...I had tried before to lock it down, but because they had configured the global user as a system admin, I couldn't bock them. After a LONG time of going around, I finally am able to remove them from the sysadmin role, so now I should finally have it locked down.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources