Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label logins. Show all posts
Showing posts with label logins. Show all posts
Wednesday, March 21, 2012
Prompt for login credentials using Windows-NT Authentication
Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Prompt for login credentials using Windows-NT Authentication
Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick Tompson
It's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson
|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick Tompson
It's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson
|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql
Monday, February 20, 2012
Programatically Create User Login
I need to create new user logins that can only get data from a specific
database. I am using the "Create Login" command to do this. Right now, I a
m
working with SQL Server EXPRESS 2005
CREATE LOGIN TestLogin
WITH PASSWORD = 'password',
DEFAULT_DATABASE = TestDB,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
This works, as it does create the login. However, when I try to actually
connect with this new login, I receive an error that it can't open the
default database. If i connect as the SA, and go to the "User Mapping" for
the new login (in this case "TestLogin"), no databases are checked. If I
check this login's default database (in this case "TestDB"), then I am able
to re-connect as the new login ("TestLogin") and am able to get data from th
e
default database ("TestDB").
Can someone explain what I am missing to programatically create this login,
assign a default database and have it work?
Thank you
MATTthe next step is
use TestDB
go
create user TestLogin for TestLogin
go
you can login now using the TestLogin acct.
hth,
"MATT" <MATT@.discussions.microsoft.com> wrote in message
news:38F2CB3D-2F2A-4D92-A737-CA459C3A01A5@.microsoft.com...
>I need to create new user logins that can only get data from a specific
> database. I am using the "Create Login" command to do this. Right now, I
> am
> working with SQL Server EXPRESS 2005
> CREATE LOGIN TestLogin
> WITH PASSWORD = 'password',
> DEFAULT_DATABASE = TestDB,
> CHECK_EXPIRATION = OFF,
> CHECK_POLICY = OFF
> This works, as it does create the login. However, when I try to actually
> connect with this new login, I receive an error that it can't open the
> default database. If i connect as the SA, and go to the "User Mapping"
> for
> the new login (in this case "TestLogin"), no databases are checked. If I
> check this login's default database (in this case "TestDB"), then I am
> able
> to re-connect as the new login ("TestLogin") and am able to get data from
> the
> default database ("TestDB").
> Can someone explain what I am missing to programatically create this
> login,
> assign a default database and have it work?
> Thank you
> MATT
>
database. I am using the "Create Login" command to do this. Right now, I a
m
working with SQL Server EXPRESS 2005
CREATE LOGIN TestLogin
WITH PASSWORD = 'password',
DEFAULT_DATABASE = TestDB,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
This works, as it does create the login. However, when I try to actually
connect with this new login, I receive an error that it can't open the
default database. If i connect as the SA, and go to the "User Mapping" for
the new login (in this case "TestLogin"), no databases are checked. If I
check this login's default database (in this case "TestDB"), then I am able
to re-connect as the new login ("TestLogin") and am able to get data from th
e
default database ("TestDB").
Can someone explain what I am missing to programatically create this login,
assign a default database and have it work?
Thank you
MATTthe next step is
use TestDB
go
create user TestLogin for TestLogin
go
you can login now using the TestLogin acct.
hth,
"MATT" <MATT@.discussions.microsoft.com> wrote in message
news:38F2CB3D-2F2A-4D92-A737-CA459C3A01A5@.microsoft.com...
>I need to create new user logins that can only get data from a specific
> database. I am using the "Create Login" command to do this. Right now, I
> am
> working with SQL Server EXPRESS 2005
> CREATE LOGIN TestLogin
> WITH PASSWORD = 'password',
> DEFAULT_DATABASE = TestDB,
> CHECK_EXPIRATION = OFF,
> CHECK_POLICY = OFF
> This works, as it does create the login. However, when I try to actually
> connect with this new login, I receive an error that it can't open the
> default database. If i connect as the SA, and go to the "User Mapping"
> for
> the new login (in this case "TestLogin"), no databases are checked. If I
> check this login's default database (in this case "TestDB"), then I am
> able
> to re-connect as the new login ("TestLogin") and am able to get data from
> the
> default database ("TestDB").
> Can someone explain what I am missing to programatically create this
> login,
> assign a default database and have it work?
> Thank you
> MATT
>
Programatically Backup and Restore User Logins Problem...
I'm sure your all aware of the bug in SQL 2000 where if you take a backup an
d
then do a restore on a different database server, the users accounts are not
added to the server's own security (login) section.
The user accounts are though added properly to the databases "users" list
however. And yes I do need to use SQL Server authentication, instead of
windows authentication.
Does anybody know how to get around this' I wish to do it programatically
without enterprise manager.Well I wouldnpt call it a bug, remember that the users, which are specific t
o
the database, map to logins, which are server-based... So whn you restore a
database on a different server than where the backup was made, there is no
way for the softwae to know which login on the other server the dataabse use
r
should be mapped to... If you're lucky enough that the server Login list is
identical on the other server including Login IDs, (pure coincidence) it
actually does fix things up properly, but this is rare.
So you need to use a system-level Stored proc called sp_adduser, this can be
called programatiicaly.
"David Dolheguy" wrote:
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
and
> then do a restore on a different database server, the users accounts are n
ot
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it programaticall
y
> without enterprise manager.
>|||This is not a bug at all. Logins are at the Server level and Users are at
the DB level. When you backup and restore a db it knows nothing of the
server. These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL
Andrew J. Kelly SQL MVP
"David Dolheguy" <DavidDolheguy@.discussions.microsoft.com> wrote in message
news:5C877C2E-EE7D-4658-9E31-AF46F0060E18@.microsoft.com...
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
> and
> then do a restore on a different database server, the users accounts are
> not
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it
> programatically
> without enterprise manager.
>
d
then do a restore on a different database server, the users accounts are not
added to the server's own security (login) section.
The user accounts are though added properly to the databases "users" list
however. And yes I do need to use SQL Server authentication, instead of
windows authentication.
Does anybody know how to get around this' I wish to do it programatically
without enterprise manager.Well I wouldnpt call it a bug, remember that the users, which are specific t
o
the database, map to logins, which are server-based... So whn you restore a
database on a different server than where the backup was made, there is no
way for the softwae to know which login on the other server the dataabse use
r
should be mapped to... If you're lucky enough that the server Login list is
identical on the other server including Login IDs, (pure coincidence) it
actually does fix things up properly, but this is rare.
So you need to use a system-level Stored proc called sp_adduser, this can be
called programatiicaly.
"David Dolheguy" wrote:
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
and
> then do a restore on a different database server, the users accounts are n
ot
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it programaticall
y
> without enterprise manager.
>|||This is not a bug at all. Logins are at the Server level and Users are at
the DB level. When you backup and restore a db it knows nothing of the
server. These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL
Andrew J. Kelly SQL MVP
"David Dolheguy" <DavidDolheguy@.discussions.microsoft.com> wrote in message
news:5C877C2E-EE7D-4658-9E31-AF46F0060E18@.microsoft.com...
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
> and
> then do a restore on a different database server, the users accounts are
> not
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it
> programatically
> without enterprise manager.
>
Subscribe to:
Posts (Atom)