New to Instances: Dont have sa of newly created instance

A new instance has been setup on one of our pilot servers. I do not have th
e
sa password to that instance, and it appears that the group who set up the
instance also removed the built-in/admin group. Am I doomed to never access
this instance?
Should I bring flowers, donoughts AND cookies when begging for the sa
password?I would suggest that you get those that built it to come back and fix it.
The sa password will only help if the thing is running in mixed mode - you
can change this if you are an admin on the windows box by changing the
registry key for LoginMode of the instance (do a search down the
hkey_localmachine\software path) and changing the value to 2, then stopping
and restarting the sql server service.
You could also ask if they set up another admin group before deleting
built-in/admin, and get into it.
Good luck
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"wxd" <wxd@.discussions.microsoft.com> wrote in message
news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>A new instance has been setup on one of our pilot servers. I do not have
>the
> sa password to that instance, and it appears that the group who set up the
> instance also removed the built-in/admin group. Am I doomed to never
> access
> this instance?
> Should I bring flowers, donoughts AND cookies when begging for the sa
> password?|||Thank you for the response. Checking on what you wrote, I found the
LoginMode is already set to 2, and my administrator still receives a "failed
login" message when attempting to connect to the instance through Windows
Authentication. I did not think it was possible to lock out the net admin
group?
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stoppin
g
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>|||NEVERMIND! Thank you very much for your assistance.
I have found out what this instance is and am discovering that all SQL logic
no longer applies. This is an ACT! installation. ACT! installs an extremel
y
hacked up version of the SQL engine. It is my understanding that the normal
SQL login processes are not present on the server for me to get into this
instance. The company that produces ACT desperately wants people to use
their client vs. any other tool to access their database.
Thank you for your help and insight.
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stoppin
g
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>

New to Instances: Dont have sa of newly created instance

A new instance has been setup on one of our pilot servers. I do not have the
sa password to that instance, and it appears that the group who set up the
instance also removed the built-in/admin group. Am I doomed to never access
this instance?
Should I bring flowers, donoughts AND cookies when begging for the sa
password?
I would suggest that you get those that built it to come back and fix it.
The sa password will only help if the thing is running in mixed mode - you
can change this if you are an admin on the windows box by changing the
registry key for LoginMode of the instance (do a search down the
hkey_localmachine\software path) and changing the value to 2, then stopping
and restarting the sql server service.
You could also ask if they set up another admin group before deleting
built-in/admin, and get into it.
Good luck
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"wxd" <wxd@.discussions.microsoft.com> wrote in message
news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>A new instance has been setup on one of our pilot servers. I do not have
>the
> sa password to that instance, and it appears that the group who set up the
> instance also removed the built-in/admin group. Am I doomed to never
> access
> this instance?
> Should I bring flowers, donoughts AND cookies when begging for the sa
> password?
|||Thank you for the response. Checking on what you wrote, I found the
LoginMode is already set to 2, and my administrator still receives a "failed
login" message when attempting to connect to the instance through Windows
Authentication. I did not think it was possible to lock out the net admin
group?
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stopping
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>
|||NEVERMIND! Thank you very much for your assistance.
I have found out what this instance is and am discovering that all SQL logic
no longer applies. This is an ACT! installation. ACT! installs an extremely
hacked up version of the SQL engine. It is my understanding that the normal
SQL login processes are not present on the server for me to get into this
instance. The company that produces ACT desperately wants people to use
their client vs. any other tool to access their database.
Thank you for your help and insight.
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stopping
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>

New install of Windows 2003 EE sp1 and Sql Server 2000 sp3 full text problem

I have a newly installed Windows 2003 EE sp1 with Sql Server 2000 sp3
machine. I restored a database to the machine that had several full text
catalogs in it. I'm trying to go in and do a rebuild on the catalogs. When
I do this.. I get the error:
Error 7635: The Microsoft Search service cannot be administered under the
present user account.
The Sql Server Agent is being started with the system account.
I've tried this on a windows 2003 sp1 standardard machine with sql 2000 sp3.
I get the same error. It's making me belive that perhaps sp1 put some
security on that needs to be changed...
can someone help me get Full Text going agian.
Thanks
Shannon
Shannon,
Have you or anyone else removed or altered the login BUILTIN\Administrators
? If so, can you either re-establish it or reset the defaults back to master
database with sysadmin rights? If not (for security reasons), you may want
to implement the following T-SQL code as the external MSSearch service
(should be running under the system account) needs sysadmin rights when
accessing SQL Server:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Shannon Ramirez" <dba@.prairieinet.net> wrote in message
news:u6kXJPaUFHA.1200@.TK2MSFTNGP10.phx.gbl...
> I have a newly installed Windows 2003 EE sp1 with Sql Server 2000 sp3
> machine. I restored a database to the machine that had several full text
> catalogs in it. I'm trying to go in and do a rebuild on the catalogs.
When
> I do this.. I get the error:
> Error 7635: The Microsoft Search service cannot be administered under the
> present user account.
> The Sql Server Agent is being started with the system account.
>
> I've tried this on a windows 2003 sp1 standardard machine with sql 2000
sp3.
> I get the same error. It's making me belive that perhaps sp1 put some
> security on that needs to be changed...
> can someone help me get Full Text going agian.
> Thanks
> Shannon
>
|||no.. i'm the database guy and I didn't alter it.
I've just doubled checked.. default db is master and it belongs to the
sysadmin role.
anything else you can think of that I shoudl check
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O%23QYzjaUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Shannon,
> Have you or anyone else removed or altered the login
> BUILTIN\Administrators
> ? If so, can you either re-establish it or reset the defaults back to
> master
> database with sysadmin rights? If not (for security reasons), you may want
> to implement the following T-SQL code as the external MSSearch service
> (should be running under the system account) needs sysadmin rights when
> accessing SQL Server:
> exec sp_grantlogin N'NT Authority\System'
> exec sp_defaultdb N'NT Authority\System', N'master'
> exec sp_defaultlanguage N'NT Authority\System','us_english'
> exec sp_addsrvrolemember N'NT Authority\System', sysadmin
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Shannon Ramirez" <dba@.prairieinet.net> wrote in message
> news:u6kXJPaUFHA.1200@.TK2MSFTNGP10.phx.gbl...
> When
> sp3.
>
|||Shannon,
You should also confirm if the startup account for the SQL Server service
(MSSQLServer) has been altered via Win2K's Component applet vs. being
altered via SQL Server Enterprise manager. The later is the only place where
if one server is altered then both services security context is altered. See
the following two KB articles for more info:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
Hope this helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Shannon Ramirez" <dba@.prairieinet.net> wrote in message
news:u1WLC9aUFHA.2096@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> no.. i'm the database guy and I didn't alter it.
> I've just doubled checked.. default db is master and it belongs to the
> sysadmin role.
> anything else you can think of that I shoudl check
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O%23QYzjaUFHA.3176@.TK2MSFTNGP12.phx.gbl...
want[vbcol=seagreen]
text
>
|||thanks for the response.. I have checked and the accounts are as they should
be. this is already a production box so stopping and restarting the sql
service is not an option right now..
hope someone else has run into this and can help me out.
thanks
shannon
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ex%23hQHbUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Shannon,
> You should also confirm if the startup account for the SQL Server service
> (MSSQLServer) has been altered via Win2K's Component applet vs. being
> altered via SQL Server Enterprise manager. The later is the only place
> where
> if one server is altered then both services security context is altered.
> See
> the following two KB articles for more info:
> 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate
> Catalogs
> http://support.microsoft.com/default...b;en-us;317746
> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K
> Component
> Services]
> http://support.microsoft.com/default...B;EN-US;277549
>
> Hope this helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Shannon Ramirez" <dba@.prairieinet.net> wrote in message
> news:u1WLC9aUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> want
> text
>
|||I tried dropping the built in administrator sql account... then added it
back in along with the nt Authority\System account. On one of the windows
2003 sp1 sql 2000 sp3 boxes that fixed the problem. on the windows 2003 sp1
sql 2000 sp3 box.. it didn't. i'll restart teh sql service on Sunday and
see if that doesn't fix it.. sure hope so..
"Shannon Ramirez" <dba@.prairieinet.net> wrote in message
news:ePVuHUdUFHA.1384@.TK2MSFTNGP09.phx.gbl...
> thanks for the response.. I have checked and the accounts are as they
> should be. this is already a production box so stopping and restarting
> the sql service is not an option right now..
> hope someone else has run into this and can help me out.
> thanks
> shannon
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ex%23hQHbUFHA.3188@.TK2MSFTNGP09.phx.gbl...
>

New Id

I am using Microsoft Visual Studios 2005 Pro creating a web form in asp.net with a vb.net code page. I am trying to get the id of a newly inserted record. I am using the sqldatasource insert command to insert the record. Does anybody know how to return the new id. during the insert? Thanks, Any help is appreciated

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

|||

Here is a modified sample from Quickstarts tutorial with inline SQL for insert (ContactID is the Identity field of the table):

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)

Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@.ContactID").Value.ToString()) +"<br/>")

EndSub

<asp:GridViewAutoGenerateColumns="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

ID="GridView1"runat="server">

<Columns>

<asp:CommandFieldShowDeleteButton="True"/>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

</Columns>

</asp:GridView>

<asp:SqlDataSourceConnectionString="<%$ ConnectionStrings:MSDN_forumConnectionString %>"ID="SqlDataSource1"

runat="server"SelectCommand="select ContactID, ContactName from Contacts"

InsertCommand="INSERT INTO Contacts (ContactName) VALUES (@.contactName);SELECT @.contactID = Scope_Identity()"

OnInserted="SqlDataSource1_Inserted"DeleteCommand="DELETE FROM Contacts WHERE ContactID = @.ContactID">

<InsertParameters>

<asp:ParameterName="contactName"Type="String"/>

<asp:ParameterDirection="Output"Name="contactID"Type="Int32"/>

</InsertParameters>

<DeleteParameters>

<asp:ParameterName="ContactID"/>

</DeleteParameters>

</asp:SqlDataSource>

<br/>

<asp:DetailsViewAutoGenerateRows="False"DataKeyNames="ContactID"DataSourceID="SqlDataSource1"

DefaultMode="Insert"HeaderText="Insert New Contact"Height="50px"ID="DetailsView1"

runat="server"Width="125px">

<Fields>

<asp:BoundFieldDataField="ContactID"HeaderText="ContactID"InsertVisible="False"

ReadOnly="True"SortExpression="ContactID"/>

<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

|||

orka777:

there are basically 2 ways of doing that.

1. Create ID before you do insert using something like SELECT Max(id) +1 FROM ... Use this approach if you ID field is not set as a identity column

2. If your id field is an identity field then you can write a stored procedure that inserts the record and then you do something like

SET @.ID = scope_identity()

Where @.ID is your output parameter or you can return it as a return value.

For Number 1, is correct but might no be that good even it will do the job for you.

For Number 2, the code should be like:

1SET @.MyIdentity =@.@.IDENTITY

Where @.MyIdentity in an output parameter of type int.

Here I prefere to not return the identity of the inserted record as an output parameter, I perefere to return it using the Return statment in the stored procedure., like:

1CREATE PROCEDURE MySP23AS45SET NOCOUNT ON67declare MyIdentityint-- as a normal variable in the stored procedure body89Insert Into MyTable (col1, col2)values ('A','B')10SET @.MyIdentity =ISNULL(@.@.IDENTITY,-1)-- if no record is inserted then you will get -1111213Return @.MyIdentity1415SET NOCOUNT OFF16

Good luck.|||

I am trying to do this through the sqldatasource1.insert statement that I have built using the querybuilder. When I try to put in the @.ID as an output or as a return value It says I need to declare the scalar variable. I have already added in the parameters area and it is set to output with the control being the textbox that I want the Id in. But when I add the line "SET @.NewID = scope_identity()" or "SET @.NewID = @.@.identity" then I get an error on both @.NewId and @.SN which is another scalar variable which normally works fine and works fine again after I pull that line out. But I'm not doing it as a stored procedure. Could that be the problem? I don't know about stored procedures. How do I create them?

|||

Carehalf:

I don't know about stored procedures. How do I create them?

Just check my last post, it is a complete running fine example.

Anyway.. here is a very simple example:

1CREATE PROCEDURE MyStoredProcedureName2@.MyInputParameterint,-- input parameter of type integer3@.MyOutputParameterint OUTPUT-- output parameter of type integer45AS67-- write you statments here8-- and here9-- and here as well :)1011-- how to set a value for ourput parameter12SET @.MyOutputParameter = 100-- just for example1314-- how to increment the input parameter by 1 (for example)15SET @.MyInputParameter = @.MyInputParameter + 1161718/* At the end you the @.MyOutputParameter output parameter value19 will be 100 (as per the logic in this simple example20 Did you see how easy it is to created a stored procedure? */21

Good luck.

|||

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

|||

Carehalf:

I understand the stored procedure from looking at your last statement and this one, but I am not sure where to create it

Create you stored procedures in your application/project database.

Example:

USE MyDatabase
GO

Then the CREATE PROCEDURE statment as in my last two posts.

Good luck.

|||

Carehalf:

Create a table in your database and copy the code sample I posted (change the connection string in the code) and check the syntax to work for you if you don't want to create the Stored Procedure.

Here is the table definition:

CREATETABLE [dbo].[Contacts](

[ContactID] [int]IDENTITY(1,1)NOTNULL,

[ContactName] [nvarchar](50)

)