permission - 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)

permission

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-12 : 11:18:00

Hi

I have a user that I need to grant access to a database, he should be able to ...

* create/edit/delete tables, views and stored procedures

So I created a user and then set his default database to the one he should access, then in user_mapping for that database I have checked.

db_Datareader
db_datawriter
db_ddladmin
db_denydatareader
db_securityadmin
public

But when this user try to access the tables with Sql server management studio he get this error "The select permission was denied on the object 'extended propoerties', database 'mssqlsystemresource', schema 'sys'. Microsoft SQL Server, Error: 229)"

What do I need to change in order to get this to work?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-12 : 11:36:12

Remove this:

db_denydatareader(this will deny select)
db_securityadmin(security purposes)

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-12 : 11:45:59

Excellent, Thank!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources