I have noticed that some folks create a user to own a schema, with both having the same name (ex: userowner = pfm schema = pfm) and others make dbo the owner for all their schemas. From what I can tell, it doesn't really matter; what does matter is if your database users are granted access to the schema. Since most users only have public rights and dbo has database owner rights, is there any particular reasoning to use one scenario over the other?
Be gentle, I'm new! :o
We have three databases running on SQL 2000 and would like a visual database schema. We'd rather not do it by hand. ;)
The free-er, the better!
Thanks in advance
Rajendrakumar Varsani
I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)
View 1 Replies View RelatedI would like to limit the role of an user In Visual Studio only to assign roles to other users for the cubes. Other than that the user should not be able to create / delete the exisiting cubes or dimensions.
View 2 Replies View RelatedI am trying to create a database setup script that will work on various OS languages. One of the things the set up script has to do is grant permissions to the NETWORK SERVICE account. This is simple when we are running on the English version of Windows with the command:
Code Block
CREATE USER [NetworkService] WITH LOGIN [NT AUTHORITYNETWORK SERVICE];
Unfortunately, if you run this on a French language version of Windows, the login is called something like [NT AUTORITESERVICE RUSSEAU] or something similar. This causes the script to fail.
I think I should be able to get around this by using the well known SID for this account (S-1-5-20) and SUSER_SNAME() to resolve to the name for the account in the local language.
Here is the code I've written:
Code Block
SET @user = quotename(SUSER_SNAME(0x010100000000000514000000));
SELECT @user;
CREATE USER [NetworkService] FOR LOGIN @user;
Everything seems to be working up to the last line. The SELECT @user line returns [NT AUTHORITYNETWORK SERVICE] on my English language server as expected. Unfortunately, the last line results in a syntax error.
This may be a really dumb question, but I'm not really a DB developer, so I'm stumped. Any ideas on how I could make this work?
Thanks,
Bill
Hi,
how do you create a username and password for a database in SQL.
Thanks
I'm trying to create a user from a powerbuilder application through powerscript.
I'm trying to create first the login using sp_addlogin
but I'm getting the following error
[microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.
Plz does someone know what should I do?
I'm not in our IT section but am responsible for the running of a finance application on SQL 2000.
I want a Read only user set-up on the SQL DB so that we can use Crystal and Access to look at the tables.
IT have some concerns with this as they say it by passes all security.
Are they spinning me a line?? Surely read oonly is read only? Can a user be restricted to a specific database as there are a number running on the same server.
Many thanks for any advice comments.
Tony
i am a newbie in SQL Server. i want to add a new user to my database, whom i want to grant only insert, update, delete, select commands and some procedures/triggers. i need to log on to the database using that user from my asp.net application. i might have several users to log no to the system and for each user i need a set of tables in the databse. i want to wirte the procedures /triggers once for all the users in the database where they can access. i can write the procs under dbo schema, but i what about the tables?
currently i am using SQL Express to test my database. i have Management Studio Express installed on my PC.
S ]-[ /- | ]-[ /- N
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
View 2 Replies View RelatedHello - I am using the generic aspnetdb membership database (aspnet_regsql.exe) which I am currently connecting to as a sysadmin user from my website (via web.config). Before going live with my first website I now want to create a sql user for the website to login with, with the minimum permissions required to run the website - the reason for this is to make the db more secure.
I have created a new sql user and am now looking at what acces they should have. I am OK giving them the required access to the NEW views, sprocs and tables I have created to link in with the generic aspnetdb database etc... however how do I know what access they require to all the BUILT-IN tables, sprocs, views etc. Also, when I am viewing Securables it also brings up all the MASTER DB sprocs, etc.. - will the user need access to these? Should I add the new user to a database role - which one? (currently I have not given the new user a database role) This is the first time I will have created a sql user for an asp.net website so any help greatly appreciated.
Cheers
Marco
hello everybody
I'm trying to create a new user for a database using the enterprise manager but it doesn't ask me for a password
does anybody know why?
thanks for any help.
Hi there,
I am setting up a database for the first time using SQL Server Express.
I have managed to create a database and create a table with data etc, all straight forward.
The problem I am having is when I come to creating a user for the database. I have the following users in there already, which I am guessing are put in by default:
dbo
guest
INFORMATION_SCHEMA
sys
When I try creating a new user via this screen I get the following error:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for User 'growstudiouser'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
'growstudio' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15007&LinkId=20476
------------------------------
I was wondering if anybody could advise me as to what I'm doing wrong. I am typing in the name as "growstudiouser" and the login name as "growstudio".
Or am I just using the wrong section to do what I want. I'm trying to create a user so that when the database is built I can connect to it using a username and password that I have created. I'm not sure I'm taking to the correct route as there is no password option anywhere when creating a new user.
Thanks in advance,
Kevin
Hi,
I have installed Microsoft SQL Server 2005 Express Edition with Advanced Services
When I try to create a new DataBase (*.mdf) in Visual Studio 2005, I get an error " Unable to create user instance. This option has been disabled"
How should I overcome this issue?
When I try to create user using ent manager in mssql2000 it gives me error that there is already such user.But it actualy doesn't exists.
How to solve this problem?
I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it??
Thanks
When creating a user in SQL Server what is a Schema as opposed to a Role?
In the security folder of a database, I also saw the folders "Asymmetric keys", "Certificates", and "symmetric keys". What are these about?
thank you.
Hi all, I'm trying to create a new user and a new db for my website in ms sql server express. I'm creating a new user, but when I try to login, i receive this error:error 18456. I've activated both windows and sql server authenthication. Can anyone help me or give me a link in which is explained how to create a user e configure the access?Thank You!
View 1 Replies View RelatedHi
I am using MS Access project as a frontend and MSDE 2000 as a backend database. I have few users and i want to give every one restricted access to database based on their use.
How can i create user accounts. I checked tools-->Security--> but workgroup admin option is not active. How to solve this puzzle
Thanks in advance
Neha
Hi,I just installed mySQL on MS NT 4.0 workstation and applied Service Pack6a. When trying to add a new user using the Control Center I get aninfo message that say that user was added but this is followed by anerror message that says:There is no such grant defined for user 'username' on host 'hostname'Does anyone know what is going on here?Please respond in email.Thanks,Paul
View 1 Replies View Relatedi login my remote sql server 2005 as 'sa' and then i try the following code:
use master
go
create login [user] with password [user123]
go
yes. that code created me a sql login 'user', but even before i grant any permission to any database, the login manage to acces database: master, tempdb, msdb and one of my application databases' tables and everythings. i like to know what is it i am doin wrong and what are the default access when creating a login, and is it becoz of any configuration in my server ?
any suggestion or answer will be appreciated. thanks
Hi,
Any body please give me some TSQL Stored procedure for
creating an SQL user and to give db access permission to all the databases available on a the server.
Mujeeb
Hi,
How do I create a user that has full access to a database using only SQL statements?
I've create very basic database called "sampledb".
Then I've done:
CREATE LOGIN sampleuser WITH PASSWORD = '123sampleuser', DEFAULT_DATABASE = sampledb;
USE sampledb;
CREATE sampleuser FOR LOGIN sampleuser WITH DEFAULT_SCHEMA = 'db_owner';
Then I logon to SQL server express 2005 management studio using this user, first of all I do not see the sampledb database listed under "databases" even though it is selected in the "available databases" toolbar (how do I list it there? Because going through "attach..." tells me I do not have the privileges to browse for the database to attach).
I then open up a new query (sampledb is selected), and type (products is one of the table in sampledb):
select * from products;
And I get:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Products', database 'sampledb', schema 'dbo'.
Shouldn't the chosen schema (db_owner) give me the right to do this? And how do I fix this from the query window (without going through the GUI, just using SQL statements)? And how do I list sampledb under the databases when I log in as this user?
Thank you!
-Yannick
Hi there,
I want to create database login and set that login to owner of the database? Can anyone help me?
thanks
I'm not a DBA, so I don't know where to look for information on this.
In SQL Server 2005, I can simply no longer create a user for the database I created. It asks that the user be associated with a login. That login is found in the Security folder of the database instance.
The question is: what is this instance security folder for? I don't recall having to do this in SQL Server 2000.
Any elaboration or links to such information would be appreciated.
I have my defined table type created with
CREATE TYPE dbo.MyTableType AS TABLE
(
Name varchar(10) NOT NULL,
ValueDate date NOT NULL,
TenorSize smallint NOT NULL,
TenorUnit char(1) NOT NULL,
Rate float NOT NULL
PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);
and I would like to create a table of this type. From this answer [URL] .... the suggestion was to try
CREATE TABLE dbo.MyNewTable AS dbo.MyTableType
which produced the following error message in my SQL Server Express 2012:
> Incorrect syntax near the keyword 'OF'.
Is this not supported by SQL Server Express? If so, could I create it some other way, for example using `DECLARE`?
Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
Looks like Using Schema in SQL Server 2005 gives enough fucntionality to control persimission on pre-created objects and database. here is my scenario....
I want to have Users that can only create new databases and then have FULL control on that database that they create, like creating table, inserrting, deleting, updating, etc etc.
what will be best steps to have this scene.
(I have created a user and made it a memeber of DBCREATOR, but that user is unable to create any table inside database that is created by that user),
Any help is highly appreciated. I am using SQL 2005
Thanks
How do you create a user instance so that you dont have to be logged on as administrator?
View 5 Replies View RelatedThe requirement is to customize database admin activities by creating new user group.
Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.
This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.
I have created a user in SQL Server Management Studio Express. However,
View 1 Replies View RelatedI am reading kb 934164. I am confused about (creating system administrator) domain user accounts....
IN SQL 2005 USER PROVISIONING Tools
under kb934164 8e type a window account by the following format
domain/user 8k Type a windows account by following format domain/user
DO I simply type domain/user or do I actually Type my domain/user account
What is domain user?
In other words where does domain (PASSWORD) come from?
where does user(PASSWORD) come from?
I have being trying to find the answer for this
Is there anything else I need to be prepared for in user
provisioning. By the way do you need to turn off uac in vista while installing
sql 2005....Thanks Is there any examples of this? I just want to get it right....
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4
Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
Diego Sierra