creating a user for ms sql 2000

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 please guide
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
RakeshBhandariAuthor Commented:

i find that hard to believe
Yes its not possible, this is similar to read-write. DDL, DML, DCL. You cant just give Insert privelege, leaving Delete, Update, etc aside...
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RakeshBhandariAuthor Commented:
ok... lets say i want to give all DML statements' permission : insert, update, delete, select

is that possible? how?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
RakeshBhandariAuthor Commented:
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
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.

Steve BinkCommented:
>>> 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:

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.   (Books Online, top level)   (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.

RakeshBhandariAuthor Commented:
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?
Chris MConsulting - Technology ServicesCommented:
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".

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 (
. Contributor on 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RakeshBhandariAuthor Commented:
thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.