I'm not sure why this is not working, a similar process worked in 6.5 I migrated yesterday.
I restore a database backup from another server. I delete all the users in that database under both Users and Roles, as well as under Security/Logins. Then under Security/Logins I recreate three login and try to reassign them to the appropriate Role. I get an error message: "User or Role already exists in current database". I already deleted that user, the only place he exists is in Public role, which is ok. I don't understand I must be doing something wrong?
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
I am trying to create a new user for a SQL Server database and use the credentials in an ASP.NET app.
Problem is dbo permissions are not being applied to the database objects when I set up a new user (Logins -> New Logins) with SQL Server Authentication, set the default database to the database I want.
I then get an error message saying that the user has not been granted access to their default database : DBNAME and therefore will not be able to gain access to their default database.
I'm not sure why this is as I am logged in as sa.
Anyway, when I ignore the error and set-up this new user through the Users section of the database none of the dbo permissions carry over.
Ok I just installed SQl 2005 for the very first time. I used a service account(domain admin)and Windows NT authentication to install SQL.
Now I am setting up Mcafee EPO on a different server. This application can either install MSDE or use a sql database. I want to use my new SQL server.
What are the best practices to : a)create database on SQL b)create a user c)what permissions to give this user
For example during installation of Mcafee EPO it is asking me name of the remote SQL server, authentication method(Windows or SQL)and the user account.
I am starting to read about SQL but need some quick answers. I need to do the same thing for my SMS 2003 installation. Thanks.
Hi, this is a bit of background to what I'm trying to achive: I have an application that creates a new database in MS 2000. The application will then create a login, with then create a user and add the login to the user, it will then add that user to the folling roles; db_owner, db_datareader, db_datawriter. A SQL script is then run to add tables, view and stored procedures to the new database. Most of this works however I'm having a few problems with loging in as the new user. Aslo how do I ensure that if new objects (tables, stored procs etc) are added the new user will have full permissions on them? This is the code I have so far...I'm going round in circles with it and was hoping that someone with fresh eyes might be able to see where I'm going wrong: Many thanks 1 protected string CreateUser(string strDatabaseName) 2 { 3 //database hase alreday been created 4 SQLDMO.SQLServer gSQLServerDMO = new SQLDMO.SQLServer(); 5 6 string serverName = ConfigurationManager.AppSettings["DbsServerName"]; 7 gSQLServerDMO.Connect(serverName, 8 ConfigurationManager.AppSettings["DbsUserName"], 9 ConfigurationManager.AppSettings["DbsUserPwd"]); 10 //get the database object 11 SQLDMO.Database dbs = (SQLDMO.Database)gSQLServerDMO.Databases.Item(strDatabaseName, ""); 12 13 #region create a login object and populate it 14 SQLDMO.Login login = new SQLDMO.Login(); 15 string loginName = strDatabaseName + "_WebLogin"; 16 login.Name = loginName; 17 string password = "password"; 18 19 login.SetPassword("", password); 20 login.Database = strDatabaseName; 21 22 //check if it exists 23 bool found = false; 24 foreach (SQLDMO.Login ologin in gSQLServerDMO.Logins) 25 { 26 if (ologin.Name == loginName) 27 { 28 found = true; 29 break; 30 } 31 } 32 if (!found) 33 gSQLServerDMO.Logins.Add(login); 34 #endregion 35 36 #region create the user 37 SQLDMO.User user = new SQLDMO.User(); 38 //assign the login 39 string userName = strDatabaseName+ "_WebUser"; 40 user.Name = userName; 41 user.Login = loginName; 42 //dbs.Users.Add(user); 43 44 found = false; 45 foreach (SQLDMO.User oUser in dbs.Users) 46 { 47 if (oUser.Name == loginName) 48 { 49 found = true; 50 break; 51 } 52 } 53 if (!found)//add the user to the dbs 54 { 55 dbs.Users.Add(user); 56 57 //add user to role 58 //db_owner 59 //SQLDMO.DatabaseRole oRole = new SQLDMO.DatabaseRole(); 60 //oRole = 61 //oRole.AddMember(loginName); 62 dbs.DatabaseRoles.Item("db_owner").AddMember(userName); 63 dbs.DatabaseRoles.Item("db_datareader").AddMember(userName); 64 dbs.DatabaseRoles.Item("db_datawriter").AddMember(userName); 65 } 66 #endregion 67 68 string connString = "server=" + serverName + ";database=" + strDatabaseName + ";uid=" + userName 69 + ";pwd=" + password + ";";
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name) at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries". now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country} who has an idea how to solve this problem?
I have moved several SQL 7 databases from one server to another. The databases are attached, and I can see all the tables,etc. However, I can only see 1 user, that being dbo, therefore users cannot log into the db. HELP
I'm using SQL 2000 and I'm the database owner. I have been running tests with stored procedures to add logins, revoke logins, add db access, revoke db access etc.
Now I can't seem to add users to the database. I've tried using 'dbo' and 'domainmy_username' but I'm denied permission to run any stored procedures to try and add myself to the db_accessadmin or sysadmin roles.
I've tried the New Database User dialog and choose <new> under login name. I browse for the user name on my domain, click OK, and I get the message 'You must be logged in as 'sa' or a member of sysadmin or securityadmin to perform this operation.'
I thought the dbo always had full permissions on his database. This doesn't make sense to me. :confused:
The only thing I can figure is my permissions have been revoked on the master db.
hi can you tell me how to creat users in MS SQL Server 2005 ?? i tried but i cannot.. my server name is : TOOLSBOXSQLEXPRESS can you guide me to lesson or book talking about how to creat users ?? and can you guide me to book talking about how to creat users and roles ... etc thank you
My database is being moved from SQL 2000 to SQL 2005. Is there a way to copy a User from the old database to the new one, call it a different name and let it have all the same rights as the old one.
I am using sql server express edition. I have centralised database running in windows XP home edtion version. I can connect only 5 clinet computers at a time to the main database. I need to connect 8 clients to the main database. Each client is running with windows xp home edition. Thanks in advance
Hello, I am new to this forum, and ASP .NET 2.0. I watched the tutorial on how to use the login control with SQL Server 2005 Express, and was able to duplicate it easily. However, I tried to see if I could do the same thing with SQL Server 2005 that is hosted by my hosting company, and I was unable to get it to work. I am using Visual Web Developer 2005 Express, at least until I get VS 2005 in a few weeks, and I am able to connect to the SQL Server. I tried following the tutorial on this site step by step, and some of the things they used to set it up I could not use. In the tutorial VWD creates the table automatically. If I use the hosted database, do I need to create the table, or is there a way for VWD to do it that I haven't figured out yet. Any help and additional information that I might need to know would be great. Thank you for your help. Ben
I recently moved my .NET project out to production, the server is Windows 2003 running SQL 2005. My developement server was a Windows XP running SQL 2005. Here is my issue. Under development I created a specific user to log in as, created a role and specified exactly what application specific stored procedures etc. that user was allowed access to. Everything worked fine. Backing that database up and restoring it to the new server, when I attempt to log in as the user (and yes I re-created the login, with the user, just as I did on the development server), I get this error... EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'MoldKit', schema 'dbo'. So I attempt to login as SA, and everything works... so I assign the db_owner role to my user and login, everything works... problem being they shouldn't be db_owner. And they're most definitely not assigned this role on development. I even went so far as to delete the database, and recreate it from scratch using the aspnet_regsql.exe and I get the same error. Anyone have any idea why this is happening, or the proper roles I need to assign so the user isn't a dbo, but can still call all the required aspnet procedures. Or could this be some other issue caused by 2003? I've seen this error in a lot of posts, but never once saw a definiative answer to what causes it and and what the fix is.
Hi there, When conecting to a MS SQL Server 2005, I could define in my connection string Windows Authentication. What account it will be used? The one used by IIS (I still don't know the user ASP.net runs, if it is independent of IIS or not)? And if I whant that some aspx pages have diferente acess rights? I need to use SQL Server Authentication with diferent users created in MS SQL Server right? In ASP.net, what is best practice: Have one SQL account, and make users access rights in Business Logic, or have different SQL Accounts por the diferent access rights I whant in every page? Sorry if this are basic questions, but In the find's I made, I saw very confusion ideas. Thanks in advance, and sorry my english. Alberto Ferreira
Hi All, Probably this is an easy question to many of you, but I'm not quite sure. I have imported a database to my local, and there are a lot of (system) users attached to it. My goal is to update the password for the system user. I have researched for a while, I find a way to update the Master.. sysxlogin. So I wonder it is ALWAYS the users who have added to individul databases, their username will always in the Master..sysxlogin table? As I have imported the database from my client, so the username are not in the sysxlogin table. Thank you for your help.
