Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 24th, 2003, 03:39 AM
Authorized User
 
Join Date: Aug 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Security of DB Structure

Two databases have been created in a SQL Server for different depts.
Both departments must not access the database of the other one.
Even the db structure is not allowed.

Both of them have installed the EM.
I find that the function "Generate SQL Script" can generate
the script of databases.
How can I protect the database structures.

Thanks a lot.



 
Old October 24th, 2003, 08:27 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How are you implementing your security?
Are you using Windows authentication or SQL Server autentication?

I am sure that each department has a group created within windows that you can use for each database. If you add groupA to databaseA only, they will not be able to view anything in databaseB.

How are you accessing the data? Visual Basic, ASP, Access?


Sal
 
Old October 26th, 2003, 09:37 PM
Authorized User
 
Join Date: Aug 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I use "SQL Server autentication".
User installed Enterprise Manager and
can generate sql script of all DB.




 
Old October 27th, 2003, 12:38 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As long as you are not giving them the sa username.
You must give them a username that you create for them. I do not recomment SQL Server authentication. Windows Authentication is better, because the users need to be logged into their network and pre-authenticated from windows to get access to SQL Server.

If you create an account for them in SQL Server authentication, make sure that you do not give them access to all databases.

They will ot be able to get access to all databases if you set-up security the right way.

Are both sets of users using the same usermane?


Sal
 
Old October 27th, 2003, 12:54 AM
Authorized User
 
Join Date: Aug 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

They have different usernames and can just access their own db.
However, after they did the "SQL Server Registration" in EM,
they know that there is another db in the SQL Server and
they can generate the other one's db script.






 
Old October 27th, 2003, 11:53 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You have not set-up security correctly. Do not add DatabaseA users to DatabaseB and do not add DatabaseB users to DatabaseA.

This will keep them from viewing anything in the other database.
Your security is set-up incorrectly!

Sal
 
Old October 27th, 2003, 10:17 PM
Authorized User
 
Join Date: Aug 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've re-configure the accounts and it works now.
Thanks a lot.


 
Old October 27th, 2003, 10:38 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good to hear that.
Listen, are you using MS Access as a front end? Please let me know, because there are a lot of ways that you can configure security a lot easier with MS Access front ends.
It is best to use Windows Authenthication because users do not have to use a user name and a password (users get tired of entering a password and username)

the reason I use windows authentication is, I will not nescesarily stay with a company forever, and with that security, a user can be trained to manage security when a new user comes into the company or an old user gets the ax.

Also it is a lot more secure because user think of a username and password as THE username for THE database. they share it with anyone and there goes your security.

Yes, I have had impossible users.





Sal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Permissions on DB without user-level security nbourre Access VBA 4 June 7th, 2008 08:09 AM
Code Access Security & Role Based Security robzyc C# 6 April 11th, 2008 02:31 AM
Flat DB structure into XML Hierarchy DaveQuested XSLT 1 January 29th, 2007 10:25 AM
System.Security.SecurityException: Security error coolcatjk Pro VB.NET 2002/2003 4 March 2nd, 2006 06:00 PM
Table / DB Structure mtangorre SQL Server 2000 1 June 27th, 2003 01:32 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.