What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/...y/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.comsql
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Sunday, March 25, 2012
ALTER USER WITH LOGIN
What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
--
Ekrem Ã?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
--
Ekrem Önsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
--
Ekrem Ã?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
--
Ekrem Önsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com
ALTER USER WITH LOGIN
What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?
It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?
|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com
|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
Ekrem nsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com
|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken
|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com
an existing login in SS2005?
It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?
|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com
|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
Ekrem nsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com
|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken
|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Tuesday, March 20, 2012
Alter table permission to dbo
I have the following requirement
I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,update on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.
I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner
Is there a way to achieve this ?
Thanks
M A Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0309260634.3791a935@.posting.google.c om...
> I have the following requirement
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,update on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
> Is there a way to achieve this ?
> Thanks
> M A Srinivas
You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.
Simon
Thursday, March 8, 2012
alter login problem with sp_executesql
Hi,
I've got a problem with new SQL Server 2005 T-SQL command alter login:
> alter login [LoginName] with password = 'NewPassword' works fine.
> exec sp_executesql N'alter login [LoginName] with password = @.P1', N'@.P1 nvarc
har(128)', N'NewPassword' does not work.
I get this error while executing an ADO 2.8 ADODB.Command with Parameters.
The .CommandText = "alter login [" & Login & "] with password = ?" seems
to
be executed as exec sp_executesql ... (see Profiler).
sp_password works fine, but BOL says not to use sp_password in new
applications.
Can anybody help, please?ALTER LOGIN doesn't accept variables so you can't parameterize the
statement. Is your intention to allow the current user to change their
password? In that case, you can specify the old/new password in the
connection string using the SQLNCLI provider:
"Provider=SQLNCLI;Data Source=MyServer;User ID=MyLogin;Old
Password=oldpassword;Password=newpasswor
d"
Hope this helps.
Dan Guzman
SQL Server MVP
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:418664FA-F9B0-4C82-8A17-A4D786C741B1@.microsoft.com...
> Hi,
> I've got a problem with new SQL Server 2005 T-SQL command alter login:
> I get this error while executing an ADO 2.8 ADODB.Command with Parameters.
> The .CommandText = "alter login [" & Login & "] with password = ?" see
ms
> to
> be executed as exec sp_executesql ... (see Profiler).
> sp_password works fine, but BOL says not to use sp_password in new
> applications.
> Can anybody help, please?
I've got a problem with new SQL Server 2005 T-SQL command alter login:
> alter login [LoginName] with password = 'NewPassword' works fine.
> exec sp_executesql N'alter login [LoginName] with password = @.P1', N'@.P1 nvarc
har(128)', N'NewPassword' does not work.
I get this error while executing an ADO 2.8 ADODB.Command with Parameters.
The .CommandText = "alter login [" & Login & "] with password = ?" seems
to
be executed as exec sp_executesql ... (see Profiler).
sp_password works fine, but BOL says not to use sp_password in new
applications.
Can anybody help, please?ALTER LOGIN doesn't accept variables so you can't parameterize the
statement. Is your intention to allow the current user to change their
password? In that case, you can specify the old/new password in the
connection string using the SQLNCLI provider:
"Provider=SQLNCLI;Data Source=MyServer;User ID=MyLogin;Old
Password=oldpassword;Password=newpasswor
d"
Hope this helps.
Dan Guzman
SQL Server MVP
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:418664FA-F9B0-4C82-8A17-A4D786C741B1@.microsoft.com...
> Hi,
> I've got a problem with new SQL Server 2005 T-SQL command alter login:
> I get this error while executing an ADO 2.8 ADODB.Command with Parameters.
> The .CommandText = "alter login [" & Login & "] with password = ?" see
ms
> to
> be executed as exec sp_executesql ... (see Profiler).
> sp_password works fine, but BOL says not to use sp_password in new
> applications.
> Can anybody help, please?
alter login on Windows user groups
Hi,
I have problem with respect to "alter login" on Windows user groups (created
via Active Directory).
If I execute the statement "alter login [domain\groupname] enable" I get
an
exception saying (the exception also occurs with "alter login
[domain\groupname] disable"):
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login of 'domain\groupname', because it does not exist or
you do not have permissions.
If if try to enable the login via SQL Server Management GUI I get the same
exception.
Is this because it is not at all possible to alter the logins of windows
user groups? Or is it a bug in SQL Server?
I am running MS SQL Server 2005 SP1 (compatibility level 90).
The user I am executing the statement as is "sysadmin" on the server.
Thanks in advance.
Kind Regards,
Rune Jensen
ETIAFAIK you can only disable/enable logins that represent individual windows
accounts, not windows groups.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Server App" <aaa@.yes.dk> wrote in message
news:%23VF$pTC%23GHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have problem with respect to "alter login" on Windows user groups
> (created
> via Active Directory).
> If I execute the statement "alter login [domain\groupname] enable" I g
et
> an
> exception saying (the exception also occurs with "alter login
> [domain\groupname] disable"):
> Msg 15151, Level 16, State 1, Line 1
> Cannot alter the login of 'domain\groupname', because it does not exist or
> you do not have permissions.
> If if try to enable the login via SQL Server Management GUI I get the same
> exception.
> Is this because it is not at all possible to alter the logins of windows
> user groups? Or is it a bug in SQL Server?
> I am running MS SQL Server 2005 SP1 (compatibility level 90).
> The user I am executing the statement as is "sysadmin" on the server.
> Thanks in advance.
> Kind Regards,
> Rune Jensen
> ETI
>
>
I have problem with respect to "alter login" on Windows user groups (created
via Active Directory).
If I execute the statement "alter login [domain\groupname] enable" I get
an
exception saying (the exception also occurs with "alter login
[domain\groupname] disable"):
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login of 'domain\groupname', because it does not exist or
you do not have permissions.
If if try to enable the login via SQL Server Management GUI I get the same
exception.
Is this because it is not at all possible to alter the logins of windows
user groups? Or is it a bug in SQL Server?
I am running MS SQL Server 2005 SP1 (compatibility level 90).
The user I am executing the statement as is "sysadmin" on the server.
Thanks in advance.
Kind Regards,
Rune Jensen
ETIAFAIK you can only disable/enable logins that represent individual windows
accounts, not windows groups.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Server App" <aaa@.yes.dk> wrote in message
news:%23VF$pTC%23GHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have problem with respect to "alter login" on Windows user groups
> (created
> via Active Directory).
> If I execute the statement "alter login [domain\groupname] enable" I g
et
> an
> exception saying (the exception also occurs with "alter login
> [domain\groupname] disable"):
> Msg 15151, Level 16, State 1, Line 1
> Cannot alter the login of 'domain\groupname', because it does not exist or
> you do not have permissions.
> If if try to enable the login via SQL Server Management GUI I get the same
> exception.
> Is this because it is not at all possible to alter the logins of windows
> user groups? Or is it a bug in SQL Server?
> I am running MS SQL Server 2005 SP1 (compatibility level 90).
> The user I am executing the statement as is "sysadmin" on the server.
> Thanks in advance.
> Kind Regards,
> Rune Jensen
> ETI
>
>
Alter login issue
I created a SQL server login "LOG1", granted him "ALTER" privilege on anothe
r
sql login "log2"
When I connect using "LOG1", right click on "log2" in "logins, security",
try to change its password I get the following error :
Change password failed......Additional info........
Can not alter login 'log2' because it does not exist or you dont have
permissions error 15151
Thanks for your helpProbably because you aren't supplying the old password when
you go through SSMS. Read the rest of the permissions
section in books online for ALTER LOGIN.
-Sue
On Thu, 26 Oct 2006 13:59:01 -0700, SalamElias
<eliassal@.online.nospam> wrote:
>I created a SQL server login "LOG1", granted him "ALTER" privilege on anoth
er
>sql login "log2"
>When I connect using "LOG1", right click on "log2" in "logins, security",
>try to change its password I get the following error :
>Change password failed......Additional info........
>Can not alter login 'log2' because it does not exist or you dont have
>permissions error 15151
>Thanks for your help|||Hello Salam,
I understand that you cannot change password of another login even you have
grant the alter login permission to the login. As Sue mentioned, this
behavior is as designed and you could refer to Books Online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b50-
57586e1cb1c5.htm for details. Any users without SQL admin rights/control
server permissions shall provide old password informaiton to change a
password of logins. This even occurs if the login want to change its own
password. This is a security purpose design.
You may try the following statement to change the password
Alter login testuser with password='newpass' old_password='oldpass'
Actually, this calls the following API in SQL Server.
ChangePassword(System.String oldPassword, System.String newPassword)
I understand it might be not convenient under some situation though it may
bring more security to SQL Server. Your feedback on this issue is routed to
the product team, and I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have concerns on the issue, please feel free
to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Salam,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Peter Yang [MSFT]" wrote:
> Hello Salam,
> I understand that you cannot change password of another login even you hav
e
> grant the alter login permission to the login. As Sue mentioned, this
> behavior is as designed and you could refer to Books Online
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b5
0-
> 57586e1cb1c5.htm for details. Any users without SQL admin rights/control
> server permissions shall provide old password informaiton to change a
> password of logins. This even occurs if the login want to change its own
> password. This is a security purpose design.
> You may try the following statement to change the password
> Alter login testuser with password='newpass' old_password='oldpass'
>
Thanks - this answer solved my problem. Nowhere in the indicated BOL page
does it say about the requirement to supply the old password to change the
new password - it even has the OLD_PASSWORD section in [optional] square
brackets in the Syntax section.
(When we create user accounts we set a default password, then get the user
to login and change it to something only they know. We were getting an
unhelpful "Login doesn't exists or permission denied" message, when the
users tried to change their details on our new SQL Server 2005 Server)|||I think your right in terms of the documentation not being
clear. The help topic for sp_password is alludes to the
issue a bit more. Nothing all that direct though.
-Sue
On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
<chrisredburn@.discussions.microsoft.com> wrote:
>
>"Peter Yang [MSFT]" wrote:
>
>Thanks - this answer solved my problem. Nowhere in the indicated BOL page
>does it say about the requirement to supply the old password to change the
>new password - it even has the OLD_PASSWORD section in [optional] squar
e
>brackets in the Syntax section.
>(When we create user accounts we set a default password, then get the user
>to login and change it to something only they know. We were getting an
>unhelpful "Login doesn't exists or permission denied" message, when the
>users tried to change their details on our new SQL Server 2005 Server)|||I opened a request for updating the permissions section of ALTER LOGIN.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hjmik29h0qm3lnrjfgkpostj8h7mjuu1ob@.
4ax.com...
>I think your right in terms of the documentation not being
> clear. The help topic for sp_password is alludes to the
> issue a bit more. Nothing all that direct though.
> -Sue
> On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
> <chrisredburn@.discussions.microsoft.com> wrote:
>
>|||Thanks Laurentiu!
-Sue
On Thu, 2 Nov 2006 11:55:24 -0800, "Laurentiu Cristofor
[MSFT]" <Laurentiu.Cristofor@.nospam.com> wrote:
>I opened a request for updating the permissions section of ALTER LOGIN.
>Thanks
r
sql login "log2"
When I connect using "LOG1", right click on "log2" in "logins, security",
try to change its password I get the following error :
Change password failed......Additional info........
Can not alter login 'log2' because it does not exist or you dont have
permissions error 15151
Thanks for your helpProbably because you aren't supplying the old password when
you go through SSMS. Read the rest of the permissions
section in books online for ALTER LOGIN.
-Sue
On Thu, 26 Oct 2006 13:59:01 -0700, SalamElias
<eliassal@.online.nospam> wrote:
>I created a SQL server login "LOG1", granted him "ALTER" privilege on anoth
er
>sql login "log2"
>When I connect using "LOG1", right click on "log2" in "logins, security",
>try to change its password I get the following error :
>Change password failed......Additional info........
>Can not alter login 'log2' because it does not exist or you dont have
>permissions error 15151
>Thanks for your help|||Hello Salam,
I understand that you cannot change password of another login even you have
grant the alter login permission to the login. As Sue mentioned, this
behavior is as designed and you could refer to Books Online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b50-
57586e1cb1c5.htm for details. Any users without SQL admin rights/control
server permissions shall provide old password informaiton to change a
password of logins. This even occurs if the login want to change its own
password. This is a security purpose design.
You may try the following statement to change the password
Alter login testuser with password='newpass' old_password='oldpass'
Actually, this calls the following API in SQL Server.
ChangePassword(System.String oldPassword, System.String newPassword)
I understand it might be not convenient under some situation though it may
bring more security to SQL Server. Your feedback on this issue is routed to
the product team, and I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have concerns on the issue, please feel free
to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Salam,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Peter Yang [MSFT]" wrote:
> Hello Salam,
> I understand that you cannot change password of another login even you hav
e
> grant the alter login permission to the login. As Sue mentioned, this
> behavior is as designed and you could refer to Books Online
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e247b84e-c99e-4af8-8b5
0-
> 57586e1cb1c5.htm for details. Any users without SQL admin rights/control
> server permissions shall provide old password informaiton to change a
> password of logins. This even occurs if the login want to change its own
> password. This is a security purpose design.
> You may try the following statement to change the password
> Alter login testuser with password='newpass' old_password='oldpass'
>
Thanks - this answer solved my problem. Nowhere in the indicated BOL page
does it say about the requirement to supply the old password to change the
new password - it even has the OLD_PASSWORD section in [optional] square
brackets in the Syntax section.
(When we create user accounts we set a default password, then get the user
to login and change it to something only they know. We were getting an
unhelpful "Login doesn't exists or permission denied" message, when the
users tried to change their details on our new SQL Server 2005 Server)|||I think your right in terms of the documentation not being
clear. The help topic for sp_password is alludes to the
issue a bit more. Nothing all that direct though.
-Sue
On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
<chrisredburn@.discussions.microsoft.com> wrote:
>
>"Peter Yang [MSFT]" wrote:
>
>Thanks - this answer solved my problem. Nowhere in the indicated BOL page
>does it say about the requirement to supply the old password to change the
>new password - it even has the OLD_PASSWORD section in [optional] squar
e
>brackets in the Syntax section.
>(When we create user accounts we set a default password, then get the user
>to login and change it to something only they know. We were getting an
>unhelpful "Login doesn't exists or permission denied" message, when the
>users tried to change their details on our new SQL Server 2005 Server)|||I opened a request for updating the permissions section of ALTER LOGIN.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hjmik29h0qm3lnrjfgkpostj8h7mjuu1ob@.
4ax.com...
>I think your right in terms of the documentation not being
> clear. The help topic for sp_password is alludes to the
> issue a bit more. Nothing all that direct though.
> -Sue
> On Wed, 1 Nov 2006 03:51:02 -0800, chrisredburn
> <chrisredburn@.discussions.microsoft.com> wrote:
>
>|||Thanks Laurentiu!
-Sue
On Thu, 2 Nov 2006 11:55:24 -0800, "Laurentiu Cristofor
[MSFT]" <Laurentiu.Cristofor@.nospam.com> wrote:
>I opened a request for updating the permissions section of ALTER LOGIN.
>Thanks
alter login disabel
hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> > hello all,
> >
> > When I RT click a login in management studio \script login as \create to
> > new
> > query window\ the script generated has
> >
> > ALTER LOGIN [ID] DISABLE
> >
> > after the creat login statement. I know the login was created enabled.
> > Is this a feature?
>
>
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> > hello all,
> >
> > When I RT click a login in management studio \script login as \create to
> > new
> > query window\ the script generated has
> >
> > ALTER LOGIN [ID] DISABLE
> >
> > after the creat login statement. I know the login was created enabled.
> > Is this a feature?
>
>
alter login disabel
hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?
I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?
|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?
I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?
|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>
alter login disabel
hello all,
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>
When I RT click a login in management studio \script login as \create to new
query window\ the script generated has
ALTER LOGIN [ID] DISABLE
after the creat login statement. I know the login was created enabled.
Is this a feature?I'm not sure I completely understand and/or agree, but did you notice the
comment:
/* For security reasons the login is created disabled and with a random
password. */
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
> hello all,
> When I RT click a login in management studio \script login as \create to
> new
> query window\ the script generated has
> ALTER LOGIN [ID] DISABLE
> after the creat login statement. I know the login was created enabled.
> Is this a feature?|||Thanks Aaron
Just proves that it helps to read the comments.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> I'm not sure I completely understand and/or agree, but did you notice the
> comment:
> /* For security reasons the login is created disabled and with a random
> password. */
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:1ACBA6C2-4C6A-444B-AB8F-78B9874254D5@.microsoft.com...
>
>
Sunday, February 19, 2012
allowing non sysadmin user to send email with attachments
i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?
any suggestion will be appreciated. thanks.
Which version of SQL Server are you using?|||i'm using sql server 2005 sp2|||One possibility could be using impersonation (EXECUTE AS) on the module and/or digital signatures. The idea would be to create a wrapper under your control that would escalate the privileges and call sp_snd_dbmail.
Personally I am not quite familiar with the DB mail functionally, but based on the fact that the invocation is via a SP, I am not sure if digital signatures will be enough in this particular case.
Digital signatures should allow the caller to execute the module, but the signature would be dropped in order to execute the code on sp_send_dbmail; if the SP has internal checks to verify the calling context the call will be terminated. Impersonating a privileged user (member of sysadmin in this case) and using a digital signature to vouch for the token at server scope (the signing certificate would require AUTHENTICATE SERVER) should be an option if the digital signature by itself is not enough in this scenario.
I would like to emphasize that because the escalation (either via signature or via EXECUTE AS) is going to be to sysadmin, you need to be extremely careful and make sure your code is safe and there are no possibilities of running arbitrary code (i.e. no SQL injection is possible, validate any input, make sure the parameters to sp_send_dbmail are safe, limit the escalated functionality to the essential minimum, etc.) by anyone who has permission to execute this module.
For more detailed information please refer to BOL:
http://msdn2.microsoft.com/en-us/library/ms188304.aspx
http://msdn2.microsoft.com/en-us/library/ms345102.aspx
Let us know if you have further questions or comments
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
|||I forgot to include Laurentiu's article on cross-DB as a reference: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Monday, February 13, 2012
All Users are being IDed as 'dbo'
We have a SQL Server 2005 database set up. We are trying to add new users to one of four define roles. Even though we are creating new login, then assigning each new login to one of the four roles. The server is returning 'dbo' as the user no matter who is logging in. Is there some setting that is causing this behavior?
Thanks of any help.
Can you post more information about the roles you mentioned and the commands you used to create the logins and assign them role memberships?
Thanks
Laurentiu
One likely possibility is that in your production system, the client is using credentials with SYSADMIN privileges (i.e. the login they are using is a member of the server fixed role SYSADMIN). Members of SYSADMIN will always have a user-identity of “dbo” in any database in the system.
-Raul Garcia
SDE/T
SQL Server Engine
Subscribe to:
Posts (Atom)