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

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