Solved

creating a user for ms sql 2000

Posted on 2010-08-15
13
464 Views
Last Modified: 2012-05-10
how do i create a user in ms sql server 2000 who only has SELECT & INSERT access for just 1 table which is in a particular database

note that i don't even want to user to see the database schema... other tables, other databases, etc

this has to be done in ms sql server 2000...so please guide
0
Comment
Question by:RakeshBhandari
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33443656
short answer: you cannot do that.

what you can do is to create a dedicated database, and with views to the relevant tables in the actual database.
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33443680
ITS NOT POSSIBLE?

i find that hard to believe
0
 
LVL 4

Expert Comment

by:Audhi203
ID: 33443712
Yes its not possible, this is similar to read-write. DDL, DML, DCL. You cant just give Insert privelege, leaving Delete, Update, etc aside...
0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33443720
ok... lets say i want to give all DML statements' permission : insert, update, delete, select

is that possible? how?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33445383
to clarify:
>how do i create a user in ms sql server 2000 who only has SELECT & INSERT access for just 1 table which is in a particular database

this is perfectly possible, so that any select/insert/update/delete to other tables etc are denied.

>note that i don't even want to user to see the database schema... other tables, other databases, etc
while you can deny with sql 2005 the view of other databases, you cannot deny the table layout of the database in question.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33556205
attention required

tell me how i give a user permission to do insert, update, delete, select ONLY on table in a particular database...not allowing him to see the other tables/views/procedures/etc on the same database and not allowing him to see even the other databases
0
 
LVL 77

Expert Comment

by:arnold
ID: 33589219
Use enterprise manager. Connect to the server with sa/admin account.
Drill down to the database/security properties for the user and see the dataread/datawrite.
You might be able to limit the user to query a single table, but I am not sure

I do not have access to the older version right now. Will check if needed.

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 33593537
>>> You cant just give Insert privelege, leaving Delete, Update, etc aside...

This is patently false.  You certainly can provide a user with the ability to just SELECT and INSERT on a single table, without allowing for UPDATE or DELETE access.  That privilege can be given to all tables across multiple databases, or limited to a single table in a single database, or even limited to just single columns in a single table.  AngelIII's original comments were correct in the context he explained: you cannot deny schema to a user with SELECT.  The user must necessarily be able to read the schema in order to read the table.

First and foremost, you should read through this article:

http://msdn.microsoft.com/en-us/library/aa140013%28office.10%29.aspx

Additionally, the following links are the "Books Online" for MSSQL 2000.  I highly recommend you read them, or at least use them as a basis for research on specific functionality.  This is essentially the "how-to" for everything MSSQL, and comes straight from the horse's mouth, as it were.

http://msdn.microsoft.com/en-us/library/dd631854%28v=SQL.10%29.aspx   (Books Online, top level)
http://msdn.microsoft.com/en-us/library/aa905156%28v=SQL.80%29.aspx   (Books Online->Administering SQL Server->Managing Security)

To do what you want, I will assume you have a database (called my_db) and a login (testuser) already created.  

Load up Enterprise Manager and make sure you are working with the server through an sa or sysadmin account.  Expand the tree in the left pane to reach your particular server.  If you are running with a basic, default installation, that should be [ Microsoft SQL Servers -> SQL Server Group -> (local) ].  Under your server, expand "Security" and select "Logins".  In the right pane, find and right-click the "testuser" object, and select "Properties".  On the last tab, "Database Access", find the database to which the user should have access, and check it.  The "User" column should automatically populate.  In the bottom half, there are specific roles you can assign to this permission.  The "public" role will be checked by default.  This is an important point, because you may have already provided permissions to the public role.  Click OK, and the server login should now be good to go.

At this point, if you log in with that user, and run "exec sp_databases;", you should see entries for "master","tempdb", and the database you just assigned to the user.  Since this is MSSQL2000, you *may* also see another database listed.  See the end of this post for more on this issue.

Now go back to the left pane, and expand "Databases".  Find the target database (my_db), expand it, and select "Users".  In the right pane, you should see the user (testuser) listed.  Right-click that user and select "Properties".  Again, you'll see that the user has been assigned the "public" role, and you cannot change this.  Here you can check what permissions the public role has by selecting the role in the list, and clicking "Properties" at the bottom.  Remember that any permissions you have granted to public will also be provided to testuser.  For now, I will assume you have provided no permissions to the public role.  Click the "Permissions..." button at the top of the properties window.

You should now see a list of all the tables in the database, including system tables.  To provide SELECT access to a table, simply check the "SELECT" column for that table by clicking it once.  You can limit this to individual columns with the "Columns" button at the bottom of the dialog.  If you have superfluous permissions provided to this user through the public role, you can *deny* those privileges (thus removing them) by clicking the box twice.  A red 'X' will appear instead of the green check, indicating you are explicitly denying those privileges.  Best practices recommend not using explicit denials unless you have no other recourse.  This also implies that, in best practice, you should avoid providing permissions to the public role since all users are part of it.

Once you are done assigning privileges, you can run "exec sp_tables;" to check your work.  Any table with SELECT privileges should be visible in the list.  This list will include the system tables.  UPDATE, INSERT, and DELETE privileges are not included in this list - only SELECT.

Also, a note on arnold's recommendation.  The db_datareader/db_datawriter roles are not what you want.  Those are database-wide read/write roles.  You could assign the db_datareader role, then go back and deny specific privileges to create the same effective permissions, but that is just making your life harder and goes against best practice (avoid denying permissions).  Ideally, you should create a role, assign permissions to that role, then assign the role to the user.  The steps I provided above skip the custom role for the sake of simplicity.

Finally, a note on MSSQL's GUI security.  As I mentioned above, the EM/SMSS application has had an information exposure I have known about since 2000.  A user logging in with the GUI can see other databases.  This is true even when they have no access - indeed, even when you have *denied* access.  I do not know how Microsoft classifies this behavior, but it is a bug/security flaw as far as I'm concerned.  This issue can be found even in 2008's SSMS, so I'm inclined to believe it is something in their architecture which requires this to happen.  You may also see other information exposures when running "exec sp_databases;" from the Query Analyzer or other clients.  This flaw does not actually provide access to the databases in question, but it does allow the user to know they are there.  As far as I know, there is no way to prevent this - your restricted user may still be able to see names for databases they have no access to.

0
 
LVL 2

Author Comment

by:RakeshBhandari
ID: 33602102
dear routinet

thank you very much for your wondeful explanation

i followed the steps exactly as you've mentioned them

for testing purposes, i denied a user [made his select permission for a particular table "x"] and yet, when i login using that user and fire the select query for the table i denied the permission, it gives me results of that query!

i logged in/logged out but to no avail

for the sake of completion, the user still has the public role assigned but removed permissio for select for a table

did i miss something?
0
 
LVL 12

Accepted Solution

by:
pastorchris earned 500 total points
ID: 33603216
Hi RakeshBhandari,
Here's a list of possiblilities on SQL 2000 for you to work with to suit your situation.

Please note that the following possible is possible with the user account:

1. Access to the database but no privileges to any other objects apart from SELECT and INSERT privileges on that one particular table you want.
2. The user will have access to the schema but will not be able to work with any other database objects in that scehma since he does not have access to them (with exception of that one table you've given him access to select from & insert into).
3. The user will not be able to browse and see database objects in the object browse using whichever SQL client of his preference.

With the above 3 met, here's what we cannot meet:
denying the user access to the schema. This does not come into play because the table the user is accessing is in a certain schema so the user needs to access the schema first in order to access the table in it.

Like I said earlier, we can give the user access to the schema then deny the user access to all objects in that schema apart from that one table you want him to SELECT from and INSERT into.

Here are the steps to perform (assuming the user is "myuser", database is "mydb", schema is "dbo" and table to insert/select is "mytable"):

1. Create a user using Enterprise Manager, make "mydb" his default database and give him access to "mydb" by clicking the databases tab and checking "mydb" for that user.
If the database has many objects in it then click "OK" and perform task 2 below otherwise (If the database does not have very many objects) then go to permissions and deny all privileges from all objects by placing the deny (red cross sign shows) privileges on all onjects except that one table.

2. Go to Query analyser, change your database to "mydb".
 a) Run the script I have attached on "mydb" which retrieves all previleges required to deny the user.
 b) Run output of this script on "mydb", which denies myuser privileges to necessary objects in dbo schema. The user still has access to the schema but nothing to do on it.
 c) Lastly, allow user to SELECT and INSERT into the table by executing the SQL line below on mydb:
     GRANT SELECT, INSERT on [dbo].[mytable] to [myuser];


RakeshBhandari, this is the best achievable on SQL 2000.

Log in using "myuser" account and see that you cannot browse the database objects, neither can you work with any other database objects apart from selecting from and inserting into "mytable".


Regards,
Chris Musasizi

. Senior DBA on Oracle, Microsoft SQL Server, Postgres, MySQL,  and IBM  Informix
. Developer on multiple platforms & languages
.  Enterprise Admin (Linux/Unix & Windows Server Systems)
. Storage  Admin
. Systems Engineer & Solutions Developer (Windows Server  Systems & Windows Solutions Developer)
. Database Architect

.  Certified Expert on Experts Exchange
. Article Author and script  writer on SQL Server Central (SQLserverCentral.com)
. Contributor on  stackoverflow.com and Microsoft TechNet.




select distinct 'deny all on [dbo].[' + rtrim(name)+'] to [myuser];' 

As cmd from sysobjects where xtype in ('S', 'U', 'V') union

select distinct 'deny all on [dbo].[' + rtrim(name)+'] to [myuser];' 

from sysobjects where xtype in ('X','P', 'FN');

Open in new window

0
 
LVL 2

Author Closing Comment

by:RakeshBhandari
ID: 33616007
thank you very much!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now