| Author |
 Topic  |
|
|
arthiasha
Starting Member
India
40 Posts |
 Posted - 01/25/2013 : 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 help
thanks in advance |
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 01/25/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 01/26/2013 : 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
USA
8 Posts |
Posted - 01/26/2013 : 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 db
2) Expand the DB emptest -> expand Security-> delete the user empdbo
With 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
India
40 Posts |
Posted - 01/30/2013 : 04:57:20
|
Thanks all...
I managed to do with the same. I have logged in as emptestuser
Now 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
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 01/30/2013 : 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 mapping
1) 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>;
GO
SELECT *
FROM fn_my_permissions('<database_name>', 'DATABASE');
GO
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
| |
Topic |
|
To configure the logins and users for a database
