Access in SQL server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Access in SQL server 2005

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-08-12 : 03:08:49

Hi All,

For a particular user i have given the DB_owner access but still she is not able to access any table from the DB.
It thrown with an error:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Employee', database 'Junior', schema 'dbo'.

If i given with Sys_admin she can able to access but i don't want to given her sys_admin.

Please help how to check grant or deny on this user.

Thanks,
Gangadhar

Liam
Starting Member

6 Posts

Posted - 2010-08-12 : 14:34:18

the easiest way is to
login to Management Studio
go to security/logins/<login name>
right click to get properties
go to user mapping
choose the database you want to allow access
tick 'map' box
then tick 'db_datareader' in the box below

this will give read permissions to all tables in that database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 14:38:34

or use GRANT T-SQL statement

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-08-13 : 02:57:16

Hi,

Even after giving the access in user mapping tab as db_datareader user not able to access..

still they are getting the error
The SELECT permission was denied on the object 'Employee', database 'Junior', schema 'dbo'.

Also i tried to give access by grant but got error

Cannot find the user 'ganga', because it does not exist or you do not have permission.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:40:20

does user ganga exist? then which username you're using to connect?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-08-14 : 04:39:41

Can you check they are not being denied access to that particular object?
You've mentioned they can access via assigning SysAdmin access - therefore allow also follow advice from Liam

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources