Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.
Thanks, Jim
How can you manually update the syslogins table in SQL 2000? It keeps giving me the following error:
Server: Msg 4406, Level 16, State 2, Line 1
Update or insert of view or function 'master..syslogins' failed because it contains a derived or constant field.
I used to be able to do it in 6.5.
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
In a recent Disaster Recovery TEST, I successfully recovered some SQL Server 6.5 databases by re-installing SQL Server, applying the SP5a, restoring the application database, and then BCPing IN the syslogins. Everything went normal, as I have done this many times.
I had one SQL 7 to recover. I used the same procedures, however, the BCP in did not work. I ended up getting a Dr. Watson message (not sure if it was related to the BCP or not). Luckily I had the Logins scripted and was able to apply them as follows:
USE master
go
EXEC sp_addlogin 'user1',
0x21312144332c534025532f3a442d5e4c,
@defdb = 'DBNAME',
@deflanguage = 'us_english',
@sid = 0xf8ddfa809a05d411a56600508ba705cb,
@encryptopt = 'skip_encryption'
go
This seemed to work, and luckily there weren't many users in this database.
How do you get the syslogins in for SQL 7?
Thanks,
Carl
We are looking to migrate an entire server with several databases which collectively contain every user in the company. Obviously, I would prefer to directly migrate the logins without having to make everyone reset their own passwords. Anyone know of a way to do this?
I tried dts'ing the syslogins table but it fails due to password collumn being read only.
hi,
I created a new 6.5 sql server. I brought all the logins from the old
server. I bcp out the syslogins from the old server and bcp into the new
server. There were no errors from the bcp in. Somehow, I still can't
login to the new server using the same password from the old server.
Why?
how can I fix it?
thanks
my problem is : I want to export syslogins from database SQL6.5 to syslogins SQL7.0.
I use BCP (command : mssqlBCP master..syslogins out d:empsyslogins.dat -c -Sservername -Usa -P -ed:emplogins.err)
It runs well.
AFTER to export to SQL7 database :
(command : mssql7BCP master..syslogins in d:empsyslogins.dat -c -SNEWservername -Usa -P -ed:emplogins.err)
I always have DR WATSON.
If someone has encountered this problem and solved it.
Your help will be strongly appreciate.
Patrick
Is it possible to have a two way replication of the Logins and Users between two servers? What we have is two SQL 7.0 servers, for an CRM package. They use the CRM replication program to move data back and forth. However, we need to keep the logins and passwords between the two machines ther same. Any thoughts on how to do this?
View 1 Replies View RelatedJust installed SQL Server 2000 Beta 2. I have a procedure that selects the suid from the syslogins table from a SQL 7.0 version that is now saying that column is not valid.
All the documentation I can find still refers to that being a valid column.
Any suggestions?
Pls help me out with this query as
select * from master..syslogins WHERE hasaccess = 1
as it is both giving me record where hasaccess = 0 and 1
brgds
I've got a tool that accesses syslogins to pick up some information.When I run the tool, I get the error message that sasys that my logindoes not have sufficient permissions to read syslogins. If I run undermy admin ID, everything is fine. HOWEVER, the intention is that thistool will be used by non-DBA staff members so we have a generic idcreated to run this tool specifically (non-DBA account).OK, so I go to Enterprise Manager and open up the master database, goto syslogins and add my non-DBA id to the permissions list as havingSELECT access, and click on EXECUTE.BOOM! No sign of my permissions being set.Am I missing something? If so, what do I need to do to set permissionsagainst this view?
View 1 Replies View RelatedHello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
Hello EveryoneI am in the process of creating a web application that will allow me to have one place to go to see all of my SQL Databases. I would like to have one of the pages that showed the current status, or maybe next to the name of the server, have the status listed.I know how to create a database connection string using the web.config But I will need to have a query that loops thru a list of database server names, and each of them returning the status. Being able to connect, query the status of the server, show the status of each database. My database connection string will only log into one database. How can I code this to be able to loop thru the list of stored database server names, and then be able to log into each database server?Can I use a separate class with a method, and pass in the name of the server? That means that I would have to have the same user name on all the database servers, wouldn't I ?I am looking for a way to accomplish this. I am open to suggestions. I am coding my web app using C#. ThanksAndrew SQLDBA
View 2 Replies View RelatedHi
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.
Has anyone got any ideas?
Thanks
When I a set of databases from backup all login information is lost. The front end of the application provides a way to create new users, but it is a very long process. I am looking for a script or stored procedure that will help attach the users to the database.
View 3 Replies View RelatedHi,
We are running MSSQL 2000 on a reasonably powerful dedicated Win2k system Dual 2.4 Xeon Procs, 2 GB of RAM, Ultra 160 SCSI disks.. blah blah blah.
Our primary use this database server is for our Coldfusion Application servers. We host about 50 or so databases.
Of late, we have been experiencing random failed logins for certain databases. These failed logins are logged to the event log on the server. They randomly; queries executed against a datasource configured on our application servers trying to get data from the database fail with a login failure when other times they will succeed. I am confused about this issue. Could an issue where the SQL Server is overloaded cause a login failure for a database?
Errol Neal
Hi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
Bye,
Madhavi
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
I use detach/attach method to move database to a new SQL Server 7 on MSCS. The security information was lost in the new server. Database Access permission was gone and I failed to select it again because message showed the database role is already exist. The default databases were also posted incorrectly. Would you please to tell me how to fix this type of peoblem or let me know how to move the login info such as login id, password, database permisson to the new server which locates in a different machine.
Thank you very much.
Rich
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I am working on a Login migration issue. I have the Master database from the original database, that I have attached as a User Database, call it OriginalMaster. I also renamed the mdf and ldf files, before attaching, just in case.
I want to select the logins from the OriginalMaster, and using sp_help_revlogin script all the Logins out. I know there were many logins in that database. But what I am seeing, are all the Logins from the Current Master database. Which are only 7 Logins.
How can I select the logins, or script out the Logins from the OriginalMaster database?
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?
Michelle
Hi there,
Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.
Any help is appreciated. Many thanks!
what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?
View 3 Replies View RelatedI am in the process of implementing database mirroring. I am trying to get an understanding about manageable ways of keeping sql user logins synced between instances.
I have looked at KB918992 which makes sense for the initial intallation, but what about keeping passwords synced in the long-term. I don't want to have to manually set a users password on the failover every time they change it.
How is everyone else accomplishing this using sql logins (not windows auth)? Pointers to articles and/or documentation would be great.
Thanks.
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
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?
Thanks for your time..
Bidyut
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
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">Friends,</g>
<g class="gr_ gr_78 gr-alert gr_gramm Punctuation replaceWithoutSep" data-gr-id="78" id="78">I</g>
i am migrating one database from SQL Server 2008 where I have around 20 databases to SQLServer 2012. To migrate the login I was thinking about to use SP_Help <g class="gr_ gr_114 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="114" id="114">revlogin</g> store procedure but now what I believe is this SP scripts out whole server level login and I don't need all the logins in the server except related to the database that I am migrating to.
<g class="gr_ gr_418 gr-alert gr_gramm Grammar multiReplace" data-gr-id="418" id="418">please</g>
best way to migrate logins only related to the specific database to the new server.
We log ship our production database tansaction logs to a secondary database on a separate remote domain.
Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
How do you make Asp.net use something other than the local ASPNET user for data access in sql server
View 3 Replies View RelatedI need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.