Results 1 to 5 of 5

Thread: Very Urgent...

  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    37

    Unanswered: Very Urgent...

    Hello All,
    I have the Following instance..
    I want to create a table in SQL Server database, and there after I dont want any body to touch that table ..i mean no inserts/ updates / deletes etc ..
    How can i do that .. ?

    Please reply me soon..

    sai

  2. #2
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    DENY ALL ON 'YOUR TABLE NAME' TO 'LoginName'

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Arrow

    RE:
    DENY ALL ON 'YOUR TABLE NAME' TO 'LoginName'
    Note: If you Deny All, that will prevent 'LoginName' from interacting with 'YOUR TABLE NAME', (it will include denial of select rights which will prevent reading the table contents).
    ----------------------

    You might consider allowing select permission, and selectively denying update, insert, and delete instead (if that is your intent / meets your requirements).

    For Example:

    i ----------------------
    DENY INSERT, UPDATE, DELETE
    ON [YourTableName]
    TO Tom, Dick, Harry

    GRANT SELECT
    ON [YourTableName]
    TO Tom, Dick, Harry

    --Allows Tom, Dick, Harry to Select from YourTableName (but not to INSERT, UPDATE, or DELETE).

    ii----------------------
    --Whereas, the following would not even allow Tom, Dick, Harry select permissions on YourTableName:

    DENY All
    ON [YourTableName]
    TO Tom, Dick, Harry

  4. #4
    Join Date
    Aug 2002
    Location
    India
    Posts
    37

    thx but what about the Enterprise manager case

    hello,
    Thanks for u r suggestion..

    If i do the same as u suggested, Will that login owned persons be able to Delete the table/rows from enterprisemanager ?
    I dont want even that to happen.. Please suggest me accordingly

    Thanks and regards
    SAI

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Post Re: thx but what about the Enterprise manager case

    hello, Thanks for u r suggestion..
    Q1 If i do the same as u suggested, Will that login owned persons be able to Delete the table/rows from enterprisemanager ?
    Q2 I dont want even that to happen.. Please suggest me accordingly
    Thanks and regards
    SAI
    You are welcome. The following applies to MS Sql Server 7.0, and 2k (for 6.x and before you would use groups and different procedures).

    A1 IF:
    none of the Sql Server logins Tom, Dick, Harry are members of special Server Roles, or DB roles
    AND:
    none of Tom, Dick or Harry use NT Logon accounts that are members of groups with automatic membership in special Server Roles, or DB roles:
    THEN:
    If you run the following:
    ----------------------
    DENY INSERT, UPDATE, DELETE
    ON [YourTableName]
    TO Tom, Dick, Harry

    GRANT SELECT
    ON [YourTableName]
    TO Tom, Dick, Harry
    ----------------------
    none of the three logins (Tom, Dick, Harry) should be able to perform INSERT, UPDATE, DELETE operations on the [YourTableName] table from any kind of connection (query analyzer, isql, osql, enterprise manager, ADO, OLEDB, etc.,)
    --------------------------------------------

    A2 HOWEVER, IF:
    the Sql Server logins Tom, Dick, Harry ARE members of special groups they may simply grant themselves any rights necessary to do as they may please.

    To prevent any chance of such kinds of problems, one could check Tom, Dick, Harry memberships and remove each from:
    i all Server Roles
    ii all Database Roles (except public)
    --------------------------------------------

    NOTE #1: (you may check memberships from EM, or with tsql as below)

    To check server role memberships:
    EXEC sp_helpsrvrolemember 'sysadmin'
    EXEC sp_helpsrvrolemember 'securityadmin'
    EXEC sp_helpsrvrolemember 'serveradmin'
    EXEC sp_helpsrvrolemember 'setupadmin'
    EXEC sp_helpsrvrolemember 'processadmin'
    EXEC sp_helpsrvrolemember 'diskadmin'
    EXEC sp_helpsrvrolemember 'dbcreator'
    EXEC sp_helpsrvrolemember 'bulkadmin'

    To check database role memberships: (execute in the desired DB)
    EXEC sp_helprolemember
    ----------------------

    NOTE #2:

    This may be more advanced information than you are interested in at the moment, however, often custom roles are created to ease rights / permissions management.

    A few roles with thousands of members (Sql logins or NT accounts) are easier to manage (generally, you manage rights / permissions by assigning logins / NT Accounts appropriate role memberships). Changing rights / permissions for a given role automatically applies the changes to all role members. (Managing thousands of Sql logins or NT accounts individually to accomplish the same task is far more tedious!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •