How do I associate a sql server login with the SQLAgentUserRole?

Currently a 2005 login has been granted sysadmin because I can not figure out how to grant the same login the SqlAgentUserRole. I thought I would use SSMS but there must be a trick. The role only exists in the msdb database, which the login has not been granted explicit access to. And the login does not appear in the list of Role Members or its sub windows. This should be real easy and intuitive . . .

Thanks!

Michael

You cannot associate a login with a database role. What you can do is map the login to a database user (CREATE USER/sp_adduser) and then make that user a role member (sp_addrolemember).

For understanding the distinction that exists between logins and users, have a look at: http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx.

Thanks

Laurentiu

|||

I was rather loose with my definitions. But thinking about it caused me to create a User in msdb for the login in question and was then able to associate the User in msdb as a Member of the fixed database role SQLAgentUserRole. I still don't know yet if this will allow me to demote the login from sysadmin and still allow the User to set-up and run his own SqlServerAgent jobs. But that is another day!

Thanks

sql

How can you force a password, from a sql login, to expire

How can you force a password, from a sql login, to expire?

I would like to use the password expiration feature for sql logins in SqlServer 2005. The msdn document provides example code for SqlClient SqlConnection.ChangePassword like in Bob Beauchemin's book. http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changepassword.aspx

There is a modify_date in the system view sys.sql_logins but that is read-only.

Thanks,

Karl

If you are on a Windows 2003 Server, and the password policy for expiratiopn is enabled this will be automatically enabled for SQL Logins. Password policies for SQL Server 2005 are only available on a Windows 2003 Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens for replying,

I want to test the client side code in the scenario when the password expires for a sql server login. So I'm hoping for a method that I could repeat several times to test my client code.

Yes I am using Windows Server 2003. The password expiration is set to 90 days in the security policy. However, I don't want to wait 90 days to see If my test sql login account will expire, so I can test my client side code. I'm not the administrator for the Windows Server, but I am the administrator for SQL Server 2005.

It sounds like one possible approach would be to set the security policy to 1 day on a test server then wait one day, but that would only be good for one test?

Karl

|||If you enforce password policies from Windows 2003 Server one test should be enough. Just make sure to handle the appropiate codes like 18468 "Password Expired." and 18487 "Password has to be changed at first logon".

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

If you want to programatically modify the global password policy setting to a value that you can test can finish fast, you can try NetUserModalsSet(...) to set max_passwd_age to a value in seconds.

http://windowssdk.msdn.microsoft.com/en-us/library/ms707221.aspx

How can you force a password, from a sql login, to expire

How can you force a password, from a sql login, to expire?

I would like to use the password expiration feature for sql logins in SqlServer 2005. The msdn document provides example code for SqlClient SqlConnection.ChangePassword like in Bob Beauchemin's book. http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changepassword.aspx

There is a modify_date in the system view sys.sql_logins but that is read-only.

Thanks,

Karl

If you are on a Windows 2003 Server, and the password policy for expiratiopn is enabled this will be automatically enabled for SQL Logins. Password policies for SQL Server 2005 are only available on a Windows 2003 Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens for replying,

I want to test the client side code in the scenario when the password expires for a sql server login. So I'm hoping for a method that I could repeat several times to test my client code.

Yes I am using Windows Server 2003. The password expiration is set to 90 days in the security policy. However, I don't want to wait 90 days to see If my test sql login account will expire, so I can test my client side code. I'm not the administrator for the Windows Server, but I am the administrator for SQL Server 2005.

It sounds like one possible approach would be to set the security policy to 1 day on a test server then wait one day, but that would only be good for one test?

Karl

|||If you enforce password policies from Windows 2003 Server one test should be enough. Just make sure to handle the appropiate codes like 18468 "Password Expired." and 18487 "Password has to be changed at first logon".

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

If you want to programatically modify the global password policy setting to a value that you can test can finish fast, you can try NetUserModalsSet(...) to set max_passwd_age to a value in seconds.

http://windowssdk.msdn.microsoft.com/en-us/library/ms707221.aspx

How can I turn off the encrypted login feature?

Hi all.

My question is, is there any option that turning of the login encrypt?

I've read many post that they said the login encryption is always on and can't turn that off.
With MSSQL2K, I made some program that audit who try to login and when and access which db.
But I can't check with MSSQL2K5 because the server and client always encrypt the login info (via TLS?)

Someone said that profile the client but when the site is busy, that feature give heavy stress to server..
The port mirror server is another machine so I need 1. decrypt the login info or 2. turn off the login encryption feature.
Is there any possible way to solve this?

Thanks for reading this.

If your goal is to audit SQL Server login's and access to SQL Server objects, you can consider SQL Server auditing. E.g., the following link in SQL Server 2005 books online describes how to turn on Login Auditing through SQL Server Management Studio: http://msdn2.microsoft.com/en-US/library/ms175850.aspx.

|||

Yes, my goal is audit the login information and other info also.

But, audit in the local server do the heavy stress to itself and also need more and more hard disk space. Especially the site QPS is more than 10000..

So, there is no way to turn off the login encryption?

Anyway, thanks for your answer, it's very helpful information.

|||

Hi Hopi,

No, there is no way to turn of login encryption for SQL Server 2005. The server will always encrypt the login packet whenever possible. And with the server's new capability of generating a self-signed cert, this most of the time.

Il-Sung.

|||

Hi. Il-Sung.

Thank you for your answer. And here's final question.

1. Is the self-signed cert generated whenever the SQL service stared? Or that cert generated only once that installed into server?

2. Where can I found that cert?

3. Is there any document for this?

Hopi

|||

Hi Hopi,

I apologize for the delay. Here are the answers to your questions:

1 - The self-signed cert is generated each time the service is started

2 - The cert is stored internally and is not accessible or queryable by any external interface

3 - The BOL topic Encrypting Connections to SQL Server mentions this:

"By default, credentials transmitted when a client application connects to SQL Server 2005 are encrypted. If a certificate signed by a mutually-trusted certification authority is not available, the self-signed certificate will be used."

Thanks,
Il-Sung

|||

Thank for your kind answer.

I still didn't resolve this but maybe a week or so.. I think I can fix our solution. Not by turning off the encryption.
Thanks again!

B/R
Hopi