Hiya,
I'm trying to run reports, some of which have UserEntered as a criteria. So I'm filling up a list with (SELECT * FROM SysUsers WHERE IsLogin = 1), which is also pulling up INFORMATION_SCHEMA as a login. I'm not really sure why this would be an islogin = 1 or issqluser = 1. Is this a server default login,and if yes,why, and what can I do about it?
Thanks,
Sarah
Sql 7.0
Is there Tsql to list the roles of all the users(both logged and unlogged)of sql server.
Any help will be appreciated
what is the status column value spcify in sysusers table.
because in our job selecting a row whose status = 2.I am new DBA to this project, and this code were written by previous DBA.Thanks in Advance
Has anyone else ever restored a database and noticed that any users added into the sysusers table do not show up on the main database GUI under the general tab (i.e., the number of users for that database will equal zero)?
However,a quick look into the sysusers table will show that they are in fact there.
It's very strange.
Any ideas?
Does any one know the meaning of "status" column in the sysusers table?
I found out in one of my database the status has three different values: 0, 2, 14, what this value means? Thanks.
Yu
Hi,
In SQL Server, if I active the application role, can all the public user still read the sysusers table even though the application role does not select the sysusers as read permission?
Thanks.
Is it possible to have a two way replication of the Logins and Users between two servers? What we have is two SQL 7.0 servers, for an CRM package. They use the CRM replication program to move data back and forth. However, we need to keep the logins and passwords between the two machines ther same. Any thoughts on how to do this?
View 1 Replies View RelatedDear All
Within the sysusers table there is a field named status... All the users has this field set to 2. However there is a particular user and the status for this user is set to 14. Does anyone knows what this means... i.e. why the status field is not set to 2 as it is for the other users?
Regards
Sandro
Hello:
We are running an application on mssql 6.5, sp 4.
As the data for the application is growing by leaps and bounds, we were shortly going to run out of disk space on one and decided to move production to a new, larger box.
I took a full backup of the current production database nad loaded it onto the new server with no problems except one.
When users tried to log in, there were problems. Apparently the passwords for users were switched with other users or somehow the sysusers data got reversed. THe logins, or the data from msyslogins, looked fine.
I don't understand how a full backup and then a restore would have any effect on the sysusers data.
Has any one experienced this before? Other than bcp out the data from sysusers and bcp'ing the data after the restore, is there any thing else I can do?
Does any one have a script tp offload sysusers data?
Any help which can be provided would be appreciated. THanks.
David Spaisman
Hi everyone
I have a problem about sysusers table in sql2005.
if you run the query in SQL2000
select * from sysusers where status = 2
and you can see the users which are created as sqlauthentication.
But in sql2005 status column equals 0 for sql authenticated users.
Is this a bug or the sturucture of the sysusers table is changed???
And how can i query the sql authenticated users in sql2005.
thanks
Hi to all,
I'm using SQL Server 2005. May I know where the INFORMATION_SCHEMA
is found, can we view this by object explorer.
Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
Thanks
Hi:
I need to change a column default to '' and not null for 1500 databases accross 10 servers.
if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end
Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?
thanks
-D
I'm having a really hard time displaying the structure of my database using INFORMATION_SCHEMA. Can this be used with Access? I've used it before for sql databases but when I try it with this access one I get an error message telling me: Can't find file c:...INFORMATION_SCHEMA.mdb
I'm hoping maybe there's another command that works just as well or some other way to get around it.
I'm using Visual Web Developer 2005 Express
and my .aspx pages are coded using VB so any insights would be awesome!
Thanks much,
~UNI
Hi I need to see all the indexes in a database. The ID has dbo rightsto the database, but not to the master. I can't see anything inINFORMATION_SCHEMA.CHECK_CONSTRAINTS orINFORMATION_SCHEMA.KEY_COLUMN_USAGEAn sa ID for the master sees everything however.Thanks for your helpPachydermitis
View 5 Replies View RelatedHi,
I'd like to get information about tables defined in the SqlCe20 database. So I tried to use the following which doesn't work:
string sql = "SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"cmd = new SqlCeCommand(sql, connection);cmd.Parameters.Add(newSqlCeDataParameter("A", DbTypes.NVarChar);cmd.Prepare();//later in codecmd.Parameters[0].Value = tableName;reader = cmd.ExecuteReader();while (reader.Read())....
Here the reader.Read() returns always false indepent of the table name.
In contrast doing the following without parameter works as expected:
reader = cmd.ExecuteReader(string.Format("SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName));
while (reader.Read())
Because I have to execute this statemen often I have to use prepared statement with parameters.
How can I get it working?
Miroslaw
Hi:
from SQL2005 information_schema, I don't see anything related to default constraint.
So, I still need the old way to use old stuff such as
from sysobjects sobj inner join syscolumns scolumn.
I need to check existing 3000 databases to drop any possible default for tableA.columnB's default and then add a new one.
I thought SQL2005 will stop let us to query system table to retrieve table structure info including default, but only via information_schema? :rolleyes:
thanks
David
Hi there!
I'm trying to find how can I get the information_schema for a temp table.
I'm trying to find all columns of a temp table.
So it will be something like this SELECT * FROM information_schema.columns
But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns .... nada....
Please help!
Thanks,
Or Tho
Hi:
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
Thanks a lot
how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?
Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local
select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked
but not
select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]
How can I access the INFORMATION_SCHEMA on the linked server ?
Thanks,
Helena
The following code example to returns table space usage in my databases, except for AdventureWorks.
When I "USE AdventureWorks", I get the following message. "Invalid object name 'INFORMATION_SCHEMA.tables'."
Why is AdventureWorks different? Thanks. Bill
USE AdventureWorks
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100))
DECLARE @TableName varchar(100)
--Insert Table names into the TABLE variable
INSERT @AWTables
(SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable
EXEC sp_spaceused @TableName
DELETE @AWTables
WHERE SchemaTable = @TableName
END
Hi,
I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).
Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?
Thanks!
Hi Folks:I'm a little new to SQLServer, so please pardon my ignorance!I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, andTABLE_CONSTRAINTS. I'm looking for the views that will give me the list ofcolumns by constraint.For instance, if Table1 has a unique key called Table1_UK01, I can find thatunder INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know thecolumns in that UK constraint. I've triedINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE andINFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this usertable doesn't seem to show up in either of those views.Can anyone point me in the right direction? Any sample queries would betremendously appreciated. I'm going to be using this meta-data toautomatically generate quite a bundle of stored procs that do updates basedon finding rows via unique keys...TIA,Dave
View 3 Replies View RelatedHi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“
select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'
However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“
select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'
0 rows are returned!
Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.
Hi,
we've just installed SQL2005 and, as expected, are hitting problems with collation settings.
We have made sure that the collation of our new server is the same as the old (working) SQL2000 server (both are Latin1_General_CI_AS) but a function that works fine on the 2000 box just doesn't work on the 2005 box.
The problem is this...
Code Snippet
select *
from information_schema.[tables] as t
left join :: fn_ListExtendedProperty('MS_Description','user','dbo','table',null,null,null) as xp on ( xp.objname = t.table_name )
...where that string comparison on "table_name" results in...
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."
Now... I can't see where the "Latin1_General_CI_AI" is coming from. The two data sources are both "system" sources, so should, I would have thought, been the same. If I do...
execute sp_help "information_schema.tables"
...all the columns are Latin1_General_CI_AS. If I do...
execute sp_help fn_ListExtendedProperty
...the same collation is returned for all columns.
I've checked everything I can think of (server, master, tempdb, etc.), and everything seems to be "AS".
Where might this "AI" collation be defined ? We have only just installed 2005, so a re-install, while a pain, is not a massive problem, but what should we check/change ?
Thanks
I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?
View 3 Replies View RelatedHi,
Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()
Source File: C:finalyearproject2sample.aspx Line: 15
Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies View RelatedI am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
hi,
so i have a new box and I'm trying to get my websites and SQL Server 2005 Standard Edition working on it, but the pages give me the following error when I try to load them:
"Cannot Open Database "XXXX" requested by login. The login failed. Login failed for user 'xxx'"
Everything seems exactly the same settings and user-wise from my old box to my new one, but nevertheless everything I've tried gives me the same error.
I've tried creating new users in SQL Server and giving them appropriate permissions to my database. I've even tried just using the built in 'sa' account. Nothing seems to change the error, except when I give it the incorrect password then it just says 'login failed'
This leads me to believe that i'm successfully logging into the SQL Server, but it doesn't want to give me access to the database I'm requesting access too. But "apparently" the account i'm using should have access to the database. If nothing else the 'sa' account should, but that didn't work either.
I'm stumped. Any ideas?
Hi I have asp.net2 app using SQLExpress which works fine in VS2005.
When I compile it and try to run it from IIS on the same machine using the same
sql server database with:
connectionstring="server=.SQLEXPRESS;Database=abc.mdf;Trusted_Connection=yes"/>
I get the following error.
Cannot open database abc.mdf requested by login. The login failed. Login failed for user 'machinenameASPNET'
Any help much appreciated
Thanks
David