Permissions not effective for Windows Authentication login

Hello All,

I'm hoping someone can help me with this puzzle.

Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....

My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -

SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.

So I decided to try the simplest possible scenario to make it work:

I've created a login DOMAIN\newEmployee with Windows authentication.

DOMAIN\newEmployee has been granted access to databasename.

By default, DOMAIN\newEmployee is a member of Public.

Public has been granted all available permissions on all objects.

ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.

Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAIN\newEmployee, querying -

select * from dbo.sysobjects

via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).

To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.

select * from dbo.sysobjects

runs successfully from Query Analyser (as to any queries on user-created tables).

What else is required to grant access to tables from a Windows authenticated login?

( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )

Any help would be appreciated.

Thanks.

Kim.

Moved to Security.|||

Let me see if I understand the scenario, please correct me if I am missing something:

· DOMAIN\newEmployee & DOMAIN\newEmploee2 are Windows domain users

· DOMAIN\newEmployee is a member of existingRole

· public has been granted the permissions you mentioned.

· DOMAIN\newEmploee2 can select from dbo.sysobjects

· DOMAIN\newEmploee cannot select from dbo.sysobjects and gets back a permission denied error

From your description, it seems like the most likely cause is that newEmployee has an explicit denied permission on dbo.sysobjects either directly or via a role membership. Check the permissions for all the roles and groups that newEmployee is a member of.

BTW. Some of the objects and permissions that you mentioned here are deprecated, they will still work on SQL Server 2005, but they are supported only for backwards compatibility.

Let us know if this information was useful or/and if you have further question, please also let us know what version of SQL Server you are using in order to better assist you.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

D'oh !! I've checked db_denydatareader instead of db_datawriter.

I'll now crawl into a hole and die of embarrassment.

Thanks for the help.

Kim.