To configure the logins and users for a database

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

To configure the logins and users for a database

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 help
thanks 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 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

40 Posts

Posted - 2013-01-30 : 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
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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources