locked
SQL Server 2012 RRS feed

  • Question

  • Dear Team,

    As discussed over phone, we have created one user in server named IPAADMIN.

    The same user can access server and we are able to log in in server.

    Now
    we have created 1 database LMTSTGEN and we would like to create schema
    and assign rights to IPAADMIN user. When we try to select user while
    making schema, user is not appearing in user list. Once that works then
    tables will be created with same user in database. There might be
    something in security to be authorised for same user but we are not able
    to trace the same.


    We are not able to find solution for the same.

    Kindly help us solving the same ASAP.
    • Moved by Kalman Toth Thursday, November 20, 2014 2:32 PM Not database design
    Thursday, November 20, 2014 6:42 AM

Answers

  • Hi,

    Have you created login for IPAADMIN  and mapped that login in LMTSTGEN database.

    Use below script to create login & map that user to a database and then assign database role.

    CREATE LOGIN IPAADMIN WITH Password = 'IPAADMIN' -- change password u like to assign
    GO
    USE LMTSTGEN
    Go
    CREATE USER IPAADMIN FROM LOGIN  IPAADMIN 
    GO
    sp_addrolemember 'db_owner' , IPAADMIN -- change role name.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, November 20, 2014 7:22 AM
  • In addition

    http://technet.microsoft.com/en-us/library/aa258256(v=sql.80).aspx

    If the user is not a member of sysadmin server role so and if you issue create  table you will see 

    username.Tablename to SELECT  from the table


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 20, 2014 9:17 AM

All replies

  • Hi,

    Have you created login for IPAADMIN  and mapped that login in LMTSTGEN database.

    Use below script to create login & map that user to a database and then assign database role.

    CREATE LOGIN IPAADMIN WITH Password = 'IPAADMIN' -- change password u like to assign
    GO
    USE LMTSTGEN
    Go
    CREATE USER IPAADMIN FROM LOGIN  IPAADMIN 
    GO
    sp_addrolemember 'db_owner' , IPAADMIN -- change role name.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, November 20, 2014 7:22 AM
  • In addition

    http://technet.microsoft.com/en-us/library/aa258256(v=sql.80).aspx

    If the user is not a member of sysadmin server role so and if you issue create  table you will see 

    username.Tablename to SELECT  from the table


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 20, 2014 9:17 AM