I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.
The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:
Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.
Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution. I first select the database names into a #temp table which has two columns, ID and dbname. After that I use the following code:
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
I am trying to add a user in every database. So far I have tried using a cursor - Wont work because you cannot issue Use @database_name within the cursor. I have also tried master.dbo.sp_MsForeachdb "exec sp_grantdbaccess 'user'" this does not work either as it addes the user to the first database then doesnt cycle through. I also tried fully qualifying the sp_grantdbaccess with master.dbo.sp_grantdbaccess.
If you have a way of doing this i would appreciate some help.
I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded
i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.
Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?
to further illustrate my query:
Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp
and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp
will Setup 1 be enough if I will use SQL Server as database?
or Setup 2 is better user/database architecture?
This application will be online enrollment for a school with 16,000 students
very much appreciated for the feedbacks and suggestions
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
i have a database which get refreshed every day from client's data . and we need to pull heavy data from them every day as reports . so only selects happens on that database.
we do daily population of some table in some other databases from this daily refreshed DB.
will read uncommitted or NOLOCK with select queries to retrieve data faster.
there will be no dirty read as there are NO DML operation in that database so for SELECT which happens concurrently on these tables , will NOLOCK work?
Had a user created from a local NT account called joe. Used mixed mode athentication. Things are fine.
Later, we needed to add all users from primary domain account (domain2joe), and not have users on all the local servers (joe). Instead central athentication. So I removed the SQL user bill(that was there from local NT account), then went ahead and added the same username, but from a different domain (domain2joe). (same SQL username!) Now when I go into database access to grant user joe access to database1, SQL complains saying that the user already exists. I check the database1 and the user isn't there. BUT, the user bill exists on the SQL role "public" of that database.
I can I add this user and grant access to database1 when SQL thinks the user already exists? How do I remove user from public role?
I have read only user for which I need to give permissions so that he can see the view definition from the management studio by right click - script view as - alter view - copy to clipboard.
Is there any access privilege/rights that can accomplish this.
I have a database instance which of course I can connect to and edit data. I would like to set up other users for this database that can just as easily connect but that only read data?
I'm not in our IT section but am responsible for the running of a finance application on SQL 2000.
I want a Read only user set-up on the SQL DB so that we can use Crystal and Access to look at the tables.
IT have some concerns with this as they say it by passes all security.
Are they spinning me a line?? Surely read oonly is read only? Can a user be restricted to a specific database as there are a number running on the same server.
I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.
I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.
I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.
i'm trying to add a new user to a certain database directly through the users section in this database, but i was given an error that i can't and nothin was added, so i tried adding this user from the Logins section, a pop up message showed saying that this user already exists knowing that it doesn't when i try to create a user from the Logins in a different username it worked but the problem is i can't change the user i must stick with the username that is not working. how can i fix it??
I need to create a new login with SELECT rights so the users can view all tables with no UPDATE, DELETE, OR INSERT rights. But this user needs to be able to CREATE VIEWS. I have assigned the user to the Public role and gone in and modified Securables for the Database to be able to CREATE VIEW. When I connect using my new user and try to create a view, I get the error message: CREATE VIEW permissions denied in database 'test01'.
Hi, I'm able to connect to my db using ASP.NET, but now I need to connect with ASP. I need to add a user like <computer name>ASP( I already have <computer name>ASPNET) using windows authentication to the db. I cannot seem find the way to do this in SQL Server management Studio, because it tells me that it doesn't find that group. Where can I add this user?Thanks a lot.
Hi All,I am having a serious problem of removing and adding again an user in adatabase.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Dec 17 2002 14:22:05Copyright (c) 1988-2003 Microsoft CorporationEnterprise Edition on Windows NT 5.2 (Build 3790: Service PackSomehow, the user was created earlier but was not able to run queryassigned to him. As a result I wanted to drop and recreate the user.I have done many possible things to create the users again with thesame user id but failed.1. I tried to delete this user from the enterprsie manager security-->logins-->user1It deletes but when I try to add again, it gives me error message(Error 15023: user or role u'user1' already exist).2. Then I tried in the db:delete sysusers where name='user1'it deletes the user1.3. Again tried adding, got the message in 1.4. Then I trieduse db1EXEC sp_change_users_login 'Update_One', 'user1', 'user1'Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,Line 88Terminating this procedure. The User name 'user1' is absent or invalid.I also tried master database and ran the following.EXEC sp_droplogin 'user1'The login 'user1' does not exist.But If I try to add the login user1, get the error message.Error 15023: user or role u'user1' already existI also ran the following when I got Ad hoc error messageexecute sp_configure "allow updates",1goreconfigure with overridegoCould you please tell me how I can solve this problem.I do highly appreciate your help.Thanks a million in advance.best regards,mamun
In Ms SQL 2000, I want to be able to share my database with another user butI only want them to be able to look at the database and not be able tochange anything.Does anyone know how I go about doing this?
Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.
I have inserted a new NT user via the Security Manager applet into a group associated with two databases. Now when I select 'Logins' from the Server Manager window and then double-click the newly added user, I receive this message 'Error 21770: The name '<NT username>' was not found in the Users collection.'
Can anyone please explain the above error message.
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
I have an ASP.NET 2 external application that is using and mix of the web services and tables/sps available from my 2005 SSRS.
I want to add an authenticated user from the external application. I don't see a web service that will do this. I have been through the SP's and can create roles, authenticated users, policies etc. But I think I need to update the XmlDescription field in the SecData table.
Do I need to build this XML string from scratch or is there a function / sp / web service that will do this. XML is a long ways from my strong suit.
Or am I just crazy and I should redirect my users to the Security tab!
The current setup is as follows: SQL 2005 64bit v9.0.3054 Windows 2003 SP2
Problem: When trying to add an AD user to SQL, I receive error 15401
Background: The user's last name was recently changed and so all relevant AD fields were appropriately changed: Lastname, Displayname, E-mail, UserLogonname, UserLogonname (pre-Windows 2000). The user's previous email account has not been changed except for additional email addresses to account for the new name and old name. Also note, the uses Exchange alias is still the previous account name.
When I enter the user's new account name into the Login Name field in SQL I recieve the 15401 error. When I do a search for the user's new account name SQL finds the previous account name and not the new one. I have reviewed KN 324321 and that article does not resolve this error. The user account can be found be only using the original account name and not the new one.
On the same SQL server, I opened the security settings for a folder and added the new user's account name and it was found. I then had it search for the user's old login name and it could not find it.
I've created an rdl report in reporting services 2005. Report is working fine. I've deployed this report on SQL Server 2005. The problem is this that to access the reports from client, I need to add client's login ID in Administrator user's group os server. If I dont add them in that group, it shows following error:
"The permissions granted to user 'loginname' are insufficient for performing this operation. (rsAccessDenied)".
This solution works fine in development but in actual environment, I can't add users in that group. Can anyone tell me how to view reports without adding user in administrator group. Its urgent.
What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.
Hi, Apologies in advance if you get confused by reading this.... I am trying to create an additional step in the Create Wizard User Control that is provided by ASP.net. The additional step that I want to add will come after a user will create their username, password, email etc. The information which I want to save in the extra step are details such as firstname, lastname, address, height, weight etc. (I am creating an online weight management system for dieticians).When I run through the application, the username, password etc save perfectly to the database, but nothing happens with the other "personal information". There are no errors thrown so I don't know where the problem is coming from.I have included the code below as I have it:The code behind the Register.aspx file is as follows: <asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" InsertCommand="INSERT INTO [aspnet_UserInformation] ([first_name], [surname], [address1], [address2], [city], [country], [number], [height], [weight]) VALUES (@txtFirstName, @txtSurname, @txtAddress1, @txtAddress2, @txtCity, @txtCountry, @txtNumber, @txtHeight, @txtWeight)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString1.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="txtFirstName" Type="String" ControlID="txtFirstName" PropertyName="Text" /> <asp:ControlParameter Name="txtSurname" Type="String" ControlID="txtSurname" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress1" Type="String" ControlID="txtAddress1" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress2" Type="String" ControlID="txtAddress2" PropertyName="Text" /> <asp:ControlParameter Name="txtCity" Type="String" ControlID="txtCity" PropertyName="Text" /> <asp:ControlParameter Name="txtCountry" Type="String" ControlID="txtCountry" PropertyName="Text" /> <asp:ControlParameter Name="txtNumber" Type="String" ControlID="txtNumber" PropertyName="Text" /> <asp:ControlParameter Name="txtHeight" Type="String" ControlID="txtHeight" PropertyName="Text" /> <asp:ControlParameter Name="txtWeight" Type="String" ControlID="txtWeight" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource> Then the code I have behind the Register.aspx.cs page is: protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e) { TextBox UserName = (TextBox)CreateUserWizard1.FindControl("UserName"); SqlDataSource DataSource = (SqlDataSource)CreateUserWizard1.FindControl("InsertExtraInfo"); MembershipUser User = Membership.GetUser(UserName.Text); //object UserGUID = User.ProviderUserKey; DataSource.InsertParameters.Add("UserId", UserGUID.ToString()); DataSource.Insert(); } I know there is a problem with the code on the aspx.cs page but I cant figure it out. I need the username / password information to relate to the personal details information. I know I have to create a foreign key in the asp_UserInformation table that will link to the username in the asp_Membership (where all the username / password info is stored) Any help will do, I'm almost in tears here!!!The Spud
This is a pretty simple query that tracks 'yesterdays' apply clicks on our website.
SELECT MIN(A.hitDate) AS minDate, D.AffiliateName, B.ProdType, COUNT(DISTINCT A.ipAddress) AS TOTAL FROM ArchiveApplyTracking A LEFT OUTER JOIN Configuration.dbo.Affiliates D ON A.affiliateID = D.AffiliateID LEFT OUTER JOIN Incoming C ON A.campaignID = C.ID LEFT OUTER JOIN Configuration.dbo.ProductType B ON A.productTypeID = B.ProdNo WHERE (A.hitDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE() - 1), 0)) AND (A.hitDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND (A.deal = 1) AND (A.ipAddress NOT IN (SELECT ipAddress FROM [Tracking].[dbo].ipTracking)) AND (B.ProdType NOT LIKE 'refused%') AND (D.AffiliateName LIKE 'ARROW%') AND (B.ProdType NOT LIKE '%prequalify%') GROUP BY D.AffiliateName, B.ProdType ORDER BY D.AffiliateName
No problem there, that's fine.
But how can i put a simple date range interface on my published report so users can say "get me the stats from 04/09/2005 to 20/09/2005, which then runs the query?
