Post reply

permissions for database and tables in sql server 2005

  • February 8, 2011 at 12:35 am

    #234587

    i have created a login bob for my sql server:

    create login bob with password='bobpass'

    now, i open sql server with this login

    now , i have a database named importdata

    i make bob the owner of importdata:

    use importdata

    exec sp_changedbowner 'bob'

    now, i want to create a table named csvtest in importdata database and deny select permission on it.

    how do i add a user named csvuser for the csvtest table?

    how do i deny select, insert permissions on the csvtest table?

    how do i deny create table (in importdata database) permission for the bob login?

    Please help. would be highly obliged for the favor.

    Thanks in advance.

  • February 8, 2011 at 3:09 pm

    #1284093

    Generally, you would create a login first, then create the user.

    To create the user:

    create user csvuser (see BOL for syntax)

    The user won't have any permissions for the table by default, so you don't really have to deny permissions. If you want to specifically deny a permission, use DENY (see BOL for syntax).

    You can't deny the database owner permission to create tables.

    Greg

  • February 8, 2011 at 11:00 pm

    #1284210

    In addition to the above dbowner is having full permission on database.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 9, 2011 at 1:28 am

    #1284255

    dear greg sir,

    --FIRSTLY,i CREATE A LOGIN TO ENTER INTO SQL SERVER WITH SQL AUTHENTICATION

    create login employeelogin with password='employee_login'

    --CREATE A USER FOR THE ABOVE CREATED LOGIN

    create user employeeloginuser for login employeelogin

    --as you told, the above created user will by default have no permissions on the table

    --but he would have to be granted permissions

    --so i executed this query on emp table, and it gave the select permission denied error which is fine

    EXECUTE AS USER='employeeloginuser'

    select * from emp

    --but now when i grant the permission on the table emp using below query:

    grant select on emp to employeeloginuser

    then it says that-----Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

  • February 9, 2011 at 12:16 pm

    #1284753

    Were you connected as employeelogin or is emp owned by employeeloginuser? If either of those is true, the message makes sense.

    Greg

Viewing 5 posts - 1 through 4 (of 4 total)

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