SQL2KSP4 sp_addalias and db_accessadmin

From: Yoshihiro Kawabata (yoshihiro_at_kawabata.com)
Date: 05/09/05

  • Next message: Tom: "SP4 upgrade issue" 
    Date: Tue, 10 May 2005 03:48:58 +0900

    Hello,

    About SQL Server 2000 Service Pack 4,
    Below fix point,

    http://support.microsoft.com/kb/826822
    "FIX: A Member of the db_accessadmin Fixed Database Role Can Create an Alias
    for the dbo Special User"

    After updated, db_accessadmin login cannot do sp_addalias any more,
    even if not 'dbo' alias.

     Ex: sp_addalias 'David', 'Gim'

    New sp_addalias seem to check this by
      DBCC TRACESTATUS('no_output', 4650) with NO_INFOMSGS

    but, this dbcc always @@ROWCOUNT = 0 in any login,
    even this login is member 'sysadmin', 'dbo', 'db_accessadmin' or not.

    My Test Environment:
      OS: Windows XP Pro Japanese Edition
      DB: SQL Server 2000 Std Edition SP4 Japanese

    Conclusion:
      After SP4, Cannot db_accessadmin role use sp_addalias ?

    Regards,

    Yoshihiro Kawabata
    MVP for SQL Server.
    PASSJ board.
    INETA Japan lead.

  • Next message: Tom: "SP4 upgrade issue"

    Relevant Pages

    • Re: DBO Best Practices
      ... lines of always owner qualifying objects... ... someone who are just "a" dbo. ... > doesn't explicitly reference the object's owner (because SQL Server will ... The startup account defines the ...
      (microsoft.public.sqlserver.security)
    • Re: DBO Best Practices
      ... If you let DBO own databases or objects then regular ... Please note that the DBO user is identified by the SQL Server process ... the SQL Server startup account (for the ... SQL Server process) controls SQL Server's ability to work with NT's objects ...
      (microsoft.public.sqlserver.security)
    • Re: How to retrieve stored procedure error code in C#?
      ... Mark and William - thx for the replies, as they pointed me in the right ... > All Sql Server stored procedures have a return value parameter, ... > Please do not send email directly to this alias. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Merge Replication using ip address to distributor/publisher
      ... is no enterprize manager or client network utility. ... I also read that alias to ip address can be programmed in subscriber ... register the Publisher in Client Network Utility." ... Looking for a SQL Server replication book? ...
      (microsoft.public.sqlserver.replication)
    • change a named instance to default instance
      ... Produced By Microsoft MimeOLE V6.00.2800.1441 ... even though there is only one SQL Server ... | Is there anyway I can make my named instance behave like the default? ... Configure an Alias by Using TCP/IP Sockets ...
      (microsoft.public.sqlserver.connect)
    • Quantcast