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.
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 ??
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.
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'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? Thanks
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
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.
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.
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.)
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 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.
