Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Write-securing a table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

WindChaser
Posting Yak Master

225 Posts

Posted - 04/08/2005 :  13:57:16  Show Profile

I have a table which contains sensitive data such as licensing information. The SA password is not known by the users. I would like to make sure that a user which gains access to the database using any query tool using Windows NT authentication cannot write to the table. From EM, I have tried to limit the permissions for the Public user to reading only, but it doesn't work. What do I need to do to alter the table, using T-SQL, to get some writing security in there?

Thanks folks!

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 04/08/2005 :  14:07:25  Show Profile
I think anyone atleast need permission to write or update to the table (or atleast part of db_datawriter database role) or UPDATE permission on that particular table?

Edited by - cshah1 on 04/08/2005 14:09:40
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 04/08/2005 :  14:16:08  Show Profile
The table is usually only accessed by an application which uses a user with a SQL Server authentication. Only the application should be able to write to the table.
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 04/08/2005 :  22:40:51  Show Profile
The table is usually only accessed by an application which uses a user with a SQL Server authentication. Only the application should be able to write to the table.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 04/09/2005 :  03:48:13  Show Profile
You could look into using an "application role" to access the database from your application (See BOL for details) and then make sure that write access is only granted to that role.

If instead you grant rights to a user or a group so that they use the application, this won't prevent them from accessing the data outside the application.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 04/09/2005 :  16:11:01  Show Profile
1. But with an application role, only the application can access, right? I need to be able to access for maintenance purposes as well. Also, I'm obviously not doing it correctly because, following your suggestion of reading BOL, I put an application role in there and I can still access the data from an external tool.

2. But isn't the problem really that the MSDE installer sets a Login that has NT authentication by default (BUILTIN\Administrators). I figure that if I remove it, I'm removing the problem. Is there a straightforward method of removing this login?
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 04/10/2005 :  13:35:21  Show Profile
1) Yes, but from your problem statement, I thought that that is exactly what you wanted ("I would like to make sure that a user which gains access to the database using any query tool using Windows NT authentication cannot write to the table.")

If people are able to access this table it's only because somewhere in their permissions list, they have permission. (So much for my glaring grasp of the obvious ;-)

Remember that permissions are cumulative. The permissions granted to the user reflect permissions granted to the user themself and all rights granted to every group, server role and database role they are members of.

The System Administrator, clearly, has the ultimate access rights. The BUILTIN\Administrators should be a memeber of the System Administrator account and thus would have the permissions.

2) If the system is not allowing you to delete this login it could be because it is the only System Administrator account. Deleting this would be bad....very, very bad.

If you don't understand what I'm saying, step away from the computer and go hire a DBA.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 04/10/2005 :  14:15:25  Show Profile

Thanks for the chuckle, Bustaz Kool, 'cause the fact of the matter is that in the security aspects of SQL Server, I'm obviously flying by the seat of my pants.

However, I think that I might have found a remedy to my situation. I've done a sp_denylogin 'BUILTIN\Administrators' and nothing but my two SQL Authenticated Logins of my application and mine (for maintenance) now have access to anything. Radical? Possibly but, hey, it seems to meet the objective. If I try to access with Windows NT Authentication from Enterprise Manager or other tools, I'm locked out solid. Of course, I recognized that there may be some downsides that I'm not aware of in doing this. Any critiques would be welcomed.

Thanks folks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000