Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
arthiasha
Starting Member
40 Posts |
Posted - 2013-01-25 : 00:00:34
|
Hi all,I created a database 'emptest' logging in using 'sa'. I have another database 'emp' in which i have used empdbo as login name.So when i login with 'empdbo', i'm able to access 'emptest' db. This should not be allowed.How do i set a user or login name only for a particular db and should not be allowed to access other db's?please helpthanks in advance |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-25 : 03:06:28
|
Are you an SA yourself? Or is empdbo classed as an SA?If you are then that over-rides (as far as I know) all other settings.Try logging in as non-SA and see if that works.You can only access a database you have permissions for. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-26 : 06:05:08
|
Could you doublecheck the privileges of the login empdbo . Check it's server level privileges , are there any extra ones enabled?Check the mappings of the empdbo login - is it mapped to just one database i.e empdbo?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
shan007
Starting Member
17 Posts |
Posted - 2013-01-26 : 17:15:03
|
This can be achieved in multiple ways, 1) Expand Security-> open the user empdbo(make sure he doesn't have sysadmin role, enable only public) -> select User Mappings -> Uncheck emptest db2) Expand the DB emptest -> expand Security-> delete the user empdboWith above two steps, empdbo can see other db's but he can access only to the db's mapped with, other db's cant. Hope it helps.==============================I'm here to learn new things everyday.. |
|
|
arthiasha
Starting Member
40 Posts |
Posted - 2013-01-30 : 04:57:20
|
Thanks all...I managed to do with the same. I have logged in as emptestuserNow for emptest db, in Security-logins I have the emptestuser created under this database.And the same emptestuser & sa logins are seen under Main Security-logins.But i could not view the tables or procedures.how could i view it?What is the problem here?please explain me |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-30 : 07:52:31
|
1) Confirm that the Login "emptestuser" is mapped to the database user 'emptestuser'. If so, procedd with step . If not create the mapping1) Look in the database list of users and check the user has relevant permissions. test by logging as the user - make sure you're using the correct db and run :USE <database_name>;GOSELECT * FROM fn_my_permissions('<database_name>', 'DATABASE'); GO Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|