Schema/user Details In SQL Server2005

Aug 22, 2006

We are using SQL Server2005 on Windows XP.

My question is:
What is the difference b/w schema, owner and user in SQL Server 2005. The reason for asking is that when I login as a user say 'user1' and create objects under it.. they should show up in its own schema - right. Because this is how Oracle works. Now we are not seeing this behaviour in SQL Server2005. Instead, we see the object created with <dbo>.<object_name>.

Are we missing something? thanks.

View 4 Replies


ADVERTISEMENT

SQL 2012 :: Computer Details Of The User That Is Logged In

Jan 20, 2015

I need to get the computer details of the person that logged in. I have a client that needs me to write a computer asset logging system for them. This is a one table database that will keep all the computers in the company's details like serial number, bios serial number, ram size disk size, mother board info etc. This is a vast number of details and what they want to do is when for instance the ram changes e.g. taken out or added they need the program to update the table with the new information once the computer starts again.

View 4 Replies View Related

SQL Security :: Schema Name Same As User Name

Jun 28, 2015

In some our dotabases I can see Schemas created with the same name as Domain User name (domainusername). Schema owner for those schemas is not dbo but the same user as in schema name. How this happens? Is any way to prevent or prohibit this?

View 9 Replies View Related

Varying Ddl Permissions For A User By Schema

May 18, 2007

Is it possible to set up the permissions to not allow a specific user to create schemas, but to allow that user to create tables and procedures and functions in one schema, and to create procedures and functions but not tables, in a different schema within the same database?



View 3 Replies View Related

SQL Security :: Restrict A User Only To Particular Schema

Sep 23, 2015

I have created a user Finance and I want to grant him access only to see views which are created under Schema called "FinanceQuery".

Note: View may use tables from multiple schemas example: dbo. Staging. ect 

By doing this, I want to achieve that this user Finance can see only Views created under Schema FinanceQuery and should not see any other objects (tables, Stored Procedures, Functions etc.)

View 3 Replies View Related

User Granted Execute On Schema

Aug 8, 2006

I have granted execute on the dbo schema to a sql user so that he can execute available procedures in the dbo schema. However when he executes a procedure ie..

exec dbo.myproc

The following error is returned:

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'MyType', database 'Mine', schema 'dbo'.

MyType is a table

How can I correct this behavior?

I don't want the user to be able to access the tables except via the procedure calls.

View 5 Replies View Related

SQL Security :: How To Set Permissions For A User On X And Y Schema

Oct 22, 2015

I need to provide a User with below permissions:

1. Ability to read and write records in tables in both the X schema and Y schema
2. Ability to read metadata about objects in the X and Y schema
3. Ability to execute stored procedures in the X and Y schema
4. Ability to create and update the necessary schema objects used by X, including but not limited to tables, views, and indexes
5. CREATE FUNCTION permission
6. ALTER and EXECUTE permissions on the X schema
7. VIEW DEFINITION permission on the X and Y schemas to enable view export. 

For the point 1, I will assign db_datareader,db_datawriter database roles to the user

For the point 2, when I have searched web, I found out ReadDefinition permission should be granted. I could find only viewDefinition but not ReadDefinition.

For the point 3, 'USE DataBaseName GRANT EXECUTE TO User; Go' - does this sql suffice?

For the point 4, I am not sure what should be done.

For the point 5, 'USE DataBaseName GRANT CREATE FUNCTION TO User; Go' - I guess this will work

For the point 6, Can I use same SQL as point 3 including ALTER ?

For the point 7, 'USE DataBaseName GRANT VIEW DEFINITION TO User; Go'

View 5 Replies View Related

Discrete User Schema Permissions

Oct 1, 2007



Hi all,
I am trying to establish a "sandbox" database for a group of users/developers whereby each user has their own schema and complete control over their schema and only their schema.

I began by creating user logins (Windows Authentication), created schemas for each user where the corresponding login is the owner, and set the schema as default for the user.

At this point, the logins only had "public" and could not create tables. I then granted the Create Table privilege which allowed them to create a table in their schema. However, they could also create tables anywhere else in the database including another user's schema.
How can I set up an enironment where each user has control over just their schema? What permissions would I need to grant and at what level (database,schema, etc.). I also need them to be able to grant privileges on their own schema and/or schema objects.
Any help would be greatly appreciated.

View 4 Replies View Related

Can Sp_grantdbaccess Attach User To Default Schema

Jun 15, 2007

Hi
For MSSQL 2005 server, when we create new user using stored procedure sp_grantdbaccess, it creates schema with name given to user.

While when we create new user with query " Create login identfied by'password'. It attach user to default schema at the server.


My question is can we have a default schema assigned to the user, while user is created with sp_grantdbaccess?

Is there any way to do so?

Please reply as early as possible.

View 3 Replies View Related

User/schema Problem In SQL Server 2005!

Oct 13, 2006

do the following steps:1:Use Manage Studio login the server with Integrated security.2:Create a dabase named testdb;3:Create a SQL Server login named amber ,and set it to be dbowner oftestdb;4:Create a SQL Server login named guxiaobo ,set it's default databse tobe testbd,and in the testdb databse map login guxiaobo to userguxiaobo;5:Close Manage Studio and reopen it ,this time use login amber log tothe server.6:In database testbd create a databse role role1 owned by dbo;7:In database testdb create a schema schema1 owned by dbo;8:Set user guxiaobo to be member of role1,and set guxiaobo's defaultschema to be schema1;9:In the schema properties-schema1 dialog choose permissions,in theusers or roles listview I add role1 to it.in the explicit permissionsfor role1 listbox,I choose select /update/insert/delete/view definitiongrant checkboxes.and apply the selects.10:In the schema1 schema I create a table:create table a(a int ,bvarchar(10));11:In the dabase role properties-role1 dialog choose securables panel,int securable listview I add the schema1.a table( choose all thecolumns of table a for all permissions),and in explicit permissions forschema1.a I choose all for grant ,then apply the selects.12:Now I use login guxiaobo to log into dabase and issue "select * froma",but got a error msg saying guxiaobo has not enough permission toselect form table a.Does anyone has found anything I missed?

View 6 Replies View Related

Giving A User Permissions On Objects In A Schema

Nov 22, 2006

Hi,

SQL Server Security is not my strong point so forgive me for asking stupid questions.

 

I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.

I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:

GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION

accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)

 

With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.

-Jamie

 

P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.

 

View 5 Replies View Related

Schema Compare Is Dropping User Membership By Itself

Jul 8, 2015

I just recently updated to SSDT 12.0.50512.0 using Visual Studio 2013 Ultimate. I typically use SSDT Schema Compare to synchronize my schema across multiple databases and different environments. After updating i encountered a major bug while updating our production schema.Typically during schema compare, the compare will prompt me to drop users and user roles from the database as they are not present in the project. I will exclude these so they database users and their roles aren't affected. After the update to SSDT I noticed that schema compare was only prompting me to drop the User, but didn't show anything about the user's roles. Not thinking much of it I went through my usual task of updating all the production databases. I soon found out that this did in fact remove the user roles even though it showed NOTHING in the schema compare UI indicating it would do so.

GO
PRINT N'Dropping <unnamed>...';

GO
EXECUTE sp_droprolemember @rolename = N'db_datareader', @membername = N'dbuser';

GO
PRINT N'Dropping <unnamed>...';

GO
EXECUTE sp_droprolemember @rolename = N'db_datawriter', @membername = N'dbuser';

You could say this is partially my fault for not checking the generated script before running it, but after months of this routine task I've never had an issue until this update.i'm not seeing the changes that will happen to my user roles in the schema compare UI? 

View 2 Replies View Related

Set Default Schema To Current User For Call Database

Apr 20, 2013

i have database and set default table schema to "ray" and me must input ruy.TABLE-NAME for retrive data !!! , i need set Default Schema to current user for call database as just database name(for my program) , how changed it ? (i change default schema for current user by alter command but not worked !)

View 1 Replies View Related

Problem In Assigning Default Schema To Database User

May 6, 2008

Dear All,

I am using SQLServer 2005, I have setup a login user "User1" and next I setup database user using the same username and login name. After that I create a new schema "mySchema" and make "User1" as the owner of the schema. To "User1" I assigned the default schema to "mySchema", so far its working fine. But when I open the user's property window (dialogbox) the default schema always gets reset to "dbo".

What could be the problem here? Please help me if there is any solution to get the right schema which I assigned to the user.


Thanks and regards,



View 4 Replies View Related

SQL 2012 :: Database Schema Creation Date And User Who Created It

Nov 12, 2014

Query to find the date/time when a database schema was created and who created it.

View 2 Replies View Related

SQL Server 2014 :: How To Give Permissions To Specific Schema Only For A User

May 20, 2015

I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]

I created then schema [COM] WITH AUTHORIZATION [COM]

I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.

What am I missing?

View 9 Replies View Related

Is It Wrong To Allow User Initiated DDL Commands To Facilitate An Extensible Schema

Jul 20, 2005

I have a client who wants to build a database with a user extensibleschema.The client wants to model an entity with about 100 numeric attributes.As time goes on, more numeric attributes will be added. The clientwants an admin function that will allow him to add these attributeswhen needed.My instinct is that this is wrong and that bad things will happen ifwe build this. However, when I went to write up the reasons, all Icould come up with was that* we would need to write dynamic SQL to handle the changing tablestructure,* that table locking would result from field additions* and that it was "non-standard"One alternative is to create a table containing field-name/valuepairs. This is ugly and breaks the ER modelling rules that tell me yoput attributes of an item into the same row. All the same, I wouldprefer this method to the first approach.This problem does not arise on database projects that follow thepattern of only allowing DDL commands to be issued during the initialdevelopment phase and during subsequent change requests.Have any of you been faced with a similar problem? What should I do?Karen

View 4 Replies View Related

SQL Server 2008 :: Grant User Full Permission Within Schema Scope

Jul 13, 2015

The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.

The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.

My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,

"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.

View 2 Replies View Related

Question About Create Database,login,user,schema And Grant Permissions.

Apr 25, 2007

Hi,



I created a database,login,user and schema like belows.









-- 2. create database
CREATE DATABASE MyTempDatabase;



-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;



-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;



-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;









The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.



How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.



Additionally, what is purppose of the ROLE? Should I create or use it?



I'm confusing in security concept(login,user,schema,role).



Thanks.

View 3 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'.  However, the
current database schema is not compatible with this version.  You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Transferring Objects Form Schema A To Schema B In One Shot....!

May 27, 2008

I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.

I know how to do one by one...!

alter schema SchemaB transfer
SchemaA.TableA

but it will take long time...!

Thanks,

View 3 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET  3.5,  MSSQL 2005I  bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Moving Data From One DB Schema To Another DB Schema Using SSIS

May 8, 2007

Hello,



I would like to use SSIS tool to move the data from one database schema to another database schema.



For example:



Source table has

1. UserName (varchar 20) (no null)

2. Email (varchar 50) (can be null)



Destination table has



1. UserID (uniqueidentifier - GUID)

2. UserName (varchar 50) (no null)

3. EmailAddress (nvarchar 50) (can be null)

4. DateTime



Questions:



1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?

OLE DB Source, OLE DB Destination, Data Converson and .....

How do I insert Guid and Date at the same time?





2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.



Please give me some advices here.



Thanks.

View 3 Replies View Related

Sql Server2005

May 12, 2008

 
hi I have developed one tool which will show the realtime data of memory,processor, disk and Network...these data will change foe every second...
i want to store that data into the SQL server...is there any script to do this?...whether i need to do some scheduling?.....can anyone help me out please?...
Thnks in advance...
 

View 10 Replies View Related

SQL Server2005

May 12, 2008

Hi,
  I have developed one tool in ASP.NET(C#) which will show the realtime data of memory,processor,disk and memory which will refresh for every second...
 i want to store this data into the sql server...is it possible to store the dynamic data?...if not how can i achieve this?...
 evev i want to store the performance of all the remote servers which are in the intranet....is it possible to do?...how to achieve this?.. can anyone help me out...?
 
 
 

View 4 Replies View Related

New At Sql Server2005

Apr 28, 2006

Hello
When i execute the Management Studio Express how can i verify (or define or ...) my server name? (If i choose Browse for servers i can´t find any of them local or network servers)
I have to choose windows authentication or sql server authentication?
Sorry about my basic questions :)

View 1 Replies View Related

DTs In Sql Server2005

Mar 5, 2007

Hello evrybody..........
hope u all r doing fine ...well i hve a little problem that i hve installed sql server2005 express edition on my machine and now i just want to import my clients database which is on Acess but m not getting DTS wizard anywhere in 2005 edition so what should i do....infact i found the exe of dtswizrd in program files microsoft sql server folder but when i hit that exe it throws error that one of the file is could not be loaded it is SQLSVC.RLL file...so what should i do shall i reinstall my package ???and even m not confirmed that the free packageof 2005 which i hve downloaded by download center..wheather microsoft gives the whole package or not???like because here in my package i don't find DTS wizard anywhere like we have in 2000 package....
So does anybody help me Plz?????
hope for a positive reply from all u experts .........
thnks

View 7 Replies View Related

Sql Server2005 Err

Jun 7, 2008

We have SQL Server 2005 on windows 2003 server +SP2. In the application log I noticed the following errors during .

Event Type:Error
Event Source:MSSQLSERVER
Event Category:(3)
Event ID:19019
Date:6/6/2008
Time:10:28:13 PM
User:N/A
Computer:COMP NAME
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4b 4a 00 40 01 00 00 00 KJ.@....
0008: 08 00 00 00 53 00 51 00 ....S.Q.
0010: 4c 00 43 00 4c 00 55 00 L.C.L.U.
0018: 53 00 00 00 00 00 00 00 S.......

When error occur at this time excute a procedure to export table in csv file use bcp and copy this file in Network folder
so what should be error?

View 1 Replies View Related

I Need SQL Server2005 Materials

Oct 18, 2006

hi every body ; Ineed sql server 2005 material 

View 5 Replies View Related

What Is Catalog Name In Sql Server2005?

Apr 22, 2008

Hi,
When i execute this query

SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'a424465';
I can see the..

CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER ...
schema1 schema2


I wanna change the SCHEMA_OWNER to be schema1...

How can be done???
Plz Help me ASAP..

View 1 Replies View Related

Sql Server2005 -Alerts

May 29, 2008

hi
how are the use alerts in sql server2005,
and which alert type used in maximum and how to performaing our database

I would like to get user experiences

View 3 Replies View Related

Replication In SQL Server2005

Aug 20, 2006

Hi there i need an advice on replication in sql2005

i've heared that i can't make replication between two cluster servers installed on them sql2005 and they are in different domains so please can you tell me is that true and if is what can we do to solve this because they are in different places and we can't give them the same domains of IPs

thanks

View 3 Replies View Related

Sql Server2005 And Sql Mobile DB (.sdf)

Feb 21, 2006

hi.

I am too green in sql2005.

so the problem is with data import from csv or txt or access files to sql mobile database.

at internet i fount that tis is wery simple but i can not find how?

so dts wizzard have not this function.

coul someone provide directly steps which i shoud do to import/export data from sdf db

i have vs2005 and sql2005

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved