we a re implementing a new HR system and the spec from the supplier says we need to create a SQL user account for each member of staff that is going to use the web interface to update their details (ie everyone in the company).
Luckily i am using VS.Net and can create the script really easily but i am wondering the best way to create the user account. Each one needs to be a datareader and a datawriter in the database. what is the SQL command for this please?
I seem to understand that GRANT is object based rather than for the whole database?
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.
Even Sa account is in sysadmin role it is not able to create users usingenterprise managerAlso not able to create users from local admin who is alo on sysadmin roleBut using query analyzer we can create usersIs some thing wrong with master databaseSanjit
Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel
I know this may sound stupid but I really can't figure out how to do it. I just keep going around in cricles.
I need to create six table under three different users. so I would have users1 owning tableA and tableB. Then I would have users2 owning another copy of tableA and tableB. same for users3.
But I just can't find a way of doing it, I am using enterprise manager. Please help, Ed
I created and successfully tested a trigger on a test database. Now that Iwant to put this on a production system, the create trigger statement takesway too long to complete. I cancelled after a few minutes. The testtrigger took just a second to create. The test and production databases areidentical in design. Only difference is that there are users in theproduction system.Any ideas?Thanks
I need to be able to set up a table listing users permissions, this needs to be run daily and then notify me of any changes without using the auditing or profiler software.
There's something I can't quite figure out about user creating
The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).
Question is how can I allow users to create these user with giving them as few permissions as possible.
If there's is somewhere a code sample on the net I would appreciate the link.
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?
hi..i need help on how to create user logs with a specific transaction he made. for example, he added new item in the database, the grid wud contain user id, date, time and transaction. help po.. tnx!
Situation: If possible, create one stored procedure for Crystal Reports(CR). For any CR users ou there, looking for coding suggestion also. Thank you for your assistance.
Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter which is passed in, allowing it to retrieve one record for the header(report header or RH).
The CR then has 4 subreports in which each has its own stored procedure. This I believe happens because the Report Header records relationship to the subreport is 1 to many. The 3 remaining subreports relationship to the Report Header is also a 1 to many. The main problem is the subreports is that there may or may not have any records based on this, the subreport is suppressed within CR and thus there can be alot of unused white space on the 1st page and one of the subreports prints on a 2nd page when it could have been on the 1st page.
Example:
Main Stored Procedure(sp) RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
Results of subreports and their associated procedure are varchar(8000) decriptions. Each line should be counted in some manner in the stored procedure(sp) and then should be counted in CR to avoid excess white space. To complicate matters subreport 2 to has font, bold, showbox but can have different font sizes. These variations could cause different line space requirements. Any ideas?
Each line should be counted in some manner in the stored procedure and then should be counted in CR. There is a count of records for each the main stored procedure.
RH has 1 record to many records in subreport 1 with same @xyz parameter. RH has 1 record to many records in subreport 2 with same @xyz parameter. RH has 1 record to many records in subreport 3 with same @xyz parameter. RH has 1 record to many reords in subreport 4 with same @xyz parameter.
Because of the relationships, its seems impossible to create one stored procedure which give in one select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought concatinating i.e. 3 records together and then parsing it out some how in CR, along with utilizing the i.e. 3 record count to help count lines. Thought of some how creating a temporary table matrix for 8pt - 28pt for line and spacing considerations.
autorized user: sa any user : nuran temporary table: birtablo
I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo. sa will execute following command:
create table nuran.birtablo (...........)
Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain?
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master] GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/ SET ANSI_NULLS ON GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
- restore a backup of a 3rd party database onto one of our servers - this has no users that I can use - there is some ETL processing so we're using Control-M to manage the process - create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
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!!
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'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
I have moved several SQL 7 databases from one server to another. The databases are attached, and I can see all the tables,etc. However, I can only see 1 user, that being dbo, therefore users cannot log into the db. HELP
