Modifying Sysxlogins Domain Names In SQL2000, Strange Result..
Jul 20, 2005
It seens that a simple script:
Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8
)
where name like 'ILLINOIS%'
Will replace the SQL2000 domain name correctly in sysxlogins:
ILLINOISJonesP becomes AA001JonesP
But for some strange reason via ILLINOISJonesP can still logon via
QueryAnalyzer although he is no longer in the sysxlogins table
anymore? SQL has been stop/started, server even rebooted, yet BOTH
the new and old logins seem to both allow QA login, any thought how
the old one is getting thru SQL security?
I have tried to synchronize master..sysxlogins table from production server to a testing server. All the databases in the testing server will be loaded from the production server. In order to save drop and recreate the database's users in every database restore, I have tried to find out a quick way to synchronize the sysxlogins table. However, when I load the sysxlogins from the production server onto the tempdb in the testing server, I get the following error :
Server: Msg 213, Level 16, State 5, Line 1 Insert Error: Column name or number of supplied values does not match table definition.
Any comment and suggestion is highly appreciated. Thanks
The table structures (output of sp_help) of the temporary table and master..sysxlogins are as follows :
1. sp_help testjoe4
Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- --------------------------- testjoe4 dbo user table 2002-02-19 15:27:31.930
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- srvid smallint no 2 5 0 yes (n/a) (n/a) NULL sid varbinary no 85 yes no no NULL xstatus smallint no 2 5 0 no (n/a) (n/a) NULL xdate1 datetime no 8 no (n/a) (n/a) NULL xdate2 datetime no 8 no (n/a) (n/a) NULL name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS password varbinary no 256 yes no no NULL dbid smallint no 2 5 0 no (n/a) (n/a) NULL language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS isrpcinmap smallint no 2 5 0 yes (n/a) (n/a) NULL ishqoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL selfoutmap smallint no 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------- No identity column defined. NULL NULL NULL
RowGuidCol -------------------------------------------------------------------------------------------------------------------------------- No rowguidcol column defined.
No foreign keys reference this table. No views with schema binding reference this table.
2. sp_help master..sysxlogins
Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- --------------------------- sysxlogins dbo system table 2000-08-06 01:29:12.500
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- srvid smallint no 2 5 0 yes (n/a) (n/a) NULL sid varbinary no 85 yes no no NULL xstatus smallint no 2 5 0 no (n/a) (n/a) NULL xdate1 datetime no 8 no (n/a) (n/a) NULL xdate2 datetime no 8 no (n/a) (n/a) NULL name sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS password varbinary no 256 yes no no NULL dbid smallint no 2 5 0 no (n/a) (n/a) NULL language sysname no 256 yes (n/a) (n/a) SQL_Latin1_General_Pref_CP1_CI_AS isrpcinmap smallint yes 2 5 0 yes (n/a) (n/a) NULL ishqoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL selfoutmap smallint yes 2 5 0 yes (n/a) (n/a) NULL
Identity Seed Increment Not For Replication -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------- No identity column defined. NULL NULL NULL
RowGuidCol -------------------------------------------------------------------------------------------------------------------------------- No rowguidcol column defined.
index_name index_description index_keys -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ncsysxlogins nonclustered located on PRIMARY srvid, sid sysxlogins clustered, unique located on PRIMARY srvid, name, sid
No constraints have been defined for this object.
No foreign keys reference this table. Table is referenced by views --------------------------------------------------------------------------------------------------------------------------------
running sql server 2000 sp4, viewing the sysxlogins table data, and am wondering about the three items listed with a <NULL> in the name column, all xstatus = 192, each has a unique srvid value of 0, 2 or 3.
Looking in the MS tables reference book did not help they don't cover sysxlogins, googled around but no luck, searched this forum also.
Hi All, Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column “col1” had width of varchar(10) and this was altered to varchar(100).
Hello All, SQL 2000 does not allow me to create a table variable or UDF. I am sure my syntax is correct but it gives me a syntax error near 'create' or near 'table'. This db was originally ported from SQL 6.5->7.0->2000. Can anyone explain?
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
Hi Folks!I have a strange thing happening; I have a field that Counts the number ofrecords and another field that shows the number of clients(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression"Count(RecordID)/NoOfClients" the expected result should be 1.5. Insteadthe result I get is 1. Any ideas?Thanks!Rick
I have a SQL Server 200 database called sampleDB in my local server that uses windows authentication, and all objects are owned by 'dbo' user. I created a creation script using the Eneterprise Manager 2000. Then I ran the script against a database on my hosting service provider's server, that uses sql server login and not windows authentication, and I found some stored procedures were created with 'dbo' as owner and some with 'sun21170' as owner. 'sun21170' is the user for the database of my hosting service provider's server. Why should not all stored procedures be owned by dbo, as they were in original database on my local server?
Hello all, I have the following Stored Procedure that has been working perfectly for the last year: CODE ==================================================== DELETE FROM tblReportMainMembers INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444' UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users. But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again. Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it. Any ideas? Thanks in advance.Richard M.
This is a very strange result I had noticed while using the IsMissing property.
I had the following Expression.
=IIF(Fields!CertainField.IsMissing = True, "N/A", Sum(Fields!.CertainField.Value)) This should just put the text "N/A" into the textfield if the specific field is missing from the dataset in the report.
I had put this in the following TextFields:-
The group's detail and the table Footer.
The result that I had gotten show an empty textfield in the group's detail but showed "N/A" in the page footer. So I know that the expression I had used is correct since it showed up in the table footer. What baffles me is that it didn't give me a "#Error" in the textfield, but an empty string (which essentially is correct, since this particular field in the dataset is missing to begin with).
The question I am asking is, does this have anything to do with the scope or is it something else, and how do I resolve this problem ?
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 5 O'Grady Patrick 1975-09-23 00:00:00.000 7 Greenfield Lynne 1955-07-26 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
I'm in the process of converting a rather huge VSAM database into a set of SQL tables. I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME). I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255). And I have to cut and paste the data names from the manframe side to the server side (and the data types to.) So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away... Thanks for any help.
Hi I have two database called test1 and test2 both are of same size(both device and log). I run the following statements to take the backup of both the databases using the tape device 'tapedevice1'. dump database test1 to 'tapedevice1' dump database test2 to 'tapedevice1' After this I run the load statement load database test2 from 'tapedevice1'. It just restore the database test2 with test1 data which is added first to the tape with a warning. Is there any restriction that in a single tape backup of more than one database is not possible. Your valuable suggestion appreciated Jiji
this is my problem in stored procedure 1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE 2) I need to see only the employee than in the table v_un 3) i see all the employee 4) problem in this line ( [new date] = @mydate2,[new_shift2] = ) i see 2 rows of result from fields [new date] + ,[new_shift] how to see only 1 field for each field
new date new_shift new date empid name --------------------------------------------------------------- 2007-12-01 99 2007-12-02 99 2568947 aaa 2007-12-01 99 2007-12-02 99 2845209 bbbb 2007-12-01 99 2007-12-02 99 4807756 ccc 2007-12-01 99 2007-12-02 99 9819590 ddd 2007-12-01 99 2007-12-02 99 10055648 eee 2007-12-01 99 2007-12-02 99 10815413 ffff 2007-12-01 99 2007-12-02 99 11070042 gggg 2007-12-01 99 2007-12-02 99 11162047 hhh ------------------------------------------------------------------------------- i need to see only one for each field
new date new_shift id name --------------------------------------------------------------- 2007-12-01 99 2568947 aaa 2007-12-01 99 2845209 bbbb 2007-12-01 99 4807756 ccc 2007-12-01 99 9819590 ddd 2007-12-02 99 10055648 eee 2007-12-02 99 10815413 ffff 2007-12-02 99 11070042 gggg 2007-12-02 99 11162047 hhh
Code Block DECLARE @yeara [varchar](4) DECLARE @month1 [varchar](2) DECLARE @day1 [varchar](2) DECLARE @day2 [varchar](2) DECLARE @day3 [varchar](2) DECLARE @mydate1 [datetime] DECLARE @mydate2 [datetime] set @yeara ='2007' SET @month1 ='12' Set @day1 ='1' Set @day2 ='2' set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103) set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103) SELECT SilokE CASE WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 2 else 99 END, [new date] = @mydate2,[new_shift] = CASE WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 12 ))) THEN 2 else 99 END, SilokE.empid, SilokE.Fname FROM SilokE
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names.
How can I set SQL 2005 so the names of the columns will come along with the content of the copy function?
Background:
When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.
In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?
Hi All Gentleman, I am writing a script to control rights and privileges regarding roles and login . I have upgraded my server from sql server 2000 to 2005.I have a script which gives me all the logins that moved from sql server 2000 to sql server 2005 (i.e same instance)or on same server.I have multiple database say for example 5 database.Now my task is to grant all programmers login to programmer role and all logins belonging to this role should be granted only select permission on table on all the databases. Can anyone help me with the script for my task? I have observed that sysxlogins table exist in sql server 2000 however it does not exist in sql server 2005. Can anyone tell me what is the equivalent of sysxlogins system table in sqlserver 2005?
-A "master domain" AD, a "sub domain" AD, a trust relationship between the two (sub trust master) -A sql server 2005 on a win server 2003 in "sub domain" AD -A linked server to "sub domain" AD -A linked server login using a "sub domain" admin acccount -A view to this linked server -A grant on masterDomain/Domain Users to the database -A grant on subDomain/Domain Users to the database -We want all connections done through "Windows Authentication" not "Database Authentication".
Queries on the view work fine using "sub domain" user accounts. Queries on the view fail using "master domain" user accounts (including master domain admin accounts)
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."
All connections are done through "Windows Authentication" not "Database Authentication".
Can we establish cross domain connectivity with "Windows Authentication" ?
Below are details of the implementation:
SELECT TOP (100) PERCENT * FROM OPENQUERY(ADSI, 'SELECT displayname, givenName, sn, cn (etc...) FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')
In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:
"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'
Error: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT displayname, givenName, sn, cn
FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'
WHERE
objectCategory = 'Person'
AND objectClass = 'user'
" against OLE DB provider "ADsDSOObject" for linked server "ADSI".
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any  password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success. - Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server ------------------------------
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
