Only sysadmin can do that.. but I am one

Hi all.
We recently have been getting strange messages from SQL Server. Going into
EM and right clicking the server and selecting properties... I get a message
saying that "Only members of the sysadmin role can access this feature."
But I have administrator priveleges on the machine.
I try logging in with the SA username and password. It works but it also
tells me that I am not a sysadmin.
I tried re-installing the service pack but it won't even let me start
stating that I need to be a sysadmin to install it.
So I tried re-installing SQL Server and restoring my databases... still no
luck.
Does anyone have any ideas as to what it going on?
--
Michael
a0209129As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinking
about your login.
Are you saying you are getting this error message even with SA login?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Hi
You will have to check who the instance is registered under in EM, by
editing the registration.
If that is ok, you may want to check who is actually in the system
administrators role using
EXEC sp_helpsrvrolemember 'sysadmin'
BUILTIN\Administrators may have been removed. sa can't be removed from the
system administrators role.
John
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Yes, I am getting the error with the SA login. When I try select
IS_SRVROLEMEMBER('sysadmin', 'my_login') it responds with a 1 just like it
does with the SA.
--
Michael
a0209129
"Narayana Vyas Kondreddi" wrote:
> As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinking
> about your login.
> Are you saying you are getting this error message even with SA login?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> > Hi all.
> >
> > We recently have been getting strange messages from SQL Server. Going
> > into
> > EM and right clicking the server and selecting properties... I get a
> > message
> > saying that "Only members of the sysadmin role can access this feature."
> >
> > But I have administrator priveleges on the machine.
> >
> > I try logging in with the SA username and password. It works but it also
> > tells me that I am not a sysadmin.
> >
> > I tried re-installing the service pack but it won't even let me start
> > stating that I need to be a sysadmin to install it.
> >
> > So I tried re-installing SQL Server and restoring my databases... still no
> > luck.
> >
> > Does anyone have any ideas as to what it going on?
> > --
> > Michael
> > a0209129
> >
>
>|||Thanks, John.
When I try that command, I get the following message
Server: Msg 15412, Level 11, State 1, Procedure sp_helpsrvrolemember, Line 10
'sysadmin' is not a known fixed role.
--
Michael
a0209129
"John Bell" wrote:
> Hi
> You will have to check who the instance is registered under in EM, by
> editing the registration.
> If that is ok, you may want to check who is actually in the system
> administrators role using
> EXEC sp_helpsrvrolemember 'sysadmin'
> BUILTIN\Administrators may have been removed. sa can't be removed from the
> system administrators role.
> John
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> > Hi all.
> >
> > We recently have been getting strange messages from SQL Server. Going
> > into
> > EM and right clicking the server and selecting properties... I get a
> > message
> > saying that "Only members of the sysadmin role can access this feature."
> >
> > But I have administrator priveleges on the machine.
> >
> > I try logging in with the SA username and password. It works but it also
> > tells me that I am not a sysadmin.
> >
> > I tried re-installing the service pack but it won't even let me start
> > stating that I need to be a sysadmin to install it.
> >
> > So I tried re-installing SQL Server and restoring my databases... still no
> > luck.
> >
> > Does anyone have any ideas as to what it going on?
> > --
> > Michael
> > a0209129
> >
>
>|||Huh... if I do a select * from spt_values it returns no records at all.
I guess that's the reason. But how do I put them back? Can I put them
back? Can I insert into spt_values select * from
<someOtherServer>.master.dbo.spt_values?
--
Michael
a0209129
"a0209129" wrote:
> Hi all.
> We recently have been getting strange messages from SQL Server. Going into
> EM and right clicking the server and selecting properties... I get a message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Hi
You should probably rebuild the master database using rebuildm.exe as
there may be other things wrong.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_rebuildm_6dbt.asp
John

Only sysadmin can do that.. but I am one

Hi all.
We recently have been getting strange messages from SQL Server. Going into
EM and right clicking the server and selecting properties... I get a message
saying that "Only members of the sysadmin role can access this feature."
But I have administrator priveleges on the machine.
I try logging in with the SA username and password. It works but it also
tells me that I am not a sysadmin.
I tried re-installing the service pack but it won't even let me start
stating that I need to be a sysadmin to install it.
So I tried re-installing SQL Server and restoring my databases... still no
luck.
Does anyone have any ideas as to what it going on?
Michael
a0209129
As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinking
about your login.
Are you saying you are getting this error message even with SA login?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>
|||Hi
You will have to check who the instance is registered under in EM, by
editing the registration.
If that is ok, you may want to check who is actually in the system
administrators role using
EXEC sp_helpsrvrolemember 'sysadmin'
BUILTIN\Administrators may have been removed. sa can't be removed from the
system administrators role.
John
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>
|||Yes, I am getting the error with the SA login. When I try select
IS_SRVROLEMEMBER('sysadmin', 'my_login') it responds with a 1 just like it
does with the SA.
Michael
a0209129
"Narayana Vyas Kondreddi" wrote:

> As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinking
> about your login.
> Are you saying you are getting this error message even with SA login?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
>
>
|||Thanks, John.
When I try that command, I get the following message
Server: Msg 15412, Level 11, State 1, Procedure sp_helpsrvrolemember, Line 10
'sysadmin' is not a known fixed role.
Michael
a0209129
"John Bell" wrote:

> Hi
> You will have to check who the instance is registered under in EM, by
> editing the registration.
> If that is ok, you may want to check who is actually in the system
> administrators role using
> EXEC sp_helpsrvrolemember 'sysadmin'
> BUILTIN\Administrators may have been removed. sa can't be removed from the
> system administrators role.
> John
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
>
>
|||Huh... if I do a select * from spt_values it returns no records at all.
I guess that's the reason. But how do I put them back? Can I put them
back? Can I insert into spt_values select * from
<someOtherServer>.master.dbo.spt_values?
Michael
a0209129
"a0209129" wrote:

> Hi all.
> We recently have been getting strange messages from SQL Server. Going into
> EM and right clicking the server and selecting properties... I get a message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>
|||Hi
You should probably rebuild the master database using rebuildm.exe as
there may be other things wrong.
http://msdn.microsoft.com/library/de...uildm_6dbt.asp
John

Only sysadmin can do that.. but I am one

Hi all.
We recently have been getting strange messages from SQL Server. Going into
EM and right clicking the server and selecting properties... I get a message
saying that "Only members of the sysadmin role can access this feature."
But I have administrator priveleges on the machine.
I try logging in with the SA username and password. It works but it also
tells me that I am not a sysadmin.
I tried re-installing the service pack but it won't even let me start
stating that I need to be a sysadmin to install it.
So I tried re-installing SQL Server and restoring my databases... still no
luck.
Does anyone have any ideas as to what it going on?
--
Michael
a0209129As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinking
about your login.
Are you saying you are getting this error message even with SA login?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Hi
You will have to check who the instance is registered under in EM, by
editing the registration.
If that is ok, you may want to check who is actually in the system
administrators role using
EXEC sp_helpsrvrolemember 'sysadmin'
BUILTIN\Administrators may have been removed. sa can't be removed from the
system administrators role.
John
"a0209129" <a0209129@.discussions.microsoft.com> wrote in message
news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
> Hi all.
> We recently have been getting strange messages from SQL Server. Going
> into
> EM and right clicking the server and selecting properties... I get a
> message
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Yes, I am getting the error with the SA login. When I try select
IS_SRVROLEMEMBER('sysadmin', 'my_login') it responds with a 1 just like it
does with the SA.
--
Michael
a0209129
"Narayana Vyas Kondreddi" wrote:

> As a start, use IS_SRVROLEMEMBER function to see what SQL Server is thinki
ng
> about your login.
> Are you saying you are getting this error message even with SA login?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
>
>|||Thanks, John.
When I try that command, I get the following message
Server: Msg 15412, Level 11, State 1, Procedure sp_helpsrvrolemember, Line 1
0
'sysadmin' is not a known fixed role.
Michael
a0209129
"John Bell" wrote:

> Hi
> You will have to check who the instance is registered under in EM, by
> editing the registration.
> If that is ok, you may want to check who is actually in the system
> administrators role using
> EXEC sp_helpsrvrolemember 'sysadmin'
> BUILTIN\Administrators may have been removed. sa can't be removed from the
> system administrators role.
> John
>
> "a0209129" <a0209129@.discussions.microsoft.com> wrote in message
> news:6F86FA51-D67E-499D-A47D-8448742BBEE9@.microsoft.com...
>
>|||Huh... if I do a select * from spt_values it returns no records at all.
I guess that's the reason. But how do I put them back? Can I put them
back? Can I insert into spt_values select * from
<someOtherServer>.master.dbo.spt_values?
--
Michael
a0209129
"a0209129" wrote:

> Hi all.
> We recently have been getting strange messages from SQL Server. Going int
o
> EM and right clicking the server and selecting properties... I get a messa
ge
> saying that "Only members of the sysadmin role can access this feature."
> But I have administrator priveleges on the machine.
> I try logging in with the SA username and password. It works but it also
> tells me that I am not a sysadmin.
> I tried re-installing the service pack but it won't even let me start
> stating that I need to be a sysadmin to install it.
> So I tried re-installing SQL Server and restoring my databases... still no
> luck.
> Does anyone have any ideas as to what it going on?
> --
> Michael
> a0209129
>|||Hi
You should probably rebuild the master database using rebuildm.exe as
there may be other things wrong.
http://msdn.microsoft.com/library/d...>
ldm_6dbt.asp
John

Only select value once

Hello,

How do I go about selecting some things once and others more than once?

This is what I have returning right now:

Pet Name Food

Dog Buster Kibbles and Bits

Dog Buster IAMS

Dog Buddy Kibbles and Bits

How can I return this?

Pet Name Food

Dog Buster Kibbles and Bits

Dog IAMS

Dog Buddy Kibbles and Bits

I just want to the dog's name to appear once. If it appears more than once have all subsequent appearences be NULL.

How would I do this?

You can do this in a query but why are you generating a report using SQL? It is not meant for that. The query will look complex depending on the version of SQL Server and perform slower than a simple SELECT statement. You will get better performance if you use the client to do the reporting functionality. Any standard reporting tool will have options to suppress repeating values like you want. So what are you doing with this data? Is there an application that is issueing the query?|||

I don't have a report returning...just data.

I just want to select the dog's name once if it is the same dog....

It is filling a datagrid.

|||

The following query will work on SQL Server 2005,

But no guarantee on performance...

Code Snippet

Create Table #data (

[Pet] Varchar(100) ,

[Name] Varchar(100) ,

[Food] Varchar(100)

);

Insert Into #data Values('Dog','Buster','KibblesandBits');

Insert Into #data Values('Dog','Buster','IAMS');

Insert Into #data Values('Dog','Buddy','KibblesandBits');

;With Ordered

as

(

Select *, Row_number() Over(order By Pet) RowId from #data

)

, Grouped

as

(

Select * , Row_Number() Over(Partition By Pet,Name Order By RowID) as GroupId from Ordered

)

Select Pet,Case When GroupId=1 Then Name Else '' End,Food from Grouped Order By RowId