Giving permission for each table within a database

  • I want to give different permission for different tables within a single database for a user.For example a user has full permission to a table while for another table only read permission.How it can be done within a database?

  • you create multiple roles which contain the permissions you want to be able to assign;

    adding a user and granting permissions are a three part process.

    first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;

    users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)

    here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.

    In your Case, I imagine you want to Create a Role that has permissions to very specific objects, but we'll wait to see if you need to provide more details.

    USE [SandBox] --my db for users to do stuff.

    CREATE ROLE [ReallyReadOnly]

    --give my new role READ permission to ALL tables

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

    --explicitly DENY access to writing

    EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

    --give my new role permission to run the procedures you've created

    GRANT EXECUTE TO [ReallyReadOnly]

    now that the role is created, we can add a LOGIN to the master database

    IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

    BEGIN

    EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'

    END

    now that a LOGIN exists, lets add a USER, tied to that login, to our database

    --make a user in the db for the matching login

    CREATE USER [bob] FOR LOGIN [bob]

    finally, add our user bob to the role we created

    EXEC sp_addrolemember N'ReallyReadOnly', N'bob'

    Here's an example for a couple of specific tables:

    Exec sp_addrole 'MyRole'

    GRANT SELECT ON MyTable TO MyRole

    GRANT SELECT ON MyTable2 TO MyRole

    GRANT INSERT, UPDATE, DELETE ON MyTable2 TO MyRole

    EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'

    EXEC sp_addrolemember 'MyRole', 'Jeff'

    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!

  • You can programatically do it with dynamic SQL or you can copy paste a bunch of lines only changing the table name (Object name)..

    Simplified syntax for GRANT

    GRANT { ALL [ PRIVILEGES ] }

    | permission [ (column [ ,...n ] ) ] [ ,...n ]

    [ ON [ class:: ] securable ] TO principal [ ,...n ]

    [ WITH GRANT OPTION ] [ AS principal ]

    Simplified syntax for DENY

    DENY { ALL [ PRIVILEGES ] }

    | permission [ (column [ ,...n ] ) ] [ ,...n ]

    [ ON [ class:: ] securable ] TO principal [ ,...n ]

    [ CASCADE] [ AS principal ]

    Just change the object name and repeat the code

    --Pra:-):-)--------------------------------------------------------------------------------

  • Lowell (3/21/2013)


    you create multiple roles which contain the permissions you want to be able to assign;

    adding a user and granting permissions are a three part process.

    first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;

    users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)

    here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.

    In your Case, I imagine you want to Create a Role that has permissions to very specific objects, but we'll wait to see if you need to provide more details.

    USE [SandBox] --my db for users to do stuff.

    CREATE ROLE [ReallyReadOnly]

    --give my new role READ permission to ALL tables

    EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

    --explicitly DENY access to writing

    EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

    --give my new role permission to run the procedures you've created

    GRANT EXECUTE TO [ReallyReadOnly]

    now that the role is created, we can add a LOGIN to the master database

    IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

    BEGIN

    EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'

    END

    now that a LOGIN exists, lets add a USER, tied to that login, to our database

    --make a user in the db for the matching login

    CREATE USER [bob] FOR LOGIN [bob]

    finally, add our user bob to the role we created

    EXEC sp_addrolemember N'ReallyReadOnly', N'bob'

    Here's an example for a couple of specific tables:

    Exec sp_addrole 'MyRole'

    GRANT SELECT ON MyTable TO MyRole

    GRANT SELECT ON MyTable2 TO MyRole

    GRANT INSERT, UPDATE, DELETE ON MyTable2 TO MyRole

    EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'

    EXEC sp_addrolemember 'MyRole', 'Jeff'

    Thats a good one.. Thanks for educating

    --Pra:-):-)--------------------------------------------------------------------------------

  • Hi Lowell

    Thank you for replying. Your method of giving permissions to tables works fine.But if in server roles if i set sysadmin(I did it in SSMS) for that user then those permission given for table not works.All roles created gets neglected. I want permission for most of the database as admin and for some table in about 3 database i want to restrict the access for that user. So i gave sysadmin. So why does it will not work as i expected?How to solve it?

  • winmansoft (3/24/2013)


    Hi Lowell

    Thank you for replying. Your method of giving permissions to tables works fine.But if in server roles if i set sysadmin(I did it in SSMS) for that user then those permission given for table not works.All roles created gets neglected. I want permission for most of the database as admin and for some table in about 3 database i want to restrict the access for that user. So i gave sysadmin. So why does it will not work as i expected?How to solve it?

    you can't.

    once you give someone sysadmin, no other permissions matter; they can do anything to any object in any database. Also roles are cumulative, so if i'm in a role that is everything + anther containing three items, i still have everything. Deny permissions don't affect a sysadmin.

    so if you give someone sysadmin, and then want to take something away, you have to go back to the idea of least permissions:

    take away sysadmin, and create teh role(s) for each database which carry the appropriate permissions instead.

    people like to grant sysadmin a lot because it solves permission problems instantly, but that's just being lazy.

    In general, I grant sysadmin for our developers on their Dev SandBox machines , but not on any servers with real business functions (like production) or admin servers.

    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!

  • Hi Lowel..

    You are correct. Everyone looks for an easy method. Now in my case i should give admin for almost all databases and restrict for only some tables in few databses. So i was thinking of any easy method of doing it. But its not possible i think. So now i have to give db_owner for the database for which i want full permission(admin permission) and i have to create roles for only database for which i have to restrict the access.Also in server roles i can give only "public" permission. Is that correct?

Viewing 7 posts - 1 through 6 (of 6 total)

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