2

Greetings all, I've run into a problem that has me stumped.

I've put together a database in SQL Server Express, and I'm having a strange permissions problem. The database is on my development machine with a domain user: DOMAIN\albertp. My development database server is set for "SQL Server and Windows Authentication" mode. I can edit and query my database without any problems when I log in using Windows Authentication.

However, when I log in to any user that uses SQL Server authentication (Including sa) I get this message when I run queries against my database.

SELECT * FROM [Testing].[dbo].[AuditingReport]

I get:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'auditor'.

I'm logged into the server from SQL Server Management Studio as 'auditor' and I don't see anything in the error log about the login failure.

I've already run:

Use Testing;
Grant All to auditor;
Go

And I still get the same error. What permissions do I have to set for the database to be usable by others outside of my personal domain login? Or am I looking at the wrong problem?

My ultimate goal is to have the database be accessible from a set of PHP pages, using a either a common login (hence 'auditor') or a login specific to a set of individual users.

skaffman
  • 398,947
  • 96
  • 818
  • 769
Albert Perrien
  • 1,153
  • 12
  • 27

1 Answers1

1

GRANT ALL is not performing the action you believe it to be.

I suggest for testing purposes that you consider using Database Roles in order to manage the privileges of your User.

Here is a list of the available Database-Level Roles

You can add an existing User to a Database Level role by using the system stored procedure sp_AddRoleMember. For example, the following will provide READ permission to your User for all objects within the given database.:

EXEC sp_addrolemember 'db_datareader','auditor'

Ideally, you will likely want to consider defining your own Database Roles in order to manage privileges for your Database Users.

John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • Thanks! That helped; I can view the tables as 'auditor' now. Is there a separate permission to allow views? I still can't pull up the view 'AuditingReport', I get the same error when I try to run that as before. – Albert Perrien Jan 03 '11 at 21:40
  • GRANT SELECT ON AuditingReport TO auditor – John Sansom Jan 03 '11 at 21:52
  • I'm going to accept the answer given; I believe that I've found the root cause of the problem, and when I verify that, I'll post an update here. – Albert Perrien Jan 04 '11 at 01:26
  • Ok, solved it. The problem was that the view I was trying to access had a linked table in it; and the database that hosted the linked table did not have access set up for the 'auditor' account. I modified the other database to provide that access( via Linked Server Properties - Security), and that solved the problem. – Albert Perrien Jan 04 '11 at 19:39