Re: Two DB Owners
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/11/03
- Next message: Sorcha NiGheannain: "SQL Server Login"
- Previous message: Joshua A. Booker: "Re: Two DB Owners"
- In reply to: Joshua A. Booker: "Re: Two DB Owners"
- Next in thread: Steve Thompson: "Re: Two DB Owners"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 10 Jun 2003 20:47:56 -0500
It doesn't usually make much sense to add sysadmin role members to
database roles because sysadmin role members are aliased to dbo and have
full permissions in all databases. However, I know nothing about Great
Plains so I assume that this is needed for specialized application
security. In this case, you can try:
USE Dynamics
sp_grantdbaccess 'BUILTIN\Administrators'
sp_addrolemember 'DYNGRP', 'BUILTIN\Administrators'
sp_addrolemember 'BFGROUP', 'BUILTIN\Administrators'
USE DAVCatalog
sp_grantdbaccess 'BUILTIN\Administrators'
sp_addrolemember 'DYNGRP', 'BUILTIN\Administrators'
sp_addrolemember 'BFGROUP', 'BUILTIN\Administrators'
Note that the user will still be known as the 'dbo' user as long as the
user is a sysadmin role member.
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Joshua A. Booker" <joshuaabookerhot@mail.com> wrote in message news:eutfFX7LDHA.3392@tk2msftngp13.phx.gbl... > Hi Again, > > My appoligies if I'm not being clear. Here are some more details. > > I am setting up Great Plains eEnterprise and Business Portal on a > development server. I need to add the login 'BUILTIN\Administrators' to the > 'DYNGRP' and 'BFGROUP' roles of the Dynamics and DAVCatalog databases. When > I attempt to change anything in the login properties for > 'BUILTIN\Administrators', I get the previously mensioned error ('15405 can't > use reserved user 'dbo'). I'm assuming this is because the login is set to > user 'dbo' for all databases. When I remove and re-add the login it gives > me the previously mensioned error ('It has been detected that this login has > permissions in specific databases...') Then it sets the user to dbo for all > databases. Can this login be set to itself as the user instead of the user > 'dbo'? Or how else can I add this login to these roles? > > TIA, > Josh > > >"Bill Cheng"" <billchng@online.microsoft.com> wrote in message > news:xEh5gH1LDHA.1944@cpmsftngxa06.phx.gbl... > > Hi Joshua, > > > > I am not sure what effect you want to achieve. However, if you want to > > impede Microsoft Windows NT system administrators from having system > > administrator (sa) privileges in SQL Server, you may check the following > > article. > > 263712 INF: How to Impede Windows NT Administrators from Administering a > > http://support.microsoft.com/?id=263712 > > > > 295034 FIX: Microsoft Search Service May Cause 100% CPU Usage if > > http://support.microsoft.com/?id=295034 > > > > > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > > Regards, > > > > Bill Cheng > > Microsoft Support Engineer > > -------------------- > > | From: "Joshua A. Booker" <joshuaabookerhot@mail.com> > > | References: <umBQacrLDHA.2052@TK2MSFTNGP11.phx.gbl> > > <uAwLS8rLDHA.3144@tk2msftngp13.phx.gbl> > > | Subject: Re: Two DB Owners > > | Date: Mon, 9 Jun 2003 15:55:47 -0400 > > | Lines: 47 > > | X-Priority: 3 > > | X-MSMail-Priority: Normal > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 > > | X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 > > | Message-ID: <#oMqPHsLDHA.1960@TK2MSFTNGP11.phx.gbl> > > | Newsgroups: microsoft.public.sqlserver.security > > | NNTP-Posting-Host: me-rockland-qs-19.mint.adelphia.net 216.227.148.19 > > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.security:13857 > > | X-Tomcat-NG: microsoft.public.sqlserver.security > > | > > | Steve, > > | > > | I need to add the BUILTIN\Administrators login to certain roles in a > > | database. Because it's set to use the 'dbo' user in this database, I > get > > | the message '15405 can't use reserved user 'dbo' when I try to permit > the > > | role. For this reason, I'd like to remove all permissions from the > admins > > | group so it will not use 'dbo' user. When I remove the login it doesn't > > | remove the permissions that point to user 'dbo' for this login. How do > I > > | permit the login to use a role if the login points to the reserved user > > | 'dbo'? > > | > > | TIA, > > | Josh > > | > > | > > | "Steve Thompson" <SteveThompson@nomail.please> wrote in message > > | news:uAwLS8rLDHA.3144@tk2msftngp13.phx.gbl... > > | > "Joshua A. Booker" <joshuaabookerhot@mail.com> wrote in message > > | > news:umBQacrLDHA.2052@TK2MSFTNGP11.phx.gbl... > > | > > I have two dbowners in each database. I would like sa to be the > only > > db > > | > > owner, but the 'BUILTIN\Administrators' login has permiaaions as > user > > | > 'dbo' > > | > > for every database. I have dropped the 'BUILTIN\Administrators' > > login, > > | > and > > | > > run the sp_change_dbowner stored procedure, but somewhere > permissions > > | > > remain for the Administrators group. When I re-add the login, it > says > > | 'It > > | > > has been detected that this login has permissions in specific > > | > databases...' > > | > > Then it gives the login permissions as 'dbo' to each database. How > > do I > > | > > drop all permissions for 'BUILTIN\Administrators'? > > | > > > | > It's not necessary to drop all permissions for the > > | 'BUILTIN\Administrators' > > | > group. Once you delete that group, you effectively prevent anyone who > > has > > | > Administrative rights on your server from having sa equivalence on > your > > | SQL > > | > Server. > > | > > > | > As you've discovered, you can add the 'BUILTIN\Administrators' group > > back > > | > in, that's by design. > > | > > > | > Steve > > | > > > | > > > | > > | > > | > > > >
- Next message: Sorcha NiGheannain: "SQL Server Login"
- Previous message: Joshua A. Booker: "Re: Two DB Owners"
- In reply to: Joshua A. Booker: "Re: Two DB Owners"
- Next in thread: Steve Thompson: "Re: Two DB Owners"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
