locked
grant access to different users for different table in MSSQL 2008 RRS feed

  • Question

  • Hi! All

    I have one problem for permissions in ms sql 2008. I would like to grant access to different users for different table in one database.

    For example , I have a test database. Then , there are three table (tblOne,tblTwo,tblThree) inside it. Then, I like to give permission for a user who will be able to access only one table (tblOne).

    Please reply me if you have any solution for it.

    Thanks

    • Changed type cmmaung Sunday, March 21, 2010 9:22 AM
    Sunday, March 21, 2010 9:22 AM

Answers

  • Try something like this

     

    use master

    go

    create login L1 with password ='0123456'

    go

    use test

    go

    create user U1 for login L1

    go

    create table t1

    (c1 int,

    c2 char)

    go

    grant select on t1 to u1

    grant alter on t1 to u1

    grant delete on t1 to u1

    grant insert on t1 to u1

    grant control on t1 to u1

     

     

    -- Follow the same approach for login L2 and L3 with tables T2 and T3


    Thanks, Leks
    Monday, March 22, 2010 12:01 AM

All replies

  • Hi,

    this is solved by SQL Server security model - just create three database users and assign them appropriate permissions on the tables or use three databse roles with access to the tables and make appropriate users to be members of these roles...

    MP.

    Sunday, March 21, 2010 9:30 PM
  • Try something like this

     

    use master

    go

    create login L1 with password ='0123456'

    go

    use test

    go

    create user U1 for login L1

    go

    create table t1

    (c1 int,

    c2 char)

    go

    grant select on t1 to u1

    grant alter on t1 to u1

    grant delete on t1 to u1

    grant insert on t1 to u1

    grant control on t1 to u1

     

     

    -- Follow the same approach for login L2 and L3 with tables T2 and T3


    Thanks, Leks
    Monday, March 22, 2010 12:01 AM