Post reply

Best way to create login for single database

  • 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

  • April 2, 2009 at 8:07 am

    #970717

    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:

  • April 2, 2009 at 8:26 am

    #970746

    Just map the user.:-P

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

  • April 2, 2009 at 9:37 am

    #970840

    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.

  • April 2, 2009 at 9:58 am

    #970868

    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.

  • April 2, 2009 at 11:25 am

    #970950

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

    Thanks for the info, that helped a lot.

  • April 2, 2009 at 11:38 am

    #970956

    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?

  • April 2, 2009 at 11:44 am

    #970964

    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. 🙂

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

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