locked
SQL Server Security RRS feed

  • Question

  • Hi,

    i Have a database and a sql server user, i want to assign only few table rights to that particular database.

    Means: user have limited number of table to update,delete and insert, except this he is not able to do anything on anothers table.

    how it is possible in SQL Server 2005 Enterprise.

    Thursday, June 5, 2014 7:19 AM

Answers

  • Hello,

    When you add a Login as a database user without any role membership, he can Access the database, but will be only able to query some less System view, not any user table/view/SP.

    You have to add the user to a database role or you have to explicit grant permissions to the existing tables, and that's what you have to do.

    As it's best you should create a user defined role and grant access for the tables to the role; then add the user to the role.

    See GRANT Object Permissions (Transact-SQL)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Saeid Hasani Thursday, June 5, 2014 7:52 AM
    • Marked as answer by Mandeep Joon Thursday, June 5, 2014 11:01 AM
    Thursday, June 5, 2014 7:29 AM

All replies

  • Hello,

    When you add a Login as a database user without any role membership, he can Access the database, but will be only able to query some less System view, not any user table/view/SP.

    You have to add the user to a database role or you have to explicit grant permissions to the existing tables, and that's what you have to do.

    As it's best you should create a user defined role and grant access for the tables to the role; then add the user to the role.

    See GRANT Object Permissions (Transact-SQL)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Saeid Hasani Thursday, June 5, 2014 7:52 AM
    • Marked as answer by Mandeep Joon Thursday, June 5, 2014 11:01 AM
    Thursday, June 5, 2014 7:29 AM
  • GRANT INSERT, UPDATE, SELECT ON
       MyTable
    TO User1 --for multiples, it's   TO User1,User2

    Refer below threads

    http://technet.microsoft.com/en-us/library/ff878066.aspx

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4c12914c-8364-48f4-ad35-db64fbeb0ff1/how-to-give-read-write-permissions-to-a-specific-table-in-a-database-of-sql-2008r2?forum=sqlgetstarted


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Proposed as answer by Uri DimantMVP Thursday, June 5, 2014 11:01 AM
    Thursday, June 5, 2014 8:55 AM
  • Neha has provided a solution, I would only add that if you wrap all DML operations into a stored procedure and then grant exec permission on that procedure/s it may even better in terms of security..(user does not have access to underlying tables but via stored procedures) 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 5, 2014 11:04 AM
  • If you create a Login and User on particular Database he will be getting access to all objects of that DB.Hence you have to Deny permissions for the user first and then explicitly assign the permissions to the objects. Use below scripts.

    Creating a login

    Creating a User

    Removing access to all DB Objects

    EXEC sp_msforEachTable 'DENY SELECT ON ? TO [Login]'

    --Granting access to specific objects

    DECLARE

    @TableName  ASvarchar(max)


    DECLARE

    @Script     ASvarchar(max) 


    DECLARE

    CurGrantCURSORFORSELECT   


    name

    FROMsys.tablesWHERE   


    name

    LIKE'Table Name'OR   

    OPEN

    CurGrantFETCHNEXTFROMCurGrantINTO@TableName


    WHILE

    @@FETCH_STATUS=


    BEGIN

     


    SET

    @Script='GRANT SELECT ON dbo.'+@TableName+' TO [LoginName]'


    EXEC

    (@Script)   



    FETCH

    NEXTFROMCurGrant 


    INTO

    @TableName   


    END


    CLOSE

    CurGrantDEALLOCATECurGrant

    Friday, June 13, 2014 9:08 PM