Trouble with permissions and accessing database

Question

Hello All,

I'm having a lot of trouble with accessing a database from a .NET application.  The database was created from the administrative account. I'm trying to access the data within the database from a different (non-admin) user account on the same machine.

My username is Visualization1\dennhardt and it's set to Windows Authentication.

I don't know if it's schema issues, role issues, securable issues, authentication issues, or etc...

Does anything here look problematic?

Appreciate the help in advance.

Target database "Migration" security, users, and roles:


Server wide security, users, and roles:

---

AndrewDen

Answer 1

Are you trying to SELECT or EXECUTE?

GRANT EXEC ON some_sp TO [Visualization1\dennhardt]-- Grant permission on a single procedure.
GRANT EXEC ON SCHEMA::dbo TO  [Visualization1\dennhardt]-- Grant perpmission on all procedures in
                                      the dbo schema
GRANT EXEC TO  [Visualization1\dennhardt]-- Grant EXEC permission all procedures in the database.


To grant all "normal" permissions on a table

   GRANT SELECT, UPDATE, DELETE, INSERT on pec TO  [Visualization1\dennhardt]

To grant freddie access on tables in a schema:

   GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO  [Visualization1\dennhardt]

To grant him access on all tables:

   GRANT SELECT, UPDATE, DELETE, INSERT TO  [Visualization1\dennhardt]

SELECT 'REVOKE EXECUTE ON ' + quotename(s.name) + '.' + 
                              quotename(o.name) + ' TO  UserJoe'
FROM   sys.objects o
JOIN   sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.type = 'P'

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

In SQL Server Management Studio, from Admin account, I executed:
GRANT EXEC TO  [Visualization1\dennhardt]
GRANT SELECT, UPDATE, DELETE, INSERT TO  [Visualization1\dennhardt]


Here's some of my C# code (it stops at con.Open(); never executing the query command):
string conString = "Data Source = VISUALIZATION1; Initial Catalog = Migration; Connection Timeout = 21600; Integrated Security = True"; 
                con = new SqlConnection(conString);
                com = new SqlCommand();
                com = con.CreateCommand();
                com.CommandTimeout = 0;
                com.CommandType = CommandType.Text;
                com.CommandText = "SELECT v.[Latitude] FROM dbo.Latitudes as v WHERE v.[ID] = @row";
                com.Parameters.Add("@row", SqlDbType.VarChar).Value = reference;
                con.Open();
                object lat_value = com.ExecuteScalar();
                string lat_lines = Convert.ToString(lat_value);
                con.Close();

So, I'm trying to both execute the query via a SELECT clause.

Thanks for your help.

---

AndrewDen

Answer 3

Are  you sure that you successfully connect to SQL Server? Has that login access  the SQL Server? 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 4

I've tested the connection C# side and it says that SQL Server connects okay, but when I run the code, the con.Open() line does not run to com.ExecuteScalar() like it's supposed to.

How can check that the login has connection to SQL?  Does that have anything to do with status/securables for that login?

---

AndrewDen

Answer 5

In SQL Server Management Studio, from Admin account, I executed:
GRANT EXEC TO  [Visualization1\dennhardt]
GRANT SELECT, UPDATE, DELETE, INSERT TO  [Visualization1\dennhardt]

If you want to grant permissions on all objects at the database level, specify DATABASE as the securable class:

GRANT SELECT, UPDATE, DELETE, INSERT, EXECUTE ON DATABASE::Migration TO  [Visualization1\dennhardt];

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 6

It still seems to hang up on the same line: con.Open()

Under the Login properties > Securables, there are several things checked with the Grantor column set to "sa."  Could this be an issue?

---

AndrewDen

Answer 7

It still seems to hang up on the same line: con.Open()

Under the Login properties > Securables, there are several things checked with the Grantor column set to "sa."  Could this be an issue?

The grantor shouldn't be an issue. 

You mention that the app is hanging during open but your other statement says the connection is ok.  Any error message?

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 8

I can't help you with your C# code. But to determine if you are connected, connect with SQL Server Management Studio and execute:

SELECT * FROM sys.dm_exec_connections;
SELECT * FROM sys.dm_exec_requests;

These two queries will tell you lots about the connections.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 9

Does this look problematic?  (result from SELECT * FROM sys.dm_exec_connections)
client_net_address    client_tcp_port       local_net_address          local_tcp_port
<local machine>       NULL                     NULL                             NULL
<local machine>       NULL                     NULL                             NULL

Is there anything that I should look for from the results of the SELECT * FROM sys.dm_exec_requests query?

You're right.  There is a lot of information, and I cannot comprehend most of it.  Appreciate any guidance here.

---

AndrewDen

Answer 10

@Dan:

To answer your question, the app continues to hang up on the con.Open() line.  So far, no exception message has been thrown, which is very odd.

---

AndrewDen

Answer 11

Security problems don't cause you to hang on Open().  Security would immediately give you an exception.

I suspect you have a connectivity problem, and the "hang" is because your Connection Timeout is much, much, much too long.

Connection Timeout = 21600

That's 6 hours.  Drop that to 30 at the most.  The default of 15 works well for most apps.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 12

That seems to be the solution!  Thank you very much.

---

AndrewDen