Editing Logins For SQL Users - Cant Do It In Enterprise Manager
Feb 14, 2006
Hi all
I'm using sql enterprise manager to access a database which has two users associated to it called 'dbo' and 'user'. I have a login that uses sql authentication also called 'user2' which has been tested and working fine.
From within enterprise manager, when i look at the list of users for my database, 'dbo' is listed as using the 'user2' login to gain access, but the entry for 'user' has no login name listed and is blank, which should be 'user2'. How can I rectify this?
I have couple sql servers. Users need to login and view tables and stored procedures, for that they use enterprise manager, recently one of the user logged in to one server and accidently stopped another sql server through service manager. How can I prevent user from accessing service manager but still be able to open enterprise manager ?? I would like also if it is possible to hide from user view certain things like dts packages, jobs ,etc Thanks
I am using SQL 2000. In a table (tblTestTable) I have a field VARHCAR(4000). the field stores text taken from a text area in a web page so contains new line characters also length is around 2000 characters.
The problem is that I am unable to edit the text in this field through Enterprise Manager. The text remains as if it is readonly. All I can do is select entire text and cut or delete. :confused: Is there reason for this occurance? How can it beovercome?
Hi,I have a table with about 70k rows. When I use open table --> returnall rows and then edit a field Enterprise Manager then says it has toreturn the entire result set for the update to take place.This can take a long time and makes editing data a slow process. Isthere any way to get Enterprise Manager just to update the one row??Thanks Tom
we have a problem in work where developers modify stored procedures on the fly through enterprise manager. we are trying to prevent this and push them towards using SourceSafe and VS.Net integration when wanting to edit stored procedures - that way we track all changes.
i remember seeing in a company that i worked for a few years ago where they where able to lock stored procedures so that the developer could only view the stored procedure through enterprise manager. similar to when you view a system stored procedure it will change the background colour to grey
how do you go about this? each user is logging into enterprise manager using their windows login.
How to check how many users are connected to a particular database and also where to look what are transactions which are being processed currently by a user
I wanted to create two users in SQL Server to be used by my application. The first would have read only access to all the tables in the database, the second would have read and write access. I tried to use one login for both but when I tried to create the second user in my database I encountered the following error :
Error 15063 : The login already has an account under a different user name.
Do I need to create two separate logins in SQL Server to achieve this? If so, what if I want various users all with different access rights to different tables. Do I need a new login and a new user for each.
hi all , any decribtion about users and logins and differncs between them ?? and any example for real use in real life ?? i use logins user name and passwords in the connection string from c# but how users can make difference with that ??
Ok, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.
Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.
At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...
I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...
Sorry for the novel there and thanks for any help or insight.
In sql2k you used to be able to have a same user in 2 databases under the same login. Just moved to 2005, using sql authentication, and have some users who need to access 2 databases using the same login credentials. But sql2005 will not let me create 2 users under the same login across databases. Any ideas?
I can't figure out what the purpose of having seperate users is as I can't actually login to the database using one.
Here is my scenario.
I have a single login called LoginA and I have a database which I want to carve up using schema's. At the database level I need to create a user, associate a login with this user and can set a default schema and specifiy what schemas this USER can access. The login created can access multiple schemas.
So..
I created a database login called loginA.
I created a user for the database called UserA set it's login name to LoginA and
I then created 3 schemas called SchemaA, SchemaB and SchemaC and set their schema owner name to UserA.
I went back to UserA and set their default Schema to SchemaA
How can I login using the new user created as it has no password associated with it. If I login using LoginA then I have no default Schema set becuase the schema is associated with a USER not a LOGIN.
I can understand why you can only have one login account assicated with one user account for each database but I can;t understand why you can specify a user name if you can't use it to login.
On one of our machines, all of the SQL Server 2000components except for the main Server component (SQL Servercore) itself were installed (Management tools, etc) a while agoand everything was running fine. Now I go and add/install theServer component and then Service Pack 3a.It seems that Service Manager won't start up (I get an hourglass cursor)and now I find that Enterprise Manager won't run as well. No errormessages appeared and I don't think I saw anything unusual inthe log file.However, I can use Enterprise Manager on a differentmachine and connect to the database (so the databaseitself seems to be running).Any suggestions as to what the problem might be and how tofix it? I like to see if I can repair this without havingto do a reinstall.Thanks.PF
IF someone can assist me. Everytime I load up enterprise manager the service manager turns off. And the enterprise manager can't connect to the local database. But everytime i turn it back on and try to connect again it shuts it off and around and around we go. Help would be appreciated. Thanks.
I just restored a database on a new server with a backup(complete backup stored in backup device) from another database on another server using the "with move" option. In fact here was my process:
Alter database ngauge SET SINGLE_USER WITH ROLLBACK IMMEDIATE
restore database ngauge from disk = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPgauge1.BAK' with move 'NGAUGE' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Data.MDF', move 'NGAUGE_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Log.LDF'
Alter database ngauge SET READ_WRITE
it worked.
But it did not move the 98 or so users/logins...from the source database what is going on??? what am I doing wrong??!!
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
Hello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
Sql 2005 I want to grate execute permissions on my stored procedures to a role. While creating the role, it asks for "schemas owned by this role". To me, the schema is merely a namespace that allows you to group objects, but arent schemas such as db_datawriter roles that are central to the db and only admin type users should have ownership of these, correct ?
In a nutshell, I want to: create a new role and assign a user to that role with a stored procedure, grant execute permissions to this role
I was confused by the sql 2005 dialog that asks me to take ownership of roles such as db_datawriter, db_datareader etc, wouldnt that mess up other things with the database ?
help ...
I think its time I review all of the above items role user login schema
Our company has 2 Database Roles (DBE and DBA). The DBE creates database schema, performs SQL Server Administration, and manages server security. The DBA writes data access, ETL, and manages database security. In 2005, we're struggling with how to allow the DBA to see all of the logins on the server in order to add them as users of their database. What permissions does the DBA need to select from any of the logins on the server to add them to their database?
A question on the permissions hierarchy: Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200) declare @object varchar(200) DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR select table_name,table_type from INFORMATION_SCHEMA.TABLES union Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' union select name,'FN' from sys.objects where type_desc like '%FUNCTION' OPEN object_cursor FETCH NEXT FROM object_cursor INTO @name,@object IF @@FETCH_STATUS <> 0 PRINT ' <<None>>' WHILE @@FETCH_STATUS = 0 BEGIN if @object = 'BASE TABLE' begin exec ('drop table ' + @name) end else if @object = 'VIEW' begin exec ('drop view ' + @name) end else if @object = 'SPROC' begin exec ('drop procedure ' + @name) end else if @object = 'FN' begin exec ('drop function ' + @name) end FETCH NEXT FROM object_cursor INTO @name,@object END CLOSE object_cursor DEALLOCATE object_cursor
I created a user on the system with a limited access privilege (Users group member). I need this user to see only specific reports when he login in to the report manager. I gave him a browser role on the report manager and went to the individual report and remove the permission. However, when this user go to the report manager he is not only browsing but also can do whatever the admin can do. I'm just confused. This user is created with a limited access and provided only a browser role, how can he act like an admin?
I installed SQL Server MSDE 2000 but I can't find how to start it. Does this package include an Enterprise Manager or not? I thought that maybe it was a standard XP Pro feature. I had no problem finding it in W2000 Pro.
Has anyone encountered this error: When you are in Enterprise manager and you select a database then the tables tab and then you select a table and go to open table- select all rows, I get an error--An unexpected error happened during this operation-Query Designer encountered a query error! Please let me know if you have seen this. Also, when I access this from one networked computer I get the same error, then when I access it from another I do not!! The servers are interacting with Pivotal. The one that works does not!!
Hi all, I have SQL 7.0 desktop addition installed on my local win NT workstation. I am able to connect to my local server using Quary Analyser but when i am trying to connect using EM, i am not able to connect. I am receiveing the following error message. __________________________________________________ ___________________________ A conncetion could not be established to "servername" - Specified SQL Server not found. ConnectionOpen(CreateFile()). Please verify SQL server is running and check your SQL Server registration properties(by right clicking "servername" node) and try again. __________________________________________________ ___________________________ I have tried as said in the error message and also tried connecting with "sa" but its not working. I can see that the server is running and i am able to connect through QA.
