triggers and sql users - SQL Server Forums

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 Development (2000)

triggers and sql users

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-07 : 16:20:53

can i in a trigger, check on an update command, that only a specific
sql user is allowed for an update, and i want to allow the update only on a certin column and to block any other column update
how can i do that?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-07 : 22:22:20

This is done through security .

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-08 : 02:15:06

the security dosent not answer on my question
beacuse suppose i have 2 sql user, with a permission to update a certin table.
but on this table i want each one of them to have a permission to update only a certin column
say user 1==>update column a
say user 2==>update column b

how can i do this?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 04:01:01

you can give permissions on individual columns. sodeep was telling you the truth... it's handled by security settings, not triggers

Em

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-08 : 04:39:58

in sql server 2000?
can you give me info how to do it?or an article that explains how to do it?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 04:42:49

i don't have a 2000 installation anymore to check, but from memory it's in the properties of the table. right click and explore your options a bit?

Em

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-08 : 04:58:43

i did it
i have permissions there
but the permissions are in the level of update/insert/delete in the level of all of the table
and not on one certin column!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 05:15:57

it is there i'm sure. other than that look at GRANT in BOL to script it out

Em

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-08 : 05:24:13

again!
GRANT work for all the table and not a certin column
maybe this possible i

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 06:23:55

from SQL2000 BOL...

quote:

SELECT and UPDATE statement permissions, which can be selectively applied to individual columns of a table or view.

it really is there.... honest

Em

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-07-08 : 06:37:13

Is it not just

Grant [Option i.e select] on [Table](Column list if needed) to [user]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-08 : 06:40:25

from BOL...

quote:

GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-07-08 : 09:30:59

mmm now i see
1)if sow after i chage the permissions with the aboe example, how can i check what was set?
2)and any way how in a stored procedure for example can i check which sql user was used to access the stored procedure?

thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources