SQL Server Login User Setup

Hamish · 5 Dec 2008 at 17:18

Hi guys, I'm trying to serup a DB user which is more restrictive than the sa we currently use...

We have a SQL server database with lots of databases, what I want to do is create a user which has the same rights as sa but which only has access to one database, It would also be nice if it couldn't see any of the other databases.

Any ideas on how to achieve this?

Cheers
Hamish

teaboy5 · 5 Dec 2008 at 23:52

hmm, its been a while. But create a user and make it the db owner of the database you want.

paul_h_amiga · 6 Dec 2008 at 10:27

There are also other permissions you can set within the database if you want to restrict the user further.

If you want the user to only be able to read the database, select the datareader role within the database itself. hell you can restrict them to certain tables if you so wish as well, the better way to manage this is to create a database role with the relevant permissions.

What are you hoping for them to do with the DB?

Hamish · 8 Dec 2008 at 14:25

Basically the setup is that we have lots of instances of software in different DBs on our server. If a client gets access to an SQL prompt they can switch database using "use otherDb go" and print out data from another client's DB which is obviously a problem.

I have tried setting the User Mapping of the db to the User as dbo and not mapping any other databases, then in Securables I have ticked Deny on the "View any database" permission, this works in that they can only see the one database, but then they have no permission to run queries on it for some reason....

Code:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'table', database 'database', schema 'dbo'.

Hamish

paul_h_amiga · 16 Dec 2008 at 15:14

Don't deny any permissions, just map them to whichever database you want as by default the public server level does not allow you to view any database data.

The datareader role will allow select queries to be run on all tables. If you need to, you can only allow them to run select queries on certain tables themselves. It just depends.