Generate Script For Current Sql Server Login And Database Users
Dec 12, 2007
Hello Everyone
How do you generate a script for all the current sql server logins and generate a script for database users for each database.? You can script operaoers, tables, databases, and a lot of other objects by using the “All Tasks” shortcut menu option, but I haven’t figured out to script logins and database users. Any help would be greatly appreciated.
I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?
I have a server that has 20 databases . I have tested with few users with different level of access and all of them were able to connect to the server and also see, select, update , delete from a particular database which is kind of weird because they do not have a user login associated or mapped to that database. I checked and no user is part of any group in AD that would give them permission to connect . I need a query that would find the permission path of a user. I already queried with xp_logininfo but I am not getting any thing.
I am in a situation where I would like to use a SQL login instead of adding individuals windows login to the server. Is there a way to force a login instead of having the report server not give rights at all??
We build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
Have just tried my sql server 2005 connected asp classic website on II7 for the first time and have gotten some errors. First off I have unchecked show friendly errors in IE and have enabled ASP to send error messages to the browser.
My error I get is when I execute a basic .asp page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "mydatabasename" requested by the login. The login failed. /index.asp, line 10 which is dbConnect.Open cstring
from this peice of code:
sub dbOpen() set dbConnect = Server.CreateObject("ADODB.Connection") cstring = "DSN=mysqldsn; Trusted Connection=yes" dbConnect.Open cstring end sub
I have gone into ODBC and have setup both a user dsn and file dsn that equals mysqldsn and points to my database (I know you only need to set up one DSN but I'm not sure which)
I also notice under mywebsite home panel there is a connection string option. Do I do anything with that?
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!!
Hi, Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword") Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn) Line 15: objConn.Open() Line 16: objRdr = objCmd.ExecuteReader() Line 17: While objRdr.Read()
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
I recently attempted to change the password of the sa login. There were no issues while updating the password neither did I get an error when I logged in as sa. Everything appeared to connect and I was able to query a database. However, an error was logged (error 18456 severity 14 state 8) as though I did not log in successfully. Even though I can connect successfully, I don't like having these misleading errors in the SQL Server Log.
I restore a database on a different server from a device file. This database has a user defined for it. In this case, 'privuser' is the name. No login has been defined.
I can add the privuser login but cannot grant db access because privuser already exists in the db. I cannot drop 'privuser' because the user owns all the user objects in the db.
How can I make the privuser login I create access the db using the privuser username in the db? Would having the login created before restoring the db as a new db work?
can there be multiple users on one single sql login?
I keep getting errors from my users randomly about the server timing out and i am wondering if they are trying to submit append queries at the same time.
I am new to this, so, I will be greatful to anyone who can clarify the following:
qn 1: In login properties under Logins of Security, what does creating mapping to the different databases mean ?
For a given login, I notice that I can login / execute DDL statements alike for the databases which are checked and also for the ones that are not checked.
Then, what does Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login -> checking the database - accomplish?
qn 2: For a perticular login, say 'Bob' which has server admin rights, I wish to include two more databases. For this I 'checked' the databases, which I wish to include, in the
Security -> Logins - > Login Properties -> User mapping -> Users mapped to this login
When I clicked 'OK', I get the following error and the two databases are left unchecked.
User, group, or role 'SQL1AGENT' already exists in the current database. (.Net SqlClient Data Provider)
I am getting the error: Cannot open database "aspnetdb" requested by the login. The login failed. When I browse to my ASP.NET 3.5 LINQ web application on the IIS 6.0 server on Server 2003. I imagine this is because while I granted SQL Server 2005 login and permissions to my database that the application stores its data in, I did NOT grant any rights to the service account the IIS Application Pool uses for its identity to the aspnetdb database on SQL Server which is where all my roles information is stored at. My question is what are the MINIMUM permissions needed for this database so it can perform its roles related functions? I'm using Windows Authentications with the SQL Role provider for authorization.
Thank you.
EDIT: I think I only need to open the aspnetdb database and add my login to the aspnet_Roles_FullAccess role. Is that correct?
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
I made a backup of a production database and copied that backup over to a development server and restored the database. Now I have users saying that the application is failing on development. I have users that have NT authentication and some with SQL authentication.
What is the best way to get everything to sync up again?????
I have a few db's that I am backing up from an old sql2000 system and moving them to the new sql2005 server. The first db's restored without any problems but now I am getting some problems with users and logins.
I am restoring in the following order
1.) Creating empty db "123" 2.) Restoring database from file to database "123" 3.) Creating Login to this database (error happens when linking it to the database, "user exists")
When connecting thru QA I get "Cannot open user default database login failed"
I only have 1 login per database and I don't mind manually deleting and recreating them all but it doesnt seem to be working :S
I'm trying to set up my reports with parameters, to select the paramater by the users login. For example, if someone from the Northwest Region accesses Report mangers and then a report. Instead of them clicking the drop down, for the report parameter. The report automatically generates from the login that gave them access to report manager. So it diplays the information for the Northwest Region.
I know how to do this for Data Driven subscriptions.Such as creating a table , with Region, login, email, and all the information relevant. But how do i do this for users accessing the site theirselves? Is this possible?
I have to give three users read access to a database.  My manager told me to map them to a User called "zxDatabase.Read.ug". I scripted the User and got this...
I don't see a Login at the server level named zxDatabase.Read.ug, so this is confusing. Where is this login in SSMS?
How do I map my three users to user zxDatabase.Read.ug. Or am I not understanding correctly? Do I just add there windows login to the database as Users and configure them the same as zxDatabase.Read.ug
<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>
I tried to research on the internet and i got a solution on changing the permissions for this database to enable user SystemName/ASPNET, but iam not able to access this ASPNETDB.MDF from SqlServer and if i go to server explorer in vs2005, i dint know where to chage the permissions.
I need an SQL string that inserts the current date into a database. So far I have tried: SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')" mycomm = New SqlCommand(sql, myconn) mycomm.ExecuteNonQuery() However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
Hi all--I am writing a DTS package looking to back up all databases in sequence from a specific instance of SQL Server. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of SQL tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure of a particular database to back up to file, execute the following SQL task:
SELECT @srvname = @@servername select @db_name = db_name() set @msg = 'Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed. Thanks--SQL Server';
SELECT @srvname, @db_name, @msg
(Many thanks to Whitney Weaver and SQL_Menace for help with sections of this code)
Upon success of #1 and/or #2, go on to the next database, repeating cycle of the SQL tasks above for the next database in the task. For example, this logic goes like:
1. Back up master database; if fails, send email in SQL task; if either task is successful, goto database #2;
2. Back up model database; ...<etc>.
My problem is the current database set in @db_name. I apparently need to reset this variable for each database that gets backed up, and I can't seem to get these set as values to be passed from SQL Task #1 to SQL Task #2. Any suggestions on how I might achieve this?
Is SQL Server sensitive to Domain group name? Like "Domain Admin"?
I have user that belong to "myDomainDomain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login.Â
SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
