Cannot Add Windows User, Cannot Delete Sql User
Jul 23, 2005
We were using sql authentication on our sql server 2000 servers, But
need to use windows authentication. I have an sql user jdoe that owns
many different objects in multiple databases. I have tried adding the
windows login of jdoe with the exact same permissions as the sql user
jdoe, but it complains because it says jdoe already exists. So I
attempt to delete the sql user of jdoe (remembering the permissions so
I can reapply them to the windows user soon to be created) But it says
"you cannot drop the selected login id because that login id owns
objects in one or more databases" So now I am trying to figure out how
to change the ownership of those many many objects in the many
databases from jdoe to dbo so I can delete this sql user of jdoe. Does
anyone know of a way to convert an sql login to a windows login? Or if
that can't be done, how to change the owner of many objects to dbo so I
can delete this user? Maybe a T-SQL script or stored procedure. There
is a stored procedure called sp_changeobjectowner but that only works
for one object, how would I do that on all the objects that jdoe owns?
As you can see I am a newbie but I appreciate any help you can
provide!!! Thanks in advance!
View 3 Replies
ADVERTISEMENT
May 29, 2007
We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.
In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).
I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.
On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called DomainSqlDevelopers. I used the GUI to verify I could see the domain and the group.
The default database is set to a test database on the server. A user in the test database is mapped to the DomainSqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.
To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.
However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.
The first time I try I will get a timeout error. If I try again will get either:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Or
Cannot generate SSPI context
I can€™t determine any pattern to which of the above errors I get.
However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.
Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.
Thanks,
Jon
View 9 Replies
View Related
Jan 23, 2007
Hi,
I have several reports for users to view on our Intranet. After installation of SQL 2005 SP2 patch, I cannot delete user or user's authority from Report in Properties Tab. An error message was shown on the status bar. It indicated that JavaScript Error: 'Return' statement outside of function. Seems something wrong with the 'Delete' funciton in SQL 2005 after update. The other functions worked fine. Could you point me out how to fix it or need to install any updates / hotfix. Thanks a lot!
Regards,
Kenneth Lai
Programmer
Error Pic
Message Box
View 1 Replies
View Related
Aug 2, 2006
I have a user who cannot login to a .mdb when he is logged
in to Windows, on his workstation or any other.
No one can login to the .mdb when he is logged in to Windows.
He CAN login to the same .mdb if someone else is logged in
to Windows, either on his workstation or any other.
When the user logs in he gets the following error:
MS SQL Server Login
Connection Failed:
SQL
State: €˜28000€™
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
for user €˜domainusername€™
I have removed and recreated the ODBC Connection
while he is logged in. I have tried changing the trusted login to
administrator (in the window that pops up when he fails to connect).
He has the permissions he needs to the .mdb because he can
login under anyone else€™s profile.
I tried copying his profile to a brand new user and ended up
with the same result.
This leads me to wonder if there is some corruption in one
of his profile€™s .dat files or ???
This is the boss€™ account and he does not want to be given a
new username and profile€¦
Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Running on MS Windows 2003 Server Standard SP1
All workstations are XP SP2 with MS Access 2003 SP2 (from
Office Pro distro)
We use roaming profiles.
All systems within the same physical and logical network and domain.
Any help you can provide will be greatly appreciated.
Please let me know if there is any additional information you need.
Thanks,
Mike
View 6 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Sep 4, 2001
I am trying to recreate a user, using an NT account that changed.
First I got rid of the user using sp_dropuser, and this seemed to work. The user has certainly gone from the sysuser table. However when I try and create a new user it throws an error, telling me that the user already exists.
Any ideas?
View 2 Replies
View Related
May 23, 2007
hey, i'm trying to delete everything record that is connected to a user i want to delete..however, i've been getting error due to the fact that the username also serves as foreign keys..is there anyway i can solve this?
View 3 Replies
View Related
Apr 16, 2014
User is login to the application using SQL Server Authentication method. I want to get his windows OS username to log in one of my security trigger.
All of the following:
SUSER_NAME(), SYSTEM_USER AS OSUser, SUSER_SNAME() AS SuserSName, ORIGINAL_LOGIN() As OriginalLogin, USER_NAME() as UserName
Are returning database user name, while i need OS username.
View 1 Replies
View Related
Jun 20, 2008
When attempting SQL Replication I am getting an error that the user I setup for connecting to the Distribution database was unable to connect. I have setup a domain administrator user on our Domain (Active Directory) with the name of SDP. In order to test the SDP login I attempted to connect as DOMAINSDP, and it failed. If I login to Windows (2000) as SDP I can connect to the Server using Windows Authentication without any problem.
Why can I not connect using SQL Server Authentication as DOMAINSDP when I am logged into Windows as kblum? Is there some sort of setting that I need to establish to allow this?
TIA
View 1 Replies
View Related
Jan 2, 2008
How can i add a windows user to sql server to access specific database with read and write permissions.
Thank you very much.
View 1 Replies
View Related
Jul 6, 2007
I've scoured Google searching for an answer that seems like it should beeasy but apparently isn't...when I open SSMS to connect to a SQL 2005database and choose Windows authentication, it greys out the User Namebox...problem is, the server I need to connect to is in another domain...howon earth are you supposed to specify a different Windows user name toconnect with other than the one currently logged into the machine?-Ben--Posted via a free Usenet account from http://www.teranews.com
View 1 Replies
View Related
Oct 27, 2006
I created many test users in my application while trying to get a custom registration process to work, and now I would like to delete those users so they don't use up space in my database. When I go into the aspnet_Users table and try to delete a user, it gives me the following error:-------------------------------- DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__aspnet_Me__UserI__0F975522'. The conflict occurred in database 'DB_83996', table 'aspnet_Membership', column 'UserId'.DELETE FROM [DB_83996].[dbo].[aspnet_Users] WHERE 1=0 OR ( [UserId] = '{7E864A07-6FE8-46EF-A2B8-354646D57C76}' )-------------------------------- I don't know much about SQL statements, so is that statement it gave me the statement I need to use to delete that particular user? If not, how would I go about deleting these users I don't need anymore?Thanks so much. :)
View 2 Replies
View Related
Nov 3, 2014
How can I delete user who own a schema in the database? The user is created by myself but I want this to be deleted.
View 2 Replies
View Related
Apr 30, 2008
Hello I am import a file into a SSIS package. Is there a easy way to delete duplicate user in the file. Could i use a sql task or something.
thanks
View 1 Replies
View Related
Mar 12, 2007
how can we drop an user from sys.server_principals . I did once before , but i forgot the sqls now
thanks
View 1 Replies
View Related
Aug 27, 2007
When trying to delete a user on SQL server 2000, we recieve the message "You cannot drop the selected login ID because that login ID owns objects in one or more databases."
We've tried a number of operations to find the objects owned by the user by searching for this error message in Google but, none of those suggestions work.
My questions are; Why does this happen, How can we avoid it in the future?
Our software applicaiton is case sensistive, and if a SQL security login is not created that matches the Domain login to the method used in the application - somehow we are suck. We can't create a new security for the login, nor can we delete or use the existing one.
View 1 Replies
View Related
Jul 4, 2002
Hi,
How do we make a normal Windows NT user as DBO in Sql server 2000?
Thanx
View 2 Replies
View Related
Apr 9, 2015
I'm almost sure that it's not possible, but I wanted to ask in case that I'm missing something.Is it possible to get the Windows user when someone logins using SQL Server Authentication? I know how to get the hostname, but I can't find how to make the next step.By the way, do sleeping connections affect performance? Would they consume resources such as RAM or network?
View 1 Replies
View Related
Sep 24, 2015
Below is the scenario
I have 2 users.
User1 : have sql rights on database but dont have rights to login into windows
User2 ; have rights to login into system but dont have access rights to database
Now I have windows application and i have logged into system with User2 and want to run windows application with the rights of User1. How can i achieve this.
View 5 Replies
View Related
Jun 21, 2007
Hi,
Is it possible to create a new user into Windows 2003 users table (not Sql) through a sql 2005 query ?
I ask this because I forgot the admin password of the Windows 2003 server and this is in a remote location, so I though, since I have the admin password of SQL 2005 server, maybe it could help me to log onto Windows 2003 and reset windows admin password.
Any help would be eternally appreciated
Regards.
George
View 4 Replies
View Related
Jan 6, 2007
Hi all,
Could somebody please help me out? I'm running Windows 2000 on my laptop. I opened a new user account and gave it administrative privilegies. I copied the administrative profile to the new account and everything seemed to work except when I tried to open any office application a dialogue appeared prompting me to place the Windows 2000 office CD rom in the drive. Why has this happened? How can rectify this problem since I don't have the CD rom.
Thanks in advance for any help.
View 1 Replies
View Related
Mar 30, 2006
Dear all
I would like to ask one sql question , it's that possible to use statement delete my columns ?
select * from tablename where columna = '--delete from tablename '
View 6 Replies
View Related
Jan 3, 2007
Good afternoon,
I have a little trouble with sql server 2005 express database:
customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.
I need delete all user objects from this DB, it must be as new created one.
Is any query whitch can do this?
PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.
Thank's for reply.
View 3 Replies
View Related
Dec 6, 2006
I have a database user (Maximum). Its default schema is db_datareader. It was created without a login name. When I try and delete this user, i get the following error message:
Drop failed for User 'maximum'. (Microsoft.SqlServer.Smo)
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
How do I remove this user?
View 4 Replies
View Related
Dec 10, 2007
Please help, it's urgent!!
We are using sql server 2000 DB,
Sa is trying to delete a user from the Great Plains application and got the follow error:
Deleting the login failed for an unknown reason, contact your sql server administrator for assistance.
View 4 Replies
View Related
Dec 30, 2007
I have a website written in asp.net and the current DATABASE logins do not use windows authenticaton. They are for online use only with a username and password. I'm trying to run my program locally on my machine and access the database locally. This is very frustrating. I'm able to connect to my database, but I can't login to it. Here's my connection string from ym webconfig file:
<add name="sqlConnectionString" providerName="System.Data.SqlClient" connectionString="Connect Timeout=8; Initial Catalog=linkexchanger; Data Source=SCOTSQLEXPRESS; "/>
I have the MSSQL engine running and I have connected with the SERVER. My database is in my directory tree of Microsoft SQL Management Studio Express. The server is SCOTSQLEXPRESSI have attempted to add a login to my sql database by right clicking on the security folder to create a new login. My understanding is that under windows, I don't need a uid and pwd in my connection string . Thus I tried removing all of this just as you see above, and I get an error message every single time I try to create a login with windows authentication.
Can someone please explain how I make this work?
Thank you.
Scot
View 1 Replies
View Related
Jun 18, 2007
Hi
In our sql server we have around 40 windows group.
Say a Windows user = "X"
This X user does not have a direct windows login, he is present in one or more windows groups registered in the sql server.
I need to know throught which group he is logging in.
If I know this , this will help in my auditing process.
Thanks in advance.
Regards
Soorya
View 1 Replies
View Related
Jun 7, 2015
I am setting up security for access of database tables for members in a specific Windows User Group.I want to check in a SQL script if this Windows User group is added and if so, add database users and grant SELECT on specific tables.
I have tried this:
SELECT * FROM master.sys.syslogins WHERE name like '%FoeUsers' AND isntgroup = '1'
but that selects a SQL user or group and not a Windows Group.Is there a way to check if a Windows NT (active Directory) user group exists?
View 5 Replies
View Related
Jun 17, 2007
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Thank you in advance for help.
View 1 Replies
View Related
Mar 4, 2008
We have SQL 2000 Enterprise edition. There are several outside joins to MS Access tables.
I need to find out what would be the best way for me to locate a Windows authenicated users permissions on a table with the SQL database. I cannot seem to find any way to trace this information.
Thank you in advance
View 3 Replies
View Related
Oct 26, 2006
I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:
Windows NT user or group '<user or group>' not found. Check the name again.
I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).
But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?
Not sure if this is possible so i had to ask. c",)
View 7 Replies
View Related
Feb 22, 2008
I wrote a simple SSIS package that runs a batch and a stored proc. They both run fine. I can see the stored procedure run and in process under task manager I see the batch run. However I don't want the batch to run under the System account, how can I change to another user. Thank you.
View 4 Replies
View Related
