How can I convert download SQL Server 2005 to a licensed version.

I downloaded a 180 day trial version of SQL Server 2005, and have it running. I have purchased a 5 user workgroup version. I would like to apply the 5 user license to the version that is currently installed.

My preference is to not reinstall everything since I have everything configured as I would like, and it is working great. What is the best approach?

Paul

The only way to do this is to Upgrade your Evaluation Edition to the Workgroup SKU. To do this you should run the Workgroup installation program and select the edition of SQL Server that you already have installed instead of installing a new edition.

Michelle

|||

Will this leave the installed database and all of its configuration as is?

Or will it change any of the configuration?

The reason I ask, is that due to a lack of proper planning we did not have SQL Server installed prior to a vendor arriving to install their application. I quickly downloaded the trial version, and installed it and ordered the Workgroup Edition. The applicationn is in and running, and I now want to make everything legal, and to have it run past the 180 day evaluation period.

If upgrading will change the application, I will need to pay the vendor to reconfigure things, and cause me problems.

Paul

sql

How can I control the user to SQL Server?

Hi,
I want to control on SQL Server 2000 users. I use C# language. My scenario
that have SQL Server 2000 on my user "CIMBOM". How can i write code there
user for control username and password. May be prepared sql function?
i hope explain my problem :)
too thanks...Hi
If you check out the topic "How to allow access by granting permissions" in
Books online it may help you to understand the how different logins can have
different access level.
Also you may want to look at the IS_MEMBER function or the other "Security
Functions" available if you need to control access to specific data within a
given table.
John
"Gürol Ayanlar" wrote:

> Hi,
> I want to control on SQL Server 2000 users. I use C# language. My scenario
> that have SQL Server 2000 on my user "CIMBOM". How can i write code there
> user for control username and password. May be prepared sql function?
> i hope explain my problem :)
> too thanks...

how can i connect to two databases?

hello,

i want to make a relation betwen one of my tables and the user tables (to take it's unique ID), if there isn't any methode to do that without using the two databases(ASPNETDB - automaticly created when a user registers, and MyData), how can i connect to both databases? here is my connection string, but what should i do?

<connectionStrings> <add name="SiteConnection" connectionString="Server=(local)\SqlExpress; Integrated Security = True; Database = MyData" providerName="System.Data.SqlClient" /> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyData.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /></connectionStrings>

thank you

Hello zuperboy90,

Maybe you should take a look at this post:-

Asp.net database created at for memebership logan

Cheers,

Eric

|||

is there any problem if i use the same databse(created by default) for users and my other things?...but still, it will be a very big mess there

|||

Hello zuperboy90,

I don't think there is any problem I supposed. If someone out there aware of any potential problem, please share your concerns with us.

Thank you.

Regards,

Eric

How can I connect to a remote sql server using windows authentication?

It is simple

1- I open the Sql Server 2005 Management Studio

2- I select Windows Authentication from the drop down.

3- I cannot write the user name and password, it chooses the default once, the one I am logged in with!

But I am in a virtual machine outside the domain controller, I can access shares on machines that are on the domain controller, thanks to the file sharing of windows, but I cannot login to sql server, thanks to a meaningless restriction on that dialog :-)

Now, how can I still use the Windows Authentication and login, how can I avoid the sql server authentication?

If you are outside domain, then you should use SQL Authentication.|||

Ok, then why the text boxes for the user name and password are still there if I choose the Windows authentication, and the text boxes have the user name already filled in, and all are disabled.

What is the point of having those there? In my case, I was trying to find a way to enable them from the settings; I guess just a false hope.

And why cannot I use the windows authentication? NTFS does allow me to do it and access the file system from outside the domain using windows authentication against the domain, what does make sql server more special?

|||

The reason for the textbox is just to let you know which Windows account is being used to connect to SQL Server using Windows authentication. To access SQL Server using SQL authentication, click the Authentication drop-down to see the SQL Server Authentication option. You'll see the User name and Password textboxes enabled.

If you want to use Windows authentication, the easiest way is to join your SQL Server to your domain.

|||

Thank you for the help, but I know how to use the SQL Server authentication, and the SQL Server is the development server and it is on the domain.

My virtual machine is the development machine, it is a virtual machine and it cannot join the domain, it must stay as it is, the real machine is on the domain, but the virtual machine that I am trying to use is not.

From the virtual machine I can do lots of things, including accessing the file system and the intranet sites on the domain, using the domain authentication box, or cached credentials, but I cannot do that with the SQL Server.

|||

if you want an nt authentication

then you must promote your virtual machine to a domain controller

How can I check the version?

Hi, I need to know what's is the latest service pack of my sql 7.0, how can
I check it?
How do I know which user is using sql database?
Thanks,
SarahHi, I need to know what's is the latest service pack of my sql 7.0, how canI
check it?
run
SELECT @.@.version statement to view the version
7.00.699 SP1
7.00.642 SP2
7.00.961 SP3
7.00.1063 SP4
How do I know which user is using sql database?
sp_who or sp_who2 to see who is logged in.
"Sarah G." <sguo@.coopervision.com> wrote in message
news:u%23lFgq0GEHA.3068@.TK2MSFTNGP11.phx.gbl...
> Hi, I need to know what's is the latest service pack of my sql 7.0, how
can
> I check it?
> How do I know which user is using sql database?
> Thanks,
> Sarah
>|||Thank you so much,
Sarah
"Prasad Koukuntla" <prasad.koukuntla@.scapromo.com> wrote in message
news:eh9exu0GEHA.1240@.TK2MSFTNGP10.phx.gbl...
> Hi, I need to know what's is the latest service pack of my sql 7.0, how
canI
> check it?
> run
> SELECT @.@.version statement to view the version
> 7.00.699 SP1
> 7.00.642 SP2
> 7.00.961 SP3
> 7.00.1063 SP4
> How do I know which user is using sql database?
> sp_who or sp_who2 to see who is logged in.
>
> "Sarah G." <sguo@.coopervision.com> wrote in message
> news:u%23lFgq0GEHA.3068@.TK2MSFTNGP11.phx.gbl...
> can
>

how can I change the owner of an sp_

Hello,
I have a couple of sp_ that a user created now the have the domain\user
as the owner but no one else can run them. Can I set the owner of these sp_
to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
JakeLook up sp_changeobjectowner in BOL.
"Jake" <rondican@.hotmail.com> wrote in message
news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a couple of sp_ that a user created now the have the
domain\user
> as the owner but no one else can run them. Can I set the owner of these
sp_
> to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
> Jake
>|||Adam,
Thanks for the info.
Jake
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OkIVw6usEHA.2960@.TK2MSFTNGP10.phx.gbl...
> Look up sp_changeobjectowner in BOL.
>
> "Jake" <rondican@.hotmail.com> wrote in message
> news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> domain\user
> sp_
>sql

how can I change the owner of an sp_

Hello,
I have a couple of sp_ that a user created now the have the domain\user
as the owner but no one else can run them. Can I set the owner of these sp_
to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
Jake
Look up sp_changeobjectowner in BOL.
"Jake" <rondican@.hotmail.com> wrote in message
news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a couple of sp_ that a user created now the have the
domain\user
> as the owner but no one else can run them. Can I set the owner of these
sp_
> to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
> Jake
>
|||Adam,
Thanks for the info.
Jake
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OkIVw6usEHA.2960@.TK2MSFTNGP10.phx.gbl...
> Look up sp_changeobjectowner in BOL.
>
> "Jake" <rondican@.hotmail.com> wrote in message
> news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> domain\user
> sp_
>

how can I change the owner of an sp_

Hello,
I have a couple of sp_ that a user created now the have the domain\user
as the owner but no one else can run them. Can I set the owner of these sp_
to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
JakeLook up sp_changeobjectowner in BOL.
"Jake" <rondican@.hotmail.com> wrote in message
news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a couple of sp_ that a user created now the have the
domain\user
> as the owner but no one else can run them. Can I set the owner of these
sp_
> to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
> Jake
>|||Adam,
Thanks for the info.
Jake
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OkIVw6usEHA.2960@.TK2MSFTNGP10.phx.gbl...
> Look up sp_changeobjectowner in BOL.
>
> "Jake" <rondican@.hotmail.com> wrote in message
> news:OYYaA1usEHA.1604@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I have a couple of sp_ that a user created now the have the
> domain\user
>> as the owner but no one else can run them. Can I set the owner of these
> sp_
>> to the dbo? How can I accomplish that on 20 sp's? Thanks in advance.
>> Jake
>>
>

How can I change the format of a date returned from asp:calendar

Hello!

I have a table in an SQL database, in which I have a field in datetime format.

In my aspx page I would like to get the date the user chooses from an asp: calendar I have and submit it to the DB.

I already have all the code ready, the datasource, the gridview, all other fields to submit, and I just added a template field with the asp:calendar so that the user could choose a date.

I′m getting this error when I run the page: "Conversion from type 'Date' to type 'Boolean' is not valid."

It seems to be a problem about the date that is given by the Calendar object (?) and the one I should submit to my DB.

Here′s the part of the code where I have my standard Calendar binded to the correspondant field:

<asp:Calendar ID="Calendar1" runat="server" SelectedDate='<%# Bind("data")%>' Visible='<%# Eval("data")%>'>
</asp:Calendar>

I′m gessing I should probably change the format of the date somehow before submit it to the DB, but how?

Thank you all,

RR

Format(dateVariable,"MM/dd/yyyy")

|||

sorry the noobness, but where can I do that?

in a script section in the beginnig of the page?

|||

You have bound the "data" column to both the SelectedDate and the Visible property. SelectedDate is of type Date, and Visible is of type Boolean. What datatype is the "data" column?

|||

You′re asking about the datatype in the db, right?

It′s datetime. (don′t know if it′s the best datatype, any advise here?) I only need a data like DD-MM-YYYY but when building my table in SQL, I have no format like this...

An update to this issue, I erased the visible property and the page at least runs, but no connection between the calendar and my field... maybe it′s better to explain my objective:

What I would need is a gridview where I can see my records. (done)
In the default view I would see all the fields in normal textboxes, (ok!, done)
When clicking insert new or edit, I would like to let the user choose a date from the calendar!
Can anyone help me to buid a thing like this?

THKS

How can I change the default Save-As/Save directory

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.

I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?

Thanks,

Nanci

Goto [Tools], [Options], [Query Results]

There you will be able to change the default query results storage location.

|||

I have changed that setting, but it only works for the results of the query, not saving the query itself. Any other suggestions?

Nanci

sql

How can I be notified when record is updated

I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..

osmansays,
Combine a trigger and a stored procedure to send a email on updates
--------------------------------
Procedure like :
Create Procedure sp_SMTPMail
@.SenderName varchar(100),
@.SenderAddress varchar(100),
@.RecipientName varchar(100),
@.RecipientAddress varchar(100),
@.Subject varchar(200),
@.Body varchar(8000),
@.MailServer varchar(100) = 'localhost'
AS

SET nocount on
declare @.oMail int
declare @.resultcode int
EXEC @.resultcode = sp_OACreate 'SMTPsvg.Mailer', @.oMail OUT
if @.resultcode = 0
BEGIN
EXEC @.resultcode = sp_OASetProperty @.oMail, 'RemoteHost', @.mailserver
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromName', @.SenderName
EXEC @.resultcode = sp_OASetProperty @.oMail, 'FromAddress', @.SenderAddress
EXEC @.resultcode = sp_OAMethod @.oMail, 'AddRecipient', NULL, @.RecipientName, @.RecipientAddress
EXEC @.resultcode = sp_OASetProperty @.oMail, 'Subject', @.Subject
EXEC @.resultcode = sp_OASetProperty @.oMail, 'BodyText', @.Body
EXEC @.resultcode = sp_OAMethod @.oMail, 'SendMail', NULL
EXEC sp_OADestroy @.oMail
END
SET nocount off
--------------------------------
Trigger like :
CREATE TRIGGER trgDataChanged on tblData
AFTER UPDATE
AS
BEGIN
exec sp_SMTPMail @.SenderName='me', @.SenderAddress='me@.somewhere.com', @.RecipientName = 'Someone', @.RecipientAddress = 'someone@.someplace.com', @.Subject='SQL Data Change', @.body='data in table tblData has been changed'
END
--------------------------------
If you also want to track the changes you can either translate the query to simple data or show the data that is changed but to view that you need to walk through the recordset with for instance a cursor.
Peter

How can default schema change in stored procedure ?

Hello,

How can default schema change in stored procedure ?

For Example:

There are two user 'User1', 'User2' in TestDb Database.
These users default schema is same name, like 'User1's default schema is 'User1', and 'User2's default schme is 'User2'.
And each users have 'Table1' table, like [User1].[Table1], [User2].[Table1]

In this enviroment,
query 'SELECT * FROM [Table1]' refer default schema of execute user.
like 'User1' execute 'SELECT * FROM [User1].[Table1]'.

But if dbo create a stored procedure below, default schema doesn't work.

CREATE PROCEDURE SelectTable1
AS
SET NOCOUNT ON
SELECT * FROM Table1
GO

When User1/User2 execute this stored procedure, error happend because Table1 not found.

So, I want to change default schema in stored procedure to current users default schema.
EXECUTE AS CALLER is change current user principal only, this doen't change default schema.

Regards,

This is not possible to do in TSQL right now without using dynamic SQL for the query inside the stored procedure. For EXECUTE AS CALLER the unqualified object names resolve against the schema for the owner of the SP and not the caller. This is known issue and there have been requests to provide the facility to resolve object name against the invoker of the SPs. Oracle for example allows you to specify this when creating PL/SQL SPs.

How can client applications know if a table has been changed by another user?

Is there a mechanism in SQL Server 2000 for notifying client apps in a
multiuser setting when a change has occurred in a table (notification
event).
And/Or, is there a way a client application can 'ask' if a table has
changed?
Thanks,
WykDo you want to know whether that table design is changed or records are
added?
Madhivanan|||In both the cases you have to create your own extensions
to handle the requirement.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109581918.952377.73540@.z14g2000cwz.googlegroups.com...
> Do you want to know whether that table design is changed or records are
> added?
> Madhivanan
>|||It seems like you are describing an optimistic locking strategy. SQL
Server provides a ROWVERSION datatype (also called TIMESTAMP) to track
changes to a row. The client retrieves the data, including timestamp
and then, immediately before saving a change or performing other
actions, the retrieved timestamp is compared to the current one in the
table to determine if the data has changed.
David Portas
SQL Server MVP
--|||> ...The client retrieves the data, including timestamp
> and then, immediately before saving a change or performing other
> actions, the retrieved timestamp is compared to the current one in the
> table to determine if the data has changed.
Or, in the UPDATE, you include the buffered rowversion value in the WHERE cl
ause (with the primary
key value). If the update modifies zero rows, you know that either the row w
as deleted or it was
updated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109586059.793247.265620@.f14g2000cwb.googlegroups.com...
> It seems like you are describing an optimistic locking strategy. SQL
> Server provides a ROWVERSION datatype (also called TIMESTAMP) to track
> changes to a row. The client retrieves the data, including timestamp
> and then, immediately before saving a change or performing other
> actions, the retrieved timestamp is compared to the current one in the
> table to determine if the data has changed.
> --
> David Portas
> SQL Server MVP
> --
>|||LOL. First time I noticed your signature, Joe, I thought that SQLNS referred
to the SQL-NS API with
which you can re-use dialogs from EM in your client app. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:uT5aNdaHFHA.3108@.tk2msftngp13.phx.gbl...
> If you're asking about a way in which a client application can be notified
of new or even updated
> records on the server, then you may want to check out Notification Service
s. The provided Realtor
> sample provides a simple example.
>
> HTH...
> Joe Webb
> SQL Server MVP
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
>
> Tibor Karaszi wrote:

how can associated sa with a trusted SQL Server connection in sql server 2005?

I want to use sa user login sql server 2005 to visit my database "dotnet20" but when I set the user property in User Mapping, It Report
when I set it, An Error Occur like follow
Cannot use the special principal 'sa'. (Microsoft SQL Server, Error: 15405)
and also when I want to Login sql server use 'sa' user, It Report
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
how can associated 'sa' with a trusted SQL Server connection?

I'm having this exact same problem.

When I login using Windows authent. I can connect to the DB but cannot ad users or grant permissions.

I can create tables fine, but other than that not much? Any idea.

I basically wanted to enable SQL Authent. as well as windows in the security Tab after right-clicking on my Database, but the Windows login lacks the rights although it was used to create the DB and all.

Help !

|||

Basically, how can I add my Windows user account to the sysadmin role.

I couldve used the default sa account but whenever I login in SQL Serv authent. mode using 'sa' and blank Pwd on the SQL Serv Mngmt Studio Express CTP, on the 1st attempt, it says smthg. like

Cannot connect to INSPI6K\SQLEXPRESS.

----------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

Upon trying again just says

Cannot connect to INSPI6K\SQLEXPRESS.

----------
ADDITIONAL INFORMATION:

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

when I changed the Network protocol to Named Pipes or TCP/IP I got

Cannot connect to INSPI6K\SQLEXPRESS.

----------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

What's happening? Why can't I login as 'sa' ?

Please tell me all U DBA-gods out there...

|||

One f the suggestions I recvd. was to hack the registry, and change theLoginMode of the SQL Server 2005 Express.

Any idea how to go about it, and which Reg. key to change for enabling mixed authentications(SQL & Windows), instead of just Windows auth.?

I saw this article but couldn't find that key

http://support.microsoft.com/default.aspx?scid=kb;en-us;285097

Thanks

|||

OK I finally found theLoginMode key by searching the Registry and changed it to 2 (original value was 1)

Required a Restart, for it to take effect.

Thankfully not getting thetrusted connection error anymore.

But now it's whining about the password.

AFAIK I never set any Pwd for 'sa' account, yet executing sqlcmd on command prompt throws error

Password Msg:18456

Is there any way to reset the sa PWD? What's the way out?

|||

Phew! The problem has finally been resolved.

What a nightmare... all due to installing SQL Server 2005 Express on top of existing MSDE (from VS 7.0)

Seehttp://forums.asp.net/1222469/ShowPost.aspx for further details.

and with some help from this articlehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124596&SiteID=1

Thanks.

Moral of the story: Always unninstall previous versions. Better safe than very sorry !

How can a variable used in SQL select command

select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.You can, but you are missing some basic insights here ...

You can do this like this:

string sql = "Select * from TABLE where user = '" + name + "'"

OR use a stringbuilder of so ...

If you are using SQL Server or any decent DBMS, try using stored procedures instead|||Thank you very much!

how can a user who is in a role assign his role to another user?

I create a role(eg. HighOperators), and add user 'ABC01' into it. 'ABC01'
is not a member of the sysadmin fixed server role or the db_owner fixed data
base role or the db_securityadmin fixed database role, can he assign his ro
le to another user?
In SQL Server Books Online, it says " Role owners can execute sp_addrolememb
er to add a member to any SQL Server role they own". but I don't know how to
set or get the a role's Owner.The only way I know to set the role owner is when you create
the role, specify the owner of the role in the second
argument for sp_addrole.
In terms of retrieving the owner, I don't remember there
being a direct way. I think it may be that the altuid in
sysusers is the uid for the owner of the role.
-Sue
On Tue, 27 Apr 2004 05:31:04 -0700, samuelzhu
<anonymous@.discussions.microsoft.com> wrote:

>I create a role(eg. HighOperators), and add user 'ABC01' into it. 'ABC01'
is not a member of the sysadmin fixed server role or the db_owner fixed dat
abase role or the db_securityadmin fixed database role, can he assign his r
ole to another user?
>In SQL Server Books Online, it says " Role owners can execute sp_addrolemember to a
dd a member to any SQL Server role they own". but I don't know how to set or get the
a role's Owner.

How can a user change password in SQL2005 via TSQL?

I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;

The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;

"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"

TOM will be setup with db roles as well

When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"

At this point I get an error:

RunTime error -2147217900 (80040e14)

ODBC SQL Server Driver][SQL Server] Cannot alter

the login 'TOM', becuase it does not exist or you do

not have permission.

Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?

Thanks in advance for your help.

CH

A user does not need any permissions to change his password, but he cannot change his password policy setting - that option is only settable by someone that has ALTER ANY LOGIN permission. See http://msdn2.microsoft.com/en-us/library/ms189828.aspx.

Thanks

Laurentiu

|||

If I have the users change password statement read; it works.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx' "

However, I am unable to test this on a Win2003 Server right now, so I wonder if the Check Policy will be enforced for user TOM.

Under no circumstances do I want to have Policy Checked since the front end of my app provides the password security.

Any ideas if this is possible?

CH

|||If you are using ADO.NET 2.0 you can also use the

SqlConnection.ChangePassword(ConnectionString, "MyNewSecretpassword");

For changing the password.

Jens K. Suessmeyer.

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

Thanks for the reply.

The problem we have run into is the Windows security, which is set across the domain, maybe set differently than our app. So if the user of our app chooses a different policy that that of the domain (which is fine), the user cannot alter his login since he does not have Alter Any login rights. And we wouldn't want to give him any.

Do you see a way around this?

All I can think of now is to change the password via the Admin login through a separate connection to the database. Something like this;

Dim gConnTmp As New ADODB.Connection
Dim sConnect As String

With gConnTmp
sConnect = "DSN=" & SQLDataSource & ";"
sConnect = sConnect & "UID= 'ADMIN' ;"
sConnect = sConnect & "PWD=" & gsPassword & ";"
.Open sConnect
End With

'change the password here... ALTER LOGIN etc.

'kill connection

gConnTmp.Close
Set gConnTmp = Nothing

Do you see any issues with doing this? I think it looks ok.

CH

|||

If you want to enforce a custom password policy, then you should just create the login with CHECK_POLICY set to off; otherwise, the Windows password policy will be enforced for a password change (on Windows 2003).

Thanks

Laurentiu

How can a non-admin see all system catalog data?

I have given the following SQL to database user who with db_SecurityAdmin & db_AccessAdmin database roles. He doesn't see any more than his data when he runs it. I am an sa on the database and see all of the data. What security does he need in order to pull all data as a non-sa or is it possible for a user other than sa to see it all?

The other idea: If this SQL was placed in a stored procedure - would a non-sa be able to pull all of the data from it? Is there a way for them to execute the proc as sa?

SELECT sys.sql_logins.name as Login_name, sys.database_principals.name as Principal_name , sys.database_principals.type_desc , database_principals1 .name AS role_name

FROM sys.database_principals

INNER JOIN

sys.sql_logins on sys.database_principals.sid = sys.sql_logins.sid

INNER JOIN

sys.database_role_members ON sys.database_principals.principal_id = sys.database_role_members.member_principal_id

INNER JOIN

sys.database_principals AS database_principals1 ON sys.database_role_members .role_principal_id = database_principals1.principal_id

Thanks for your help!

You have this user accessing restricted tables.

You could try adding the user to the securityadmin role. If that doesn't work for you, you could create the stored procedure with EXECUTE AS permissions, and then GRANT the user permission to EXECUTE the stored procedure

|||

These are system catalogs - views not tables.

This is what Online books suggested for use as the system tables could change in future releases.

If this is not the correct source for this information, where should it be retrieved from?

|||And these 'views' are accessing restricted system tables.|||

If I place the SQL in a stored procedure,

what do I need to add to ensure that the user can execute it as SA.

I have tried the EXECUTE as 'sa' statement and I must be missing something because it doesn't work.

THANKS!

|||

You don't have to do anything.

Anyone placed in the [sysadmin] Role (sa), can do anything in the server, including, executing any stored procedures. The [sysadmin] Role totally controls the server and all databases on the server.

You might wish to read up on 'Roles' in Books Online.

|||

Even the "public" role as select permission on the catalog views. In 2005, though, this isn't enough, and accounts must be granted "view definition" permissions. For example, this statement grants permission to the "public" role to see all system metadata:

use master; grant view any definition to public

See this for more info:

http://msdn2.microsoft.com/en-us/library/ms175808.aspx

Ron Rice

How can a non-admin see all system catalog data?

I have given the following SQL to database user who with db_SecurityAdmin & db_AccessAdmin database roles. He doesn't see any more than his data when he runs it. I am an sa on the database and see all of the data. What security does he need in order to pull all data as a non-sa or is it possible for a user other than sa to see it all?

The other idea: If this SQL was placed in a stored procedure - would a non-sa be able to pull all of the data from it? Is there a way for them to execute the proc as sa?

SELECT sys.sql_logins.name as Login_name, sys.database_principals.name as Principal_name , sys.database_principals.type_desc , database_principals1 .name AS role_name

FROM sys.database_principals

INNER JOIN

sys.sql_logins on sys.database_principals.sid = sys.sql_logins.sid

INNER JOIN

sys.database_role_members ON sys.database_principals.principal_id = sys.database_role_members.member_principal_id

INNER JOIN

sys.database_principals AS database_principals1 ON sys.database_role_members .role_principal_id = database_principals1.principal_id

Thanks for your help!

You have this user accessing restricted tables.

You could try adding the user to the securityadmin role. If that doesn't work for you, you could create the stored procedure with EXECUTE AS permissions, and then GRANT the user permission to EXECUTE the stored procedure

|||

These are system catalogs - views not tables.

This is what Online books suggested for use as the system tables could change in future releases.

If this is not the correct source for this information, where should it be retrieved from?

|||And these 'views' are accessing restricted system tables.|||

If I place the SQL in a stored procedure,

what do I need to add to ensure that the user can execute it as SA.

I have tried the EXECUTE as 'sa' statement and I must be missing something because it doesn't work.

THANKS!

|||

You don't have to do anything.

Anyone placed in the [sysadmin] Role (sa), can do anything in the server, including, executing any stored procedures. The [sysadmin] Role totally controls the server and all databases on the server.

You might wish to read up on 'Roles' in Books Online.

|||

Even the "public" role as select permission on the catalog views. In 2005, though, this isn't enough, and accounts must be granted "view definition" permissions. For example, this statement grants permission to the "public" role to see all system metadata:

use master; grant view any definition to public

See this for more info:

http://msdn2.microsoft.com/en-us/library/ms175808.aspx

Ron Rice

how call stored procedure when user click on report line?

have a way to add ability to report, to do somthing, like delete
record, by calling Stored procedure when user click on some line within
the report?Hey I feel reports should be meant for reporting/Viewing. You should not
provide something like delete or modify options from reports. Then it becomes
an entry screen. I hope you will agree on this point.
Amarnath
"mtczx232@.yahoo.com" wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
>|||mtczx232@.yahoo.com wrote:
> have a way to add ability to report, to do somthing, like delete
> record, by calling Stored procedure when user click on some line within
> the report?
RS can call a stored procedure, or any arbitrary SQL with a side-effect
such as deleting a record.
Your SQL should return a result set as well as deleting the record. You
could navigate to another report, passing a parameter containing the id
of the parameter to delete. This "deleting" report would display
instead of the main report, which could look a bit funny.
Alternatively you could navigate to a URL of an ASP or ASPX page that
does the job. Ideally you would set the target so the web page
displayed as a pop-up with a message like "record xx deleted" instead
of replacing the whole report.