Solved

creating a user for ms sql 2000

Posted on 2010-08-15
13
452 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]
Comment Utility
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
Comment Utility
ITS NOT POSSIBLE?

i find that hard to believe
0
 
LVL 4

Expert Comment

by:Audhi203
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:RakeshBhandari
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
>>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you very much!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

9 Experts available now in Live!

Get 1:1 Help Now