SQL Server Performance

The server principal is not able to access the database

Discussion in 'SQL Server 2005 General Developer Questions' started by ramkumar.mu, Feb 9, 2010.

  1. ramkumar.mu New Member

    I have a user that has showplan privileges but not able to access the databases. Pls help. List of steps i did...
    • Created a SQL Server Login by the name emer_ram
    • Created the user emer_ram
    • Created a Role Exec_Role
    • Granted Privileges to the Role
    • Assigned the Role to the User
    After doing the above, if i try to access a database - EMP, i am getting an errorThe server principal "emer_ram" is not able to access the database "EMP" under the current security context
    Script attached below (P.S - the script ran successfully)
    CREATE LOGIN EMER_RAM WITH PASSWORD = 'pass!123'
    CREATE USER EMER_RAM
    CREATE ROLE EXEC_Role
    GRANT VIEW DATABASE STATE TO EXEC_Role
    GRANT VIEW DEFINITION TO EXEC_Role
    GRANT SHOWPLAN TO EXEC_Role
    EXEC SP_ADDROLEMEMBER 'EXEC_Role','EMER_RAM'
    When i checked for the existence and priviliges for this id using sa, i saw that the id exists will all permissions mentioned in the script.select * from sys.database_principals
    select
    * from sys.database_permissions where grantee_principal_id = user_id('EXEC_Role')sp_helprolemember 'EXEC_Role'


  2. ramkumar.mu New Member

    Got the problem...
    While creating the user, i must have run "CREATE USER EMER_RAM FOR LOGIN EMER_RAM". Previously when i ran without the FOR, the user was created as an orphan

Share This Page