pjcvsi
asked on
Securing Tables In SQL 2008
We are using Microsoft Dynamics SL 7.0 with SQL 2008. This is our company enterprise application for all business modules.
We also use Crystal Reports.
Is there a way to secure tables within the MD Database so that employees can't use crystal reports to drill down and find, say, payroll information?
I don't want to secure the whole database, just 5 or 6 tables and restrict it to 2 or 3 people.
Thanks in advance
We also use Crystal Reports.
Is there a way to secure tables within the MD Database so that employees can't use crystal reports to drill down and find, say, payroll information?
I don't want to secure the whole database, just 5 or 6 tables and restrict it to 2 or 3 people.
Thanks in advance
ASKER
I read your response, i have the users created, but based on the list of access rights, i don't see anything that will *block* a hand full of tables from one or more users. These look more like permissions over the database as a whole.
I specificlly don't want anyone except two people to have access to the PAYROLL table within the MSD7 Database.
I specificlly don't want anyone except two people to have access to the PAYROLL table within the MSD7 Database.
sir,
Create one user,give all table rights manually except payroll table.
That way u can achieve.
Create one user,give all table rights manually except payroll table.
That way u can achieve.
ASKER
Brichsoft,
I can do that with a group as well correct? then assign the users i don't want to access payroll to the group.
More so my quesion is, how do i permit/deny rights to a table and not a database.
Thanks,
I can do that with a group as well correct? then assign the users i don't want to access payroll to the group.
More so my quesion is, how do i permit/deny rights to a table and not a database.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for above screen Right click on table -> select properties.
ASKER
I have denied one table from myself. I am not a DB_Owner (that i'm aware of, i believe i have removed that so i am only "Public"), and yet when i load up crystal reports i can still see the data in the table.
Based on the picture that you posted above, i have checked deny for everything.
Based on the picture that you posted above, i have checked deny for everything.
Hi,
are you able to run query in database with deny user?
If so then there is some additional role to that user.
are you able to run query in database with deny user?
If so then there is some additional role to that user.
ASKER
Sorry i was still a dbadmin which i couldn't deny myself for, but it works for non-dbadmins..
To secure few tables, you need to create table specific users.
For more help on user, check out below.
If you want to create new user in sql server,check out following link.
For creating users in database, you need to create login.
- http://msdn.microsoft.com/en-us/library/aa337562.aspx
For creating user
- http://msdn.microsoft.com/en-us/library/aa337545.aspx
To Assign specific rights of access, Give permission based on from your requirement
- http://www.mssqltips.com/tip.asp?tip=1718