C# Discussion Boards - CodeProject

19-Sep-04 8:22

Hi,

I am attempting to connect with MSDE for the first time with V# .NET, so this is probably a simply questions but I would appreciate help.

I have downloaded an SQL frontend called Teratrax which seems to be great?

My problem is getting full access to the SQL tables I have created, I have difficulty understanding the difference between: Database users and Server logins?

The command SqlConnection is giving me problems, what is 'dbo' user and whats the password for it, my tables appear to be owned by this user?

Any help would be greatly appreciated! Smile | :)

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Colin Angus Mackay19-Sep-04 9:39

19-Sep-04 9:39

Dam.NET wrote:
Database users and Server logins?

A server login is anyone that is permitted to log in to the SQL Server. Each server can host multiple databases, so the Database Users are the users permitted to access a particular database. When a database user is created it must know which login the user is accessing the database with.

To create a login in T-SQL use the command sp_addlogin[^]. And to create a database user use sp_grantdbaccess[^]. These links should give you more information.

Dam.NET wrote:
what is 'dbo' user and whats the password for it

The "dbo" is the DataBase Owner. There is no password for it. The "sa" account (System Administrator) is automatically the dbo, as it any user that has the sysadmin role.

As a general rule all objects (tables, views, stored procedures etc.) in the database should be created by a sysadmin user. Users with the db_owner role are permitted also to create objects but they are owned specifically by that user. At the moment I don't want to overload you will lots of information, suffice to say that is is better to have a sysadmin user create all the database object as it can be a real pain in the neck sorting out the security within a database if you allow joe-user to start creating tables, views, stored procedures etc.

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

Dam.NET19-Sep-04 10:08

19-Sep-04 10:08

Fantastic, my open command now works, but (sorry!) I am getting the following response from the SQL server?

Here is my code:
SqlConnection sql = new SqlConnection(ConStr);
sql.Open();

string sqlcommand="SELECT * FROM Users;";
SqlCommand mySqlCom=new SqlCommand(sqlcommand,sql);

SqlDataReader sqlreader;
error--> sqlreader=mySqlCom.ExecuteReader();

The error reported is:

SELECT permission denied on object 'Users', database 'brUsers', owner 'dbo'.

I would image these SQL security options could get quiet complex for a medium/large application?

Those links to the Microsoft site are great!!! Big Grin | :-D

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Dam.NET19-Sep-04 10:12

19-Sep-04 10:12

Just noticed something I find odd?

When you select a table you get a list of users names? the one I created is now in Database users and in Server logins but not within this table list?

How many passwords do you need for SQL???? Confused | :confused:

Secure its more like fort knox!!!

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Dam.NET19-Sep-04 10:23

19-Sep-04 10:23

OK, OK, Sorry I have now got the login working but I need help with a command?

I did what you said and I can see how they create login and user accounts, Thanks.

The reason it started working for me was I deleted my simple test data table and recreated is specifing my user account instead of the 'odb' which is the default? how can I add user access to a table that is already created?

Thanks again for all this help, I owe you at least a pint!!! Big Grin | :-D

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Colin Angus Mackay19-Sep-04 10:58

19-Sep-04 10:58

Dam.NET wrote:
how can I add user access to a table that is already created?

As you posted this as I was typing my other, longer, reply you should get the answer in there. But just to refresh:
GRANT SELECT ON TableOrViewName TO UserOrGroupName

Dam.NET wrote:
Thanks again for all this help, I owe you at least a pint!!!

Thanks. Cheers! Beer | [beer]

Colin Angus Mackay19-Sep-04 10:50

19-Sep-04 10:50

Dam.NET wrote:
How many passwords do you need for SQL????

Each login has one password.

Of course, you can also add Windows users and groups which allows Windows to perform the authentication on behalf of SQL Server. All you would need to do then is log in to windows.

Colin Angus Mackay19-Sep-04 10:45

19-Sep-04 10:45

What this means is that the account you used to log into the SQL Server does not have SELECT permission for the table Users.

The easiest way is to GRANT SELECT ON Users TO PUBLIC

This means that anyone who is a user of your database can see what is in the table, but not alter it. For more information see the SQL Server books-online: GRANT[^]

Dam.NET wrote:
I would image these SQL security options could get quiet complex for a medium/large application?

It can get complex. That is one reason I said in another thread that all objects in the database should be owned by the dbo, unless you have a very very compelling reason not to. Here is an example in narrative form:

Jack, a db_owner (can create database object, but is not a dbo), creates a table and populates it with some interesting data.
Bob, who is a sysadmin (and therefor is a dbo) sees that some of the intersteing data would be useful to others and creates a view to allow access to only part of the table. He grants permission to public on the view.
Jill, a regular user, wants to use the view and performs a "SELECT * FROM BobsViewOfJacksTable", but the security fails for some reason.
Jill checks the security settings and she has the right to use the view. She is confused and emails Bob.
Bob pastes the select statement into his query analyser and gets the rows back he expects. But doesn't understand why Jill doesn't get the same result. He calls Jack, who tries out the view and it works for him too. Everyone is very confused, because the view is accessable to public, and no-one has a specific deny access on it. Eventually, after two days of trying to work this out, Bob changes the owner of the table to dbo and everything works.

The reason: Jack has only granted select permission to members of sysadmin and db_owner to his table. Jill is neither. However, if the table is owned by the same user as the view the second security check doesn't take place. Jill has access to the view, the view is owned by the dbo, therefore anything that the view uses that is also owned by dbo does not need a second security check, anything that is owned by some other user needs an additional security check.

Does this make your head hurt? It makes mine hurt.

The way this works means that, if everything is owned by the dbo, you don't have to grant access directly to your tables. Instead you can protect them behind views and stored procedures, which the users do have permission for.

It is generally a good idea that permissions are scripted, so everything can be reset easily. Also, if you are doing that it is a good idea that any changes are reflected in the script and the script is kept in a source control system to that the history of changes can be seen easily.

I hope all this helps.

Dam.NET19-Sep-04 11:04