MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1831

If you use Windows security to grant permissions to your databases, how do you then prevent users from accessing the database and its objects when the users use something other than the intended application?  For example, let's say you have a custom application and that has a SQL backend.  And you have an AD group that you put users into and the permission that AD group to the database and the objects (stored procedures).  Now when the users run the custom application, the app connects using the Windows identity of the user.  But now let's say that user goes into something like Excel and connects to your database.  How do you prevent that person from executing the stored procedures?  For more thoughts, look at this MSDN post.

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/2939dd4a-68ab-4398-9c28-1183ebe51d79

Ok here is the problem I have had with groups. You cannot assign a default schema to the group. Therefore if you have, say, an admin group that is creating tables in a particular database you will all of a sudden have the named schema from each user assigned to the tables they create. This looks ugly real quick.

It is possible that there is an easy solution to this but I cannot find one. If you have any further insight because we would LOVE to use this for management!!

Peteo, that is why I mentioned that you really need to make sure that usage of fully-qualified naming is enforced when using groups.  The users need to explicitly use the schema name as a prefix when querying or else unintended results will occur if, for instance you have dbname.schema1.tableX and dbname.schema2.tableX.

This is probably were my being a neewb is not helping the matter but this still seems to be a problem to me.

In the scenario I described the application wants all of the tables to be in the .dbo schema, else it has some real issues.

Also if you have 4-5 people who are adding tables it seems to me it will be a real management nightmare with all the different schema's you now have in your database.

 Am I missing something here?

 And again I would love to make this work. I hate having to manage individual users. Using groups is SO much better from a management standpoint.

The issue with dbo schema being required in you application highlights the concern about best practices (ha! our root topic!) around developing with fully-qualified naming.  In this case it sounds like your application is making calls to objects without a defined schema: SELECT * FROM tableA instead of SELECT * FROM schemaX.tableA for example.  Therefore SQL Server has to make the assumption the the dbo schema is to be used if both dbo.tableA and schemaX.tableA both exist in the database.

If you're developing an application for use and separating objects out in schemas, there application's schemas should be organized by a role or business purpose, not by a developer name such as accounting.users or payroll.history.  Not dave.users and paul.users or paul.history.

OK I think I get what you are saying here. When you create a table you should specify the proper schema to use instead of letting it use the default user schema. Is this correct?

The problem is, and this is specific to create ESRI SDE feature class tables, is that it is done through the application. I do not think thier is a way to specify the schema, it just uses the default one associated with that particular logged in user.

So in this instance it is poor application control, correct? 

Precisely; on all counts.

Very cool. Thank you very much for your dialogue on this. It did answer several question i had!!!

Hi,

I was trying to implement the security with our new environment and i started using windows groups. It is good.. Glad to know that i chose the best practise one...

Biz

Nice to hear that Biz.

Thanks for the article on using Windows groups, Tim!

As a software developer (not a DBA expert) working on an applications that use SQL Server as the database server, one concept I've often heard told to the clients when setting up users  for SQL Server is that they should set up a separate user for each end user, so that they will be able to tell who performed given actions when looking at any audit logs or anything. The idea was that if everyone logged in using a single shared user, there would be no way to determine which person actually performed a given action. 

Is that not an issue when using the Windows groups solution, since SQL Server only sees the one login/user associated with the group?  It certainly seems to lead to cleaner management of privilages and access. Maybe the need to determine which individual was performing a specific action was being overstated?

 Thank you again for the great article.

-Charlie