Showing posts with label howdy. Show all posts
Showing posts with label howdy. Show all posts

Sunday, February 19, 2012

A problem with the SYSLOGINS view?

Howdy.
While attempting to write a stored proc, on a SQL Server 2000 machine running win2k, that would back up all the user logins, I noticed something a little odd in the syslogins view. I was wondering if anyone else has this problem.

Make sure you have a non NT login with a password on your server and you are viewing your results in query analyzer in grid mode.

do a Select * from master..syslogins

Now, look at the password column. On mine, if the password is not null, then it makes the field blank and bumps the rest of the results over one. So the in the language column, I see the encrypted password, and in the deny login column I see the default language... anyone else noticed this?Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.|||Q1 Can anyone else please just try this so that I know I am not off my rocker? Select * from syslogins, and look at the password column. (do it in grid result mode in query analyzer.) Thanks.
--A1 This should fix it:
ALTER VIEW syslogins AS SELECT
sid = convert(varbinary(85), sid),
status = convert(smallint, 8 +
CASE WHEN (xstatus & 2)=0 THEN 1 ELSE 2 END),
createdate = convert(datetime, xdate1),
updatedate = convert(datetime, xdate2),
accdate = convert(datetime, xdate1),
totcpu = convert(int, 0),
totio = convert(int, 0),
spacelimit = convert(int, 0),
timelimit = convert(int, 0),
resultlimit = convert(int, 0),
name = convert(sysname, name),
dbname = convert(sysname, db_name(dbid)),
password = convert(varbinary(256), password),
language = convert(sysname, language),
denylogin = convert(int, CASE WHEN (xstatus&1)=1 THEN 1 ELSE 0 END),
hasaccess = convert(int, CASE WHEN (xstatus&2)=2 THEN 1 ELSE 0 END),
isntname = convert(int, CASE WHEN (xstatus&4)=4 THEN 1 ELSE 0 END),
isntgroup = convert(int, CASE WHEN (xstatus&12)=4 THEN 1 ELSE 0 END),
isntuser = convert(int, CASE WHEN (xstatus&12)=12 THEN 1 ELSE 0 END),
sysadmin = convert(int, CASE WHEN (xstatus&16)=16 THEN 1 ELSE 0 END),
securityadmin = convert(int, CASE WHEN (xstatus&32)=32 THEN 1 ELSE 0 END),
serveradmin = convert(int, CASE WHEN (xstatus&64)=64 THEN 1 ELSE 0 END),
setupadmin = convert(int, CASE WHEN (xstatus&128)=128 THEN 1 ELSE 0 END),
processadmin = convert(int, CASE WHEN (xstatus&256)=256 THEN 1 ELSE 0 END),
diskadmin = convert(int, CASE WHEN (xstatus&512)=512 THEN 1 ELSE 0 END),
dbcreator = convert(int, CASE WHEN (xstatus&1024)=1024 THEN 1 ELSE 0 END),
bulkadmin = convert(int, CASE WHEN (xstatus&4096)=4096 THEN 1 ELSE 0 END),
loginname = convert(sysname, name)
FROM sysxlogins WHERE srvid IS NULL|||Yeah, changing the datatype to varbinary did fix it, however, being the system view that syslogins is, it wont let me save the changes. I ended up doing a select from the sysxlogins, decyphering the xstatus bit info, and scripting that out. Bummer that the view is broken though. It's good to know that I am not the only one with the problem.|||Try making sure you are logged in as dba, enable ad hoc system table updates, etc.?|||Yeah, I tried that... logged in with sa, enabled system table modification... I think maybe because it is a view instead of a table, it didnt like it. Anyway, no matter, my work around should function nicely.|||Odd, the syslogins view alteres each time I've attempted it.|||Well, enterprise manager let me alter the view. No idea why I couldnt do it with a script through query analyzer.|||You might want to check the Server connection properties in EM (just to see if you are using the same account you had attempted to use in isqlw, a.k.a. QA).|||This is strictly a visual problem (under very specific circumstances - not windows login using the grid in qa) - not a problem for your original task - making a backup - correct ?|||Well, ok, the original problem was as follows. One of our SQL servers here at work contains back end databases for customers websites. As you can imagine there are quite a few databases. (Over 1000) Each customer has a SQL login with access granted to their appropriate database. That login information is saved, as you know, in the sysxlogins table in the master database. Now in our back up schema, master isnt backed up that often, so we needed a method of backing up the login info. My original thought was to take the information from syslogins and script it into a table in a database that is backed up every 15 minutes. This is needed because on average 3 to 5 new databases can be added per day, and 2 to 3 deleted. When I went to select the records from the syslogins, I realized that the view had the bug and wanted to make sure that it was everywhere and not only on my machines.

As for the solution, I pull the login data directly from sysxlogins table and populate my Login_Info table. And all is happy. :D|||I just wanted to make sure - I have been using this view without problems - it retrieves and stores the data appropriately. Only through qa in grid mode does this problem appear.

Thursday, February 9, 2012

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
>
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisR
I got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>
|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>
|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:[vbcol=seagreen]
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
>> Howdy all. I've got a couple questions regarding Partition Function (PF)
> and
>> Partition Scheme (PS) and I was wondering if someone could assist?
>> 1. How do I assign a table to a Partition Scheme? I created a test PF and
>> PS, but how could they really partition my table if the table doesnt know
> to
>> use them? I know I can assign the table to a filegroup that has a PS on
> it,
>> but didn't know if that alone would cut it?
>> 2. Is there some place I can verify all my settings once I think I have
> them
>> right?
>> 3. Would it ever make sense to partition an index without partitioning
>> the
>> table it resides on?
>> TIA, ChrisR
>>
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> > 2. Is there some place I can verify all my settings once I think I have
> > them right?
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
> > 3. Would it ever make sense to partition an index without partitioning
the
> > table it resides on?
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
> >I got number 1, but could still use help with 2 and 3. Here is number 1.
> >
> > CREATE PARTITION FUNCTION myRangePF2 (int)
> >
> > AS RANGE LEFT FOR VALUES (1, 100, 1000);
> >
> > GO
> >
> >
> >
> > CREATE PARTITION SCHEME myRangePS2
> >
> > AS PARTITION myRangePF2
> >
> > TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> >
> > [TransactionNumber] [int] NOT NULL)
> >
> > ON myRangePS2 (TransactionNumber)
> >
> >
> >
> >
> >
> >
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> >> Howdy all. I've got a couple questions regarding Partition Function
(PF)
> > and
> >> Partition Scheme (PS) and I was wondering if someone could assist?
> >>
> >> 1. How do I assign a table to a Partition Scheme? I created a test PF
and
> >> PS, but how could they really partition my table if the table doesnt
know
> > to
> >> use them? I know I can assign the table to a filegroup that has a PS on
> > it,
> >> but didn't know if that alone would cut it?
> >>
> >> 2. Is there some place I can verify all my settings once I think I have
> > them
> >> right?
> >>
> >> 3. Would it ever make sense to partition an index without partitioning
> >> the
> >> table it resides on?
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>