SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 To configure the logins and users for a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 01/25/2013 :  00:00:34  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

256 Posts

Posted - 01/25/2013 :  03:06:28  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1911 Posts

Posted - 01/26/2013 :  06:05:08  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 01/26/2013 :  17:15:03  Show Profile  Reply with Quote
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..
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 01/30/2013 :  04:57:20  Show Profile  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1911 Posts

Posted - 01/30/2013 :  07:52:31  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000