Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisRCheck out sp_change_users_login in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>
Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts
Friday, March 9, 2012
Login/Account Lock
I have a scenario in my application that I need to know whether the account
is locked or not before the password is reset. If the account is locked the
app should pop up a msg.
how to programmatically determine that the account is locked or not in sql
server 2005.
Some thing like @.@.islocked or @.@.lock_status or any system SP
Can any one help me as this is an urgent case needed immediate response.
Thanks in advance.
Best regards,
venkateshVenkatesh (Venkatesh@.discussions.microsoft.com) writes:
> I have a scenario in my application that I need to know whether the
> account is locked or not before the password is reset. If the account is
> locked the app should pop up a msg.
> how to programmatically determine that the account is locked or not in sql
> server 2005.
> Some thing like @.@.islocked or @.@.lock_status or any system SP
> Can any one help me as this is an urgent case needed immediate response.
sys.server_principals.is_disabled seems like it could be what you are
asking for.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for the immediate response. But I think this will disable the login -
to disable the login we can very well use the below command
alter login [loginname] disable/enable
But what I want is to determine whether account is locked or not. When you
click the properties of a login in the status tab you would see the account
lock out check box. I want how programatically this can be determined
Correct me If I am wrong. In case if your code
sys.server_principals.is_disabled is used to lock the account could you give
me an example.
"Erland Sommarskog" wrote:
> Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> sys.server_principals.is_disabled seems like it could be what you are
> asking for.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||You can use select LOGINPROPERTY('test', N'IsLocked').
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> Thanks for the immediate response. But I think this will disable the
> login - to disable the login we can very well use the below command
> alter login [loginname] disable/enable
> But what I want is to determine whether account is locked or not. When
> you click the properties of a login in the status tab you would see the
> account lock out check box. I want how programatically this can be
> determined
> Correct me If I am wrong. In case if your code
> sys.server_principals.is_disabled is used to lock the account could you
> give me an example.
OK, I admit that I took a gamble on that one.
It seems that Mohit did a better job on reading the manual that you and I
did.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
is locked or not before the password is reset. If the account is locked the
app should pop up a msg.
how to programmatically determine that the account is locked or not in sql
server 2005.
Some thing like @.@.islocked or @.@.lock_status or any system SP
Can any one help me as this is an urgent case needed immediate response.
Thanks in advance.
Best regards,
venkateshVenkatesh (Venkatesh@.discussions.microsoft.com) writes:
> I have a scenario in my application that I need to know whether the
> account is locked or not before the password is reset. If the account is
> locked the app should pop up a msg.
> how to programmatically determine that the account is locked or not in sql
> server 2005.
> Some thing like @.@.islocked or @.@.lock_status or any system SP
> Can any one help me as this is an urgent case needed immediate response.
sys.server_principals.is_disabled seems like it could be what you are
asking for.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for the immediate response. But I think this will disable the login -
to disable the login we can very well use the below command
alter login [loginname] disable/enable
But what I want is to determine whether account is locked or not. When you
click the properties of a login in the status tab you would see the account
lock out check box. I want how programatically this can be determined
Correct me If I am wrong. In case if your code
sys.server_principals.is_disabled is used to lock the account could you give
me an example.
"Erland Sommarskog" wrote:
> Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> sys.server_principals.is_disabled seems like it could be what you are
> asking for.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||You can use select LOGINPROPERTY('test', N'IsLocked').
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005|||Venkatesh (Venkatesh@.discussions.microsoft.com) writes:
> Thanks for the immediate response. But I think this will disable the
> login - to disable the login we can very well use the below command
> alter login [loginname] disable/enable
> But what I want is to determine whether account is locked or not. When
> you click the properties of a login in the status tab you would see the
> account lock out check box. I want how programatically this can be
> determined
> Correct me If I am wrong. In case if your code
> sys.server_principals.is_disabled is used to lock the account could you
> give me an example.
OK, I admit that I took a gamble on that one.
It seems that Mohit did a better job on reading the manual that you and I
did.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 24, 2012
Login problem after DB restore
Don, we just went through the same scenario here at my
office. The problem when moving a database from one
server to another, is the SID doesn't match between the
master and user database. Refer to Microsofts Knowledge
Base Articles, 240872, 168001, 298897. We also had a
problem when we changed the IP address and name of the
server. If you need information on this, refer to
Http://ambrosoli.com/ambrosoli/how_..._a_sql_server_7
_0.htm
>--Original Message--
>SQL 7.0 using SQL/Windows authenication.
>A DB backup was restored from one server to another which
>had a new install of SQL 7.0.
>Problem:
>In EM/Security, if I try to add the user login 'xxx' with
>access to the restored DB, SQL says the login name
>already exists in the the DB. Yet, it doesn't exist, that
>I can see any where. If I try to access the DB from an
>ODBC connection using the login 'xxx' it denies access.
>I tried droplogin 'xxx', but that didn't help.
>Any ideas?
>Thanks,
>Don
>.
>Hi !
I had the same probs earlier with my databses and I followed the following s
teps to correct it may be you find them helpfull in your scenario as well.
1) Go to SQL Enterprise Manager Login through 'sa'.
2)Create Database ABC (The one you want to restore)
3)Go to Security Folder in SQL Server Enterprise Manager Select Logins and t
hen select user a.
4)After Select a(user name) ->Select Properties -> Select Database Access
5)You now see the list of all DB installed. Select abc Database and Check on
db_owner in permission.
6) Restore abc Database from another server provided to you.
7)Go to Query Analyzer Login as sa and select abc Database run the followin
g line as it is .
USE abc
GO
EXEC sp_change_users_login 'Update_One', 'a', 'a'
Go
Regards ,
Jayzee
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
office. The problem when moving a database from one
server to another, is the SID doesn't match between the
master and user database. Refer to Microsofts Knowledge
Base Articles, 240872, 168001, 298897. We also had a
problem when we changed the IP address and name of the
server. If you need information on this, refer to
Http://ambrosoli.com/ambrosoli/how_..._a_sql_server_7
_0.htm
>--Original Message--
>SQL 7.0 using SQL/Windows authenication.
>A DB backup was restored from one server to another which
>had a new install of SQL 7.0.
>Problem:
>In EM/Security, if I try to add the user login 'xxx' with
>access to the restored DB, SQL says the login name
>already exists in the the DB. Yet, it doesn't exist, that
>I can see any where. If I try to access the DB from an
>ODBC connection using the login 'xxx' it denies access.
>I tried droplogin 'xxx', but that didn't help.
>Any ideas?
>Thanks,
>Don
>.
>Hi !
I had the same probs earlier with my databses and I followed the following s
teps to correct it may be you find them helpfull in your scenario as well.
1) Go to SQL Enterprise Manager Login through 'sa'.
2)Create Database ABC (The one you want to restore)
3)Go to Security Folder in SQL Server Enterprise Manager Select Logins and t
hen select user a.
4)After Select a(user name) ->Select Properties -> Select Database Access
5)You now see the list of all DB installed. Select abc Database and Check on
db_owner in permission.
6) Restore abc Database from another server provided to you.
7)Go to Query Analyzer Login as sa and select abc Database run the followin
g line as it is .
USE abc
GO
EXEC sp_change_users_login 'Update_One', 'a', 'a'
Go
Regards ,
Jayzee
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
Subscribe to:
Posts (Atom)