Re: Admin role lost - inability to administer database

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/03/04

  • Next message: Hari: "Re: Restrict Logins" 
    Date: Mon, 3 May 2004 09:17:03 +0530

    Hi,

    Are you trying to do this with a user with "DB_OWNER" role. In this case
    only the database which is assigned this role will only have the permission.

    If you have to perform administrative task on all databases then either
    register using "SA" user or a Login with "Sys Admin" server fixed role.

    How to identify the role assigned:

    1. Login to Query Analyser as "SA"
    2. Execute the procedure sp_helplogins <login name>

    Note:

    Automatically the permissions will never be lost / revoked in SQL server.

    Thanks
    Hari
    MCDBA

    "John Russell" <anonymous@discussions.microsoft.com> wrote in message
    news:46C5D23D-730B-4820-97B6-F6A710E73C81@microsoft.com...
    > When I open SQL Server Enterprise Administrator to create or manipulate
    databases errors are thrown at me for not having appropriate permissions.
    However, on one database I am able to modify its contents, tables and so
    forth.
    >
    > How have I lost access? What can I do to fix it?

  • Next message: Hari: "Re: Restrict Logins"

    Relevant Pages

    • ADP, Application Role, and objects
      ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
      (microsoft.public.access.adp.sqlserver)
    • Re: Complete Neophyte Question(s)
      ... it's impossible to remove a login from the ... I'm not sure if that's how the default Northwind database ... Delete, Insert, References, Select and Update permissions on dbo.Orders. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (microsoft.public.sqlserver.security)
    • Re: System Administrator Implied Permissions
      ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
      (microsoft.public.sqlserver.security)
    • Re: No db access after publishing web site
      ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Logging in irrespective of database access
      ... Sysadmin role members have full permissions, ... SQL Server MVP ... > My problem is that in the Login section of Enterprise Manger I have to> specify that each login has the System Admin ticked under Server Roles> because in SQL 2000 there doesn't seem to a Grant All permissions option> like there used to be in SQL 6.5. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
      (microsoft.public.sqlserver.server)