Skip to main content
Database Administrators

Return to Question

edited tags
Link
Yasir Arsanukayev
Yasir Arsanukayev
  • 3.2k
  • 3
  • 22
  • 30
added 25 characters in body
Source Link
marc_s
marc_s
  • 9k
  • 6
  • 45
  • 52

I have a MSSQL DatabaseSQL Server 2005 database to which I would like to LIMIT access. I have done the following:

  • Created a database ROLErole called NO_HRNO_HR
  • Added the TABLEStables to this ROLErole under SecurablesSecurables that should be blocked
  • Set all permissions to DENYDENY for these tables
  • Assigned my users this ROLErole under Database Users -> Role MembersDatabase Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the ROLErole.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

  • SQL Server 2005 Standard
  • Windows 2003 R2 Enterprise
  • Users are DOMAIN logins
  • Any help would be appreciated.

SG

I have a MSSQL Database to which I would like to LIMIT access. I have done the following:

  • Created a database ROLE called NO_HR
  • Added the TABLES to this ROLE under Securables that should be blocked
  • Set all permissions to DENY for these tables
  • Assigned my users this ROLE under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the ROLE.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

  • SQL 2005 Standard
  • Windows 2003 R2 Enterprise
  • Users are DOMAIN logins
  • Any help would be appreciated.

SG

I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:

  • Created a database role called NO_HR
  • Added the tables to this role under Securables that should be blocked
  • Set all permissions to DENY for these tables
  • Assigned my users this role under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the role.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

  • SQL Server 2005 Standard
  • Windows 2003 R2 Enterprise
  • Users are DOMAIN logins
  • Any help would be appreciated.

SG

Source Link
S.Gia
S.Gia
  • 113
  • 1
  • 4

Limiting user access to tables based on a ROLE

I have a MSSQL Database to which I would like to LIMIT access. I have done the following:

  • Created a database ROLE called NO_HR
  • Added the TABLES to this ROLE under Securables that should be blocked
  • Set all permissions to DENY for these tables
  • Assigned my users this ROLE under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the ROLE.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

  • SQL 2005 Standard
  • Windows 2003 R2 Enterprise
  • Users are DOMAIN logins
  • Any help would be appreciated.

SG