Post reply

To configure the user name and login name for a database

  • January 24, 2013 at 10:05 pm

    #288448

    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

  • January 24, 2013 at 11:49 pm

    #1580832

    arthi.anan (1/24/2013)

    So when i login with 'empdbo', i'm able to access 'emptest' db. This should not be allowed.

    'empdbo' will be sysadmin thats why it can access anything in that sql instance

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • January 25, 2013 at 6:59 am

    #1581017

    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?

    When you setup empdbo did you add the Login to the sysadmin Fixed Server Role? If so then you would want to remove it from that Role using sys.sp_dropsrvrolemember.

    Who are the Database Owners for emp and emptest?

    SELECT name AS database_name,

    SUSER_SNAME(owner_sid) AS owner_name

    FROM sys.databases

    WHERE name IN (N'emp', N'emptest');

    If you only want a Login to be able to gain access to a specific database they do not need to be a member of any Fixed Server Roles except public. Then the Login must be mapped to a Database User in the database you want them to have access and that Database User can be granted the specific permissions you want them to have in that database. That will limit their actions only to that specific database and critical server activities that all Logins have.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • January 25, 2013 at 7:06 am

    #1581020

    As Bhunesh mentioned that users will have system admin rights.

    if you want a particular login to have full access on particular db give that user\login db_owner rights.

  • January 30, 2013 at 2:56 am

    #1582532

    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

  • January 30, 2013 at 3:04 am

    #1582536

    If the user needs to see the definitions of the objects, grant them the view definition permission.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 30, 2013 at 3:08 am

    #1582537

    How can i give view permission?

  • January 30, 2013 at 3:13 am

    #1582538

    You might want to do some reading on SQL's permissions...

    GRANT VIEW DEFINITION TO <database user>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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