Best way to create login for single database

Question

Best way to create login for single database

TcW_1978

SSCrazy

Points: 2018
More actions
April 2, 2009 at 7:59 am

#132445

SQL Security Newbie's Question :
How can I create a login and restrict it to a single database?
Do I create the login in the Databases-Security or in the Database-DBName-Security?
Do I need to create a schema?
Thanks
TcW

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 1

Just go to security in server.Take logins.In that you can add user.Right click on the user,take properties.In the user mapping listed in it , you can map user to particullar databases:-)

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 2

Just map the user.:-P

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

TcW_1978 SSCrazy Points: 2018 More actions · Answer 3

That works but is it normal that when I connect with this user I still see the master and tempdb?

Is there a way to remove the user's access to the system db's?

Thanks.

sayfrend SSCommitted Points: 1733 More actions · Answer 4

It is because Guest Account is not diabled in master and tempdb.

Sql Server performs many actions internally and needs access to masterDB and tempdb.

So when a new user logs into SQL Server and then to database to which he has been given permission. Some quiries might need master DB access and some need access to Tempdb.

For Example: new user wanted to create a temporary table. The only place where temporary tables gets created is Tempdb.

if you want to tighten security, Just check what Privileges Guest A/c has got in MasterDb.

Remember Guest Account is default member of Public Role. So Check permissions to Public Role. Use below script for same.

Exec Sp_helprotect @username = 'public'

Analyze if Public role needs those permissions. And act accordingly.

DO NOT Disable Guest A/c from master and tempdb.

~IM.

TcW_1978 SSCrazy Points: 2018 More actions · Answer 5

I didn't know about the stored proc "Sp_helprotect".

Thanks for the info, that helped a lot.

Achtang SSCommitted Points: 1602 More actions · Answer 6

So basically you need a login to have a User access to the database?

How do you give access to the developers? or any other DBA's?

TcW_1978 SSCrazy Points: 2018 More actions · Answer 7

That's the thing, I'm not a DBA, I'm a developer and I use SQL everyday but the "security" aspect of SQL Server, that's new for me.

The server as been managed by developers and some things, like the server's security, have been left behind by lack of time and lack of knowledge. I'm trying to tighten security up and cut unnecessary access. So I read and ask questions. 🙂