our thread seems to have stopped. i'd appreciate it if you have the
time to continue here.
To recap here is my last question, any help appreciated,
Regards
Andrew
Hi Andrea
I understand what you mean about groups now (I hope). Unless I use a
group containing ONE user - the special user NTAuthority\Authenticated
users, then I can't get away from adding each user individually (unless
I user SQL Server authentication), even with groups. Is this correct?
I got a test user (lets call them somedomain\testuser) to use osql to
run the sql code you suggested. It ran fine, but it didn't return
"guest"! Instead it returned "dbo" which surprised me as the dbo of the
database on the server (where this MSDE database came from) is me, not
somedomain\testuser. I checked on the server and it clearly states that
the owner of the database is me. Maybe its because of how the database
is created in the first place on the users machine? Here is the code
that my installation routine uses to install the database:
/* If the abcdb database exists, then drop it */
IF EXISTS (SELECT *
FROM sysdatabases
WHERE name = 'abcdb')
DROP DATABASE abcdb
GO
/* Attach mdf and ldf to new database */
exec sp_attach_db @.dbname=N'abcdb',
@.filename1 = N'c:\abcsqldata\abcdb.MDF',
@.filename2 = N'c:\abcsqldata\abcdb.LDF'
Thanks
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1101991621.708649.228870@.c13g2000cwb.googlegr oups.com
this.[vbcol=seagreen]
but[vbcol=seagreen]
for[vbcol=seagreen]
a
> :D that's what I (wanted to) suggest... to maintain your WinNT logins
as
> groups (I know "MyApplicationUsers" or "TravellingUsers" do not
usually
> exists as you have to create that groups :D... I apologize for
beeing[vbcol=seagreen]
> cryptic
)return[vbcol=seagreen]
in
> try executing
> SET NOCOUNT ON
> SELECT CURRENT_USER AS [current user]
> which should report
> --<--
> current user
> --
> guest
> that's to say MyDomain\MyUser is mapped to "Guest" user (if you did
not
> removed it from your model database as I usually do) and will be
>member of
> the "Public" database role...
hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1102930491.966945.82510@.c13g2000cwb.googlegro ups.com
> Hi Andrea,
> our thread seems to have stopped. i'd appreciate it if you have the
> time to continue here.
> To recap here is my last question, any help appreciated,
I apologize... my wife wanted me for vacation :D
> Regards
> Andrew
> Hi Andrea
> I understand what you mean about groups now (I hope). Unless I use a
> group containing ONE user - the special user NTAuthority\Authenticated
> users, then I can't get away from adding each user individually
> (unless I user SQL Server authentication), even with groups. Is this
> correct?
you only need adding new WinNT user(s) to your (SQL Server login granted)
WinNT groups...
> I got a test user (lets call them somedomain\testuser) to use osql to
> run the sql code you suggested. It ran fine, but it didn't return
> "guest"! Instead it returned "dbo" which surprised me as the dbo of
> the database on the server (where this MSDE database came from) is
> me, not somedomain\testuser. I checked on the server and it clearly
> states that the owner of the database is me. Maybe its because of how
> the database is created in the first place on the users machine? Here
> is the code that my installation routine uses to install the database:
> /* If the abcdb database exists, then drop it */
> IF EXISTS (SELECT *
> FROM sysdatabases
> WHERE name = 'abcdb')
> DROP DATABASE abcdb
> GO
> /* Attach mdf and ldf to new database */
> exec sp_attach_db @.dbname=N'abcdb',
> @.filename1 = N'c:\abcsqldata\abcdb.MDF',
> @.filename2 = N'c:\abcsqldata\abcdb.LDF'
perhaps your somedomain\testuser is member of dbowner database rose and/or
part of the sysadmins server role...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
thanks for the response.
> perhaps your somedomain\testuser is member of dbowner database rose
and/or
> part of the sysadmins server role...
No, he's not a member of either.
However on the server, If I select the server role sysadmin, I see that
BUILTIN\Administrators is a member. somedomain\testuser has admin
rights on his local PC, so is this why he becomes the owner of the MSDE
database when it is installed locally?
Regards
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1102930491.966945.82510@.c13g2000cwb.googlegro ups.com
> I apologize... my wife wanted me for vacation :D
a[vbcol=seagreen]
NTAuthority\Authenticated[vbcol=seagreen]
this
> you only need adding new WinNT user(s) to your (SQL Server login
granted)[vbcol=seagreen]
> WinNT groups...
to[vbcol=seagreen]
how[vbcol=seagreen]
Here[vbcol=seagreen]
database:
> perhaps your somedomain\testuser is member of dbowner database rose
and/or
> part of the sysadmins server role...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a
visual
> interface)
> -- remove DMO to reply
|||hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1103021537.911855.37930@.c13g2000cwb.googlegro ups.com
> Andrea
> thanks for the response.
>
> No, he's not a member of either.
> However on the server, If I select the server role sysadmin, I see
> that BUILTIN\Administrators is a member. somedomain\testuser has admin
> rights on his local PC, so is this why he becomes the owner of the
> MSDE database when it is installed locally?
if somedomain\testuser is member of BUILTIN\Administrators the he/she is for
sure member of sysadmins (local) server role..
he/she will not become owner of the attached database, as the owner will
remain the original one, but he/she will be able to create new databases as
long as attach detached ones...
usually it's not a good idea to log on as (local) admin on client pc
workstations too :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea
hmm, I wonder why somedomain\testuser becomes the dbo then? This means
the owner of the database doesn't it? When I installed the database on
my test (local) client, and ran the osql code, it showed me as the dbo.
However when my colleague did the same on his test local client, it
showed him as the dbo!
Regards
Andrew
Andrea Montanari wrote:[vbcol=seagreen]
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1103021537.911855.37930@.c13g2000cwb.googlegro ups.com
rose[vbcol=seagreen]
admin
> if somedomain\testuser is member of BUILTIN\Administrators the he/she
is for
> sure member of sysadmins (local) server role..
> he/she will not become owner of the attached database, as the owner
will
> remain the original one, but he/she will be able to create new
databases as
> long as attach detached ones...
> usually it's not a good idea to log on as (local) admin on client pc
> workstations too :D
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a
visual
> interface)
> -- remove DMO to reply
|||hi Andrew,
"CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
news:1103106240.866306.135020@.f14g2000cwb.googlegr oups.com
> Hi Andrea
> hmm, I wonder why somedomain\testuser becomes the dbo then? This means
> the owner of the database doesn't it? When I installed the database on
> my test (local) client, and ran the osql code, it showed me as the
> dbo. However when my colleague did the same on his test local client,
> it showed him as the dbo!
if somedomain\testuser is (locally) member of the Builtin\Administrators
role, he is member of the local (MSDE) server sysadmin server role... he
will be no be member of the sysadmin server role on the main SQL Server
instance residing on the central server..
as (local) sysadmin, he will be member and will be listed as dbowner of
every database residing on the MSDE instance even if the corresponding login
has not be granted access/privileges to them as, as sysadmin, he is really a
God (ok, a little and strange kind of god :D) on the local MSDE instance...
your colleague, as somedomain\some_other_testuser , on his pc's MSDE
instance, as member of his Builtin\Administrators role, will be the same on
his MSDE instance, while perhaps being a standard login (member of
somedomain\somegroup) on your MSDE instance and on the central SQL Server
instance..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
ok that's great! Thanks for all your help and patience.
Bye for now.
Andrew.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:32b14vF3k7sa0U1@.individual.net...
> hi Andrew,
> "CodeMonkey" <agaskelluk@.yahoo.com> ha scritto nel messaggio
> news:1103106240.866306.135020@.f14g2000cwb.googlegr oups.com
> if somedomain\testuser is (locally) member of the Builtin\Administrators
> role, he is member of the local (MSDE) server sysadmin server role... he
> will be no be member of the sysadmin server role on the main SQL Server
> instance residing on the central server..
> as (local) sysadmin, he will be member and will be listed as dbowner of
> every database residing on the MSDE instance even if the corresponding
login
> has not be granted access/privileges to them as, as sysadmin, he is really
a
> God (ok, a little and strange kind of god :D) on the local MSDE
instance...
> your colleague, as somedomain\some_other_testuser , on his pc's MSDE
> instance, as member of his Builtin\Administrators role, will be the same
on
> his MSDE instance, while perhaps being a standard login (member of
> somedomain\somegroup) on your MSDE instance and on the central SQL Server
> instance..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
No comments:
Post a Comment