SQL - Another "SELECT Permission was denied on the object" Issue

Question

(I know SO has well over a dozen questions with the same/similar title and I promise I've already gone through every one of them to no avail. I might have missed one or two minor details but I suspect my problem could be something more deep rooted. Enough said for intro)

Anyway, I have a database (let's call it FooDB) that I restored from a backup database. I can do whatever I want with it using SQL Server 2012 Management Studio, including SELECT, INSERT, or DELETE on any of the record, columns, or tables.

What I'm having trouble with is when I try to run a simple query against the same database, I get the following error:

An error has occurred.","ExceptionMessage":"The SELECT permission was denied on the object 'BarTable', database 'FooDB', schema 'dbo'."

There's a lot more to the stack trace but I won't post it here unless necessary. I'm using Visual Studio 2012 Ultimate and in one of the class files I specified the connection string as follows:

using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;" +
                                             "Initial Catalog=FooDB;" +
                                             "Integrated Security=true;"))
{
    con.Open();
    using (SqlCommand command = new SqlCommand("SELECT TOP 3 * FROM BarTable", con))
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ...  // code in this block is not shown as it is irrelevant to the issue
            }
        }
 }

Heeding to various suggestions I came across on SO, I tried the following:

• In SQL Server Management Studio, I selected 'Security' -> 'Logins' -> Right-click on the Windows login name and open the 'Properties' dialog -> In 'Server Roles' page, I selected dbcreator, public, serveradmin, setupadmin, and sysadmin

• While on the same 'Properties' dialog from above, I selected 'User Mapping' page, then checked the 'Map' checkbox for the database (FooDB), and assigned the following database role membership: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_owner, db_securityadmin, and public

• From the Object Explorer of the Server Management Studio, I selected 'Databases' -> 'FooDB' -> 'Security' -> 'Users' -> Then I right-clicked the Windows login name and selected 'Properties' to open the 'Database User' dialog. Then I selected 'Owned Schemas' page and checked db_backupoperator, db_datareader, and db_datawriter

• While on the same dialog from above, I selected 'Membership' page, and checked db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_owner, and db_securityadmin

That's all I can think of at this moment. Again, from within the SQL Server Management Studio (after logging in with the same Windows Authentication and server name) I can do whatever I want to with the database. But from within my C# class, it seems I can connect to the database but I lack the privilege to perform any action.

This is something I've been wrestling with unsuccessfully for almost a week. What gives?

Answer 1

Is the server authentication set to SQL server and Windows Authentication mode? To do so, right click on the SQLServer -> properties -> security.

Edit: Dint mean database, did mean the SqlServer

Answer 2

Have you tried running sp_change_users_login .

Sometimes when you restore a DB it creates new sid (security ids) for the users. When it does that, the DB user and the Server login look the same, but internally the sids don't match. sp_change_users_login will fix that.

Details on how to use the stored procedure can be found here:

https://msdn.microsoft.com/en-us/library/ms174378.aspx

Try this initially - you will have to edit it slightly to fit your user and login name:

EXEC sp_change_users_login 'update_one', 'YourDBUserName', 'YourServerLoginName';