My main task is to create a login under a specific database.
For which I'm validating whether that login already exists or not I'm checking it in the syslogins table. I need to specifically check it is in my databse or not....for that what should I do...
Hi everyone, I have an instance with many databases in it. I am looking for the easiest way to see which of those databases a user has a login on. What is the most efficient way of doing this? Thanks, Anil
I am new to SSRS. I am doing some report models for Ad-hoc reports. Im my database i have some users and their roles according to my requirement. User should only see the particular reports if he have privilege. how to customize...
User will access Report Builder to do adhoc report.
For example I have 5 report Models rptModel1 rptModel2 rptModel3 rptModel4 rptModel5
In my data base there are 3 users. user1 have privilege to view rptModel1, rptModel3 user2 have privilege to view rptModel4, rptModel5 user3 have privilege to view rptModel2, rptModel5
When Particular user access the report builder, There i want to show only that particular Report models... is there any posibilities to do this please Help me.
Application uses historical data for alarms, responses, etc. The application came with a sync agent but the agent only syncronizes at a minimum every 15 minutes (nice HA feature we paid for). 15 Minues is unacceptable in the event it syncronizes then 5 minutes later the database goes down - we lose 10 minutes of data. I have configured a standby server and have configured snapshot replication since the application also uses simple recovery mode, has tables with no PK, etc - so transactional / merge replication are out of the picture.
I have created publisher / subscriber configurations and all is fine....the problem comes into play with historical data. All data is stored in a database say 'History0208' indicating the current month/year. Each month a new database is created (I am assuming at 12:00 am of the first day of each month) but I will need to replicate the new database over to the subcriber. How can I go about automating this? There is a table called 'History Data' which does indicate the datapath 'History0208' for the current month. Could I call this value from the table into a stored procedure? How would i go about doing this....thanks for any help.
I created a new login name. I checked 3 databases in User Mapping. I tried to mimic an existing login name. When I right clicked at the database and selected Properties. I selected the Permission tab at the left. It showed both login names under users or roles. I selected the existing login name and clicked the Effective Permissions button. The login has all permissions: Alter, Create, Select, Insert, Update, Delete, Execute, Connect, etc. When I selected the new login name and clicked the Effective Permission button, the login has only Connect. Why the new login name does not have all other permissions? And how can I assign to it?
We are listing available SQL databases for selection in a VB list box, but need to be able to then elicit the tables in the database for another list. I am sure it is something simple, could someone help me out with this? Thanks!! Laura
[dbo].[PortalUser]( [PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,
...
[dbo].[Role]( [RoleID] [bigint] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1)) ...
[dbo].[PortalUserRole]( [PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL, [PortalUserID] [bigint] NOT NULL, [RoleID] [bigint] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))
I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
I have a drop down list that is populated by a stored procedure which has two int parameters (ID, STATUS). I need to default the drop down list to a value in the database that is associated to the ID & STATUS parameters.
The problem I am having is that it only works if the selected value is the first record on the drop down list. for any other records (outside the first record) from the drop down list i get "The 'SelectedIndex' and 'SelectedValue' attributes are mutually exclusive."
Here is the stored procedure I am using to populate the drop down list:
ALTER PROCEDURE [dbo].[usp_SelectName] ( @pId Int, @pStatus Int ) AS BEGIN IF @pId = -1 --THIS VALUE IS USED AS DEFAULT (NOT POSTBACK)
[Code] ....
Here is the TABLE_NAMES that holds the data for the drop down list:
IN_ID IN_NAMESTATUS 6 New 0 4 Generic 0 3 Local 1 4 Storm 0
This is the TABLE_2 that holds the complete collected FORM data that has the selected record IN_ID from the TABLE_NAME:
ID STATUS IN_ID 111906 114406 114505 114606 114706
Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DASAlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.
The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.I was expecting a clean instance with only the system databases..Is there something that can be set to keep each instance's databases private into itself?
Using SQL Server 2005 under XP Professional. I am attempting to log into the Server Management Studio as "UserX". I had previously been using Server Management while logged in as admin. I now get this error message when attempting to connect to the database server: **********************************************************************TITLE: Connect to Server------------------------------Cannot connect to [**NAME OF SERVER**].------------------------------ADDITIONAL INFORMATION:Login failed for user '[**NAME OF SERVER**].UserX. (Microsoft SQL Server, Error: 18456)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476------------------------------BUTTONS:OK------------------------------ ******************************* Microsoft help doesn't have a specific help for that particular error message. Any hints as to what is going wrong?
Hi! I have a piece of code that connects to the master database and uses the simple 'CREATE DATABASE MYDATABASE' statement to create a new database on an sql server 2005 express instance.
I then close that connection and try to open a new connection using my newly created database. I use integrated authentication in both cases. However, I get the following error: Cannot open database "MYDATABASE" requested by the login. The login failed.Login failed for user 'DOMAINNAMEusername'.System.Data.SqlClient.SqlException: Cannot open database "MYDATABASE" requested by the login. The login failed.
I don't get this error all the time. I tried to run my code many times and it some times work. But in some cases it doesn't. The database is always created but some times I cannot connect to it. But again, if I wait a little and try again with the same database, the connection is made.
I know that my connections are opened and closed normally and they don't remain open. So I don't believe I reach the maximum number of available connections.
I also sometimes get this exception : A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.
I have created an asmx web service that deserializes a JSON string and should execute stored procedures in SQL Server Express 2005, the database is a mdf file. I have created a System DSN which works fine, I have allowed the remote connections and TCP/IP and created the firewall exceptions for sqlserver and sqlbrowser. I believe there's an issue with my connection string, I have tried everything and it still won't connect.
I had another running example but that was on a previous install of Windows XP Pro SP1 with login and this asmx web service worked fine. I run Windows XP Pro SP2 without a login now so I guess I can't use Integrated Security=true or Trusted Connection=yes but does anybody know the default login of a database created in Windows Visual Developer Express?
Here's my connection string:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory| antanplan.mdf;Integrated Security=SSPI;User Instance=false;UID=sa;PWD=sa"
And here's the error:
System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at WebService.pushJsonAction(String req) in C:Documents and SettingsAlbanMy DocumentsVisual Studio 2008WebSitesWebSite1 antanplan.asmx:line 62
We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.
To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.
What I try to do:
Set up a For Each Loop container with Foreach SMO Enumerator Set the connection to my server Set the Enumerate property to "SMOEnumObj[@Name='Databases']/SMOEnumType[@Name='Names']" On the Variable Mapping page, place Index 0 in Variable User::dbName In the For Each Loop, place a script task to msgbox the value of User::dbName
This all works good. The problem comes when I try to nest the For Each Loop
Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator I connect the Msgbox Script Task with the new For each loop Use the same connection Set the EnumURN property in the Expression Builder to "Database[@Name='" + @[User::dbName] +"']/SMOEnumObj[@Name='Views']/SMOEnumType[@Name='Names']" On the Variable Mapping page, place index 0 in Variable User::tabName In the For Each Loop, place a script task to msgbox the value of User::tabName
When I try to run the package now, it does not at all.
In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.
Test 1: I change the DelayValidation for the Inner Loop to True
Now the package runs, but I never get to the script task in the inner loop.
I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?
In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domainx and schema exactly domainx).
I created a DDL Trigger on my DB to find out who and how it is created , but I didn't get any useful information.
I change the owner of schema that I can delete the schema and after that delete the user, and sometimes because the user is owner of a service , I have to alter the owner of the service to DBO and delete the user , but again and again exactly this user and schema (e.g domaninx ) are created as soon as I delete it.
I have read these topics : [URL] .... and [URL] .... but can't solve my problem.
I am trying to add "Remote Desktop Users" as a login group to SQL Server 2005 (i.e. so they can log into management studio). I get a "Not Found Error" (see below). But it is of course found, because I selected it :-) What is going on here? I see the "Administrators" group and various SQL Server groupls added automatically there, so there must be a way, right?
I know I can add each user in the group individually, but that seems overkill when I can just use the group in which they are contained.
Here is the error I get:
TITLE: Microsoft SQL Server Management Studio ------------------------------
Create failed for Login 'TESTSERVERRemote Desktop Users'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in. Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]). Any suggestions/comments would be appreciated. Thank you in advance.
i need to find how many Databases in my local SQL server (programatically).i know i have 8 databases is there but i need to print each and every database.
Is there a stored procedure to list attached databases? You can do this: use Master exec sp_helpfile go
Which shows you the config files for the current DB. What if you want to do a multiple sp_detach_db? If you could get a list of the attached DB's you could recursilvely or conditioinaly detach.
Is there a way I can query the database server (SQL 2000) to get a list of all the available databases? As a threshold problem I am not clear how I would connect to the server. I am using VB.Net and presently connect to one database at a time. The connection string is in an XML file, such as:
Is there a query I can run to retrieve a list of all tables and their sizes in a database? I want something that is like the feature in Enterprise Manager when you click on a database and then the 'Tables & Index' link. It lists the tables and their respective size. I want to push this into a spread sheet.
The reason why I am doing this is the compare data between 2 different databases. Since I cannot find a tool that will compare the data, the closest I can get (without bcp-ing out all data and comparing) is to look at the sizes of each table.
I would like to allow the user of my ADO.NET application to browse through a list of the available databases on a given server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this? TIA, Royce
Is there a way of limiting the "Databases list" to just the databases which I have permission to? My databases is in a hosted environment, and the list of databases on that server is HUGE.
Hi all,I have to write a program to read the names of databases in AnalysisServices. I don't know which table I can get this information from.Thanks a bunch
I have MSDE running on my system and I want to get a list of databasesfor that server programmatically. I'm using vb.net and I want to view,add, delete and modify databases on a server from within a class.I'm aware I can use the "Server Explorer" feature in Visual Studio, butthat isn't what I'm looking for. Any references or suggestions on whereto start are appreciated.TIARalf
