Security

Ask something
Discussion Locked

SQL 2000 Security

0 helpful
11 replies

by agibbs 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here's my dilemma:

I need to lock down a SQL server as tightly as possible. Each database on the server has one user account, and I need to make sure that user account can access and VIEW only their own database in Enterprise Manager. Specifically, when a user logs in with Enterprise manager the following criteria should be met:

1. They should have *almost* full access to their own database. They should be able to do anything on their own database EXCEPT perform backups and restores.
2. When expanding the Databases folder in Enterprise Manager, they should not be able to see the names of other users' databases on the system.

3. They should not be able to look at Process Information.

4. They should not be able to view the names of other user accounts on the system.

5. They should not be able to create Jobs.

Suffice it to say, I need to make sure that when a user logs in they can view and make changes to ONLY their own database.

3,500 points to the guy orgal that can help me do this! I'm a seasoned SQL developer but this is my first shot at security/administration so any help is appreciated!

11 replies

by timthetoolman

Hi,

Its not possible to do what you want in SQL Server Enterprise manager.

Unfortunately the Enterprise manager is just that... and *Enterprise* manager and you dont have the levels of granularity on security you want.

You can certainly stop users from altering each other's databases by adding users to each database and excluding others from modifying or seeing the data, but you cant stop the Enterprise Manager from displaying all the databases.

You can also allow users specific roles on those databases and easily stop users from backing up or restoring. Thats supported.

You can work around the viewing of usernames by using NT authentication rather than SQL authentication and stick the domain username into the DB security. That should stop users from seeing other users of DBs.

I had hoped that the process information was retrieved through a stored procedure so security could be applied in the master DB, but I couldn't find it so it looks like process information is available to all. You need to be an admin to get detailed info, though.

I dont think you're going to be able to stop anyone from creating jobs but at least with the database level security, they'll only be able to effect their own databases.

Whycant you restrict the list of DBs? Well the database information is stored in the master database and SQL server doesn't have true row level security.

Hence the table containing the list of databases (where EM gets its info from) is either readable or not...

So in summary, you can achieve some of what you want but not all. The Enterprise Manager is not the right tool for them to use if you want this level of security...

Cheers,
Tim.

by timthetoolman

Yep, I think Joseph has covered your point one pretty well in detail but he didn't give you solutions to points 2 through 5.

If they're not important to you, then I guess you're ok, but otherwise...

Cheers,
Tim.

by joseph moore

SQL Server security is a strange animal, I admit that. But, you can do what you want to do. This will involve using Database Roles. That is the easiest way of doing this.
Open Enterprise Manager.
Expand Microsoft SQL Servers
Expand SQL Server Group
Expand the SQL Server you want to build a user connection to (in this example, TESTING)
Expand Security
Right-click on Logins and choose New Login.
In the Name box, click the ... button to the right and browse your domain (gonna call it DOMAINfor this example) and choose the NT/2000 account you want to have access to a database on TESTING. For example, account JOE. Click it and click OK.
Now in the Authentication section, choose Windows Authentication.
In the Defaults section, click the Database dropdown and choose the database you want user JOE to use (for example, SALES database on our TESTING SQL Server).
Click Database Acess tab. Click the Permit checkbox next to SALES db.
Click OK.

Ok. A SQL login is built that can connect to SALES on TESTING, and the login is linked to DOMAIN\JOE. This is the first part.

Now go to the Databases folder and expand it. It will show you the databases on TESTING.
Expand SALES database.
Click Users.
This is the list of database users who can access this database. You should have dbo, guest and possibly others. JOE should also be in the list.

Right-click JOE and choose Properties.
This brings up the Database Role window. You can select a preset database role for JOE.

by joseph moore

From SQL Books Online, here are the database roles and what access they have:

db_owner-Performs the activities of all database roles, as well as other maintenance and configuration activities in the database. The permissions of this role span allof the other fixed database roles.
db_accessadmin-Adds or removes Windows NT 4.0 or Windows 2000 groups and users, and SQL Server users in the database.
db_datareader-Sees all data from all user tables in the database.
db_datawriter-Adds, changes, or deletes data from all user tables in the database.
db_ddladmin-Adds, modifies, or drops objects in the database (runs all DDLs).
db_securityadmin-Manages roles and members of SQL Server 2000 database roles, and manages statement and object permissions in the database.
db_backupoperator-Has permission to back up the database.
db_denydatareader-Denies permission to select data in the database.
db_denydatawriter-Denies permission to change data in the database.

by joseph moore

Now, based off your requirements, I think you need to give JOE db_datareader and db_datawriter database roles. Just click the checkboxes for both roles and click OK.
That is it.
db_datareader can do Select statements on tables and use views.
db_datawriter handles Inserts, Updates and Deletes. I also think that db_datawriter gives the user Create Table rights.
Pretty much these 2 database roles gives users most access to create, modify and delete data. But these roles do NOT give backup rights to the user.

Now, when JOE opens up Enterprise Manager on his workstation and opens his connection to TESTING, all databases will be visible. Can't help that! Plus, he will be able to expand the other databases to see the root list of features (diagrams, tables, users, roles, views, etc). IF JOE clicks on Tables on one of these other database, he will get a big fat error saying his login does not have server permissions to view the object! Technically, Error #916.
Now, if JOE clicks on Users, he won't get an error on this foreign database; instead, the window will be blank. He won't see any configured users for these other databases!
Also, JOE can't run any backups on his database or the others. Again, nice popup errors
But, if JOE sticks to SALES database, then he is ok. He can view the tables, run queries against them, and such.

So, give this a try.

hope this helps

Now, on the

by joseph moore

I forgot a couple of things.
First, remove the GUEST login from ALL databases. ANY user with a valid login on the server (and JOE has one; that was made first) can access the GUEST database user. So, remove GUEST.

Next, JOE can make Jobs on the TESTING server, but they will fail if he makes them for any database that his login is not a User of. So, he will be able to make a job, but the job will always fail.
Now, if he makes a job that runs on the SALES database, it might run. I am not sure on that. I will have to test that.

by joseph moore

Ok JOE CANNOT run a Job he creates on TESTING. I wasn't thinking! You need to be a member of a Fixed Server Role to do that. So, eventhough JOE can make jobs to run against SALES, they won't work.

I run SQL Server Desktop Edition on my laptop so I was able to test all of this.

Good luck!

by joseph moore

I think I covered all topics except #3, because I was not sure what that was referring to. Performance monitor counters, perhaps?

For #2, "Now, when JOE opens up Enterprise Manager on his workstation and opens his connection to TESTING, all databases will be visible. Can't help that!" This is just how Enterprise Manager works. Sorry.
For #4, "Now, if JOE clicks on Users, he won't get an error on this foreign database; instead, the window will be blank. He won't see any configured users forthese other databases!"
And for #5, the previous 2 comments address the Jobs issue.

So, yes, I skipped question #3, but I think the others are covered.

All in the name of assistance!

This discussion is closed to new replies.