creating roles - 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

General SQL Server Forums

New to SQL Server Programming

creating roles

Author

Topic

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-13 : 23:37:21

I created a role called user and would like to insert rows in 2 tables called tbl1 and tbl2 and i am adding the user that is created to be the member of the role A

Create role user
Grant insert on tbl1, tbl2 to user
sp_addrolemember A, user

when i run the sql i get an error , having to do with the ','

is it not possible to do that? or i have to write the Grant insert statement for each table separately?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-14 : 01:46:08

Did you look at books online before posting? Usage of sp_addrolemember is:

sp_addrolemember role, user

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-14 : 11:24:48

Thanks.

i previously created a user = Lisa
and now i'm creating a role called user1

Create role user1
Grant insert on tbl1, tbl2 to user
sp_addrolemember Lisa, user1

Basically im having trouble with the grant insert part. is it not okay to grant multiple inserts?

or would i have to write it this way:
Grant insert on tbl1 to user
Grant insert on tbl2 to user

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-14 : 11:42:33

Yes. You will have to write two separate GRANT statements.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-14 : 16:22:34

Is it time to get formal sql training?

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-14 : 16:48:24

im still in the beginner stage. thanks for your help