| Author |
Topic |
|
Pickles
Starting Member
7 Posts |
 Posted - 2010-07-05 : 23:49:54
|
| Hi,I am having a MSSQL server with multiple databases. How can i ensure that when a user logins to the server, he/she can only see his/her own database instead of having access to all the databases in the server. Is that possible? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-06 : 01:40:11
|
| Don't give the login rights on the other databases. They'll be able to see the names, but not to use them at all.--Gail ShawSQL Server MVP |
 |
|
|
Pickles
Starting Member
7 Posts |
Posted - 2010-07-06 : 03:08:22
|
| Hi Gail,Where can I do this? database->security->users? Right click on users and click properties? What option do i need to change?Sorry for the noob questions ... i've never done any db server admin before.Thanks. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-06 : 03:54:22
|
| Where's the DBA?Server->security->loginsGet the properties of the login, go to user mapping, uncheck any databases that you don't want the user to be able to access. This will only work if the login is not a member of the sysadmin role (which noone but the DBA should be anyway)Bear in mind that the login will lose ALL permissions that they had in that DB. If you do this in error, you'll have a lot of work fixing it. Check the user's permissions before you remove their access from each DB.--Gail ShawSQL Server MVP |
|
|
|
Pickles
Starting Member
7 Posts |
Posted - 2010-07-08 : 00:46:07
|
| Hi Gail,Thanks for your response. Unfortunately, we don't have a dba so i'm on my own :)I have managed to play around with what you mentioned seems to work. However my problem is, i have a database user id e.g. abc. User abc is the owner, so when i try to create another server user of the same name, it says it alreay exists etc etc. The reason why i need to create the same user id is because my application uses it in the connection string to connect to the database. Any advice? |
|
|
|
Pickles
Starting Member
7 Posts |
Posted - 2010-07-08 : 03:14:16
|
| Hi Gail,I managed to get the security access on specific users to work. However, i have a new problem. When logging in as e.g testuser, i am only allowed to see the specified database, but i can actually change my own role to sysadmin. How do i block these users from having the rights to give themselves sysadmin role?Thanks. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-08 : 05:07:35
|
| Um, only a sysadmin can make someone sysadmin. How exactly did you manage that with a restricted user? (check exactly what permissions that testuser has)--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-08 : 05:09:43
|
quote:
Originally posted by Pickles
However my problem is, i have a database user id e.g. abc. User abc is the owner, so when i try to create another server user of the same name, it says it alreay exists etc etc. The reason why i need to create the same user id is because my application uses it in the connection string to connect to the database. Any advice?
Change the database's owner to sa, drop the login completely, recreate it with just the minimum permissions that you need.--Gail ShawSQL Server MVP |
|
|
|
Pickles
Starting Member
7 Posts |
Posted - 2010-07-21 : 06:18:46
|
| Ok understood .. thank you very much Gail. |
|
|
|
|
Some server administration questions