Schema And Principals (Sql Server 2005)
Aug 29, 2005Hello Everyone,
View 12 Replies
ADVERTISEMENT
HELP - Schema And Security Principals ?
Jan 18, 2008Having a great deal of difficulty finding a good description or exposition on the use of SCHEMAS as it pertains to security principals. I've been working with DBs such as AD and Exchange for several years, and am familiar with the concept and use of SCHEMAS in these contexts.
Specifically ... what and why the necessity to map a (user?) schema to a login/user in SS2k5? Someone please provide a clear (simple ... not too techno-nerd) answer or provide a link to an article/faq/blog/thread where this concept is clearly and FULLY explained.
Thanks ...
Proxy Account Principals Not Saved
Jul 12, 2007I am trying to create a Proxy account to use for Job Scheduling, but when ever I add Logins to the Principals in the Proxy Properties and click OK, when I check again, they are not saved. Is there a reason why this happens? Is there an alternate way to add principals? Thanks.
View 2 Replies View RelatedSQL Server 2005 Schema
Sep 27, 2006A database was created in SQL 2000. We are going to move to sql 2005. So I attached a database to SQL 2005 and now I have a problem with name resolution. When I worked with SQL 2000 I did not put my schema's name before table name (select * from table1). The schema's name is my user name (IQA) and by default the schema name is a name of user who loged in. (in 2000)
Here is a problem with SQL 2005. The schema's name is still IQA. But I need to do select * from IQA.table1.
I created IQA login and IQA user is an onwer my IQA schema but I still can not do a select without schema name. I need to resolve this because VB.Net code has all select statement without schema's name. Need help!!!
Schema Name Has Changed In SQL-server 2005!
Nov 9, 2007I am using sql server 2005 express. When I created a new database my schema name was dbo and my stored procedures looks like this:
dbo.sp_name
Now when I am creating new sp:s my schema name has change to DOMAINUsername:
DOMAINUsername.sp_name
My webb application cant find any sp:s with this schema name and I don't now how to change schema name back to dbo. What has happened, what is this schema name and why has my schema name been changed!? Please help me out!
Information Schema In SQL Server 2005
Sep 6, 2007Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. I have written a small article that exposes the same. Let me know your comments on the same. http://aspalliance.com/1380_Information_Schema_and_SQL_Server_2005
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
Relation Schema SQL Server 2005
Nov 26, 2007Hi Group,(I am just starting with SQL Server 2005.)On SQL Server 7 I used often the nice relation schema, where I used todraw out the whole database, especially the Foreign Keys constraints.I found these relational schemas very handy to study an old database Ibuild a few years earlier that needs some updating.I tried to find such an utility in SQL Server 2005, but cannot find it.I did found the FK-constraints, but it is just an popup where I candefine them.The overview such a visual schema gave was really great and I miss it.Question: Is it gone in SQL Sevrer 2005, or do I just not know where tolook? If the latter, please guide me. :)Thanks for your time.Regards,Erwin Moller
View 1 Replies View RelatedTracking Schema Changes In SQL Server 2005 Db
Jul 11, 2006I wonder how can I track the changes in the columns whether added, deleted or their name is changed and how can I test that?
your help is appreciated
SQL Server 2005 Schema And Security
Aug 7, 2006Hi everyone,
I'm currently investigating the security improvements of SQL Server 2005. I've got some problems with the schemas introduced in SQL 2005 and security settings.
For my test I've created two schemas: UserManagement and Sales. A user "test" is attached to the UserManagement schema. There's a table Sales.Users containing a list of users (varchar) and a stored procedure named UserManagement.AddUser that can be executed by the UserManagement schema (GRANT EXECUTE, so "test" can execute the SP). UserManagement.AddUser simply inserts a new row into Sales.Users.
Because the Sales schema doesn't contain any user, nobody (except the sysadmin, of course) can do a INSERT/SELECT/DELETE in the Sales.Users table. As expected, the following SQL statement fails:
EXECUTE AS LOGIN='machine est';
INSERT INTO Sales.Users VALUES('Test User');
INSERT was not allowed: object 'Users', database 'test', schema 'Sales'.
The second way of inserting rows into Sales.Users is to execute the stored proc UserManagement.AddUser:
CREATE PROCEDURE [UserManagement].[AddUser]
WITH EXECUTE AS CALLER
AS
INSERT INTO Sales.Users VALUES('Test User');
The user "test" can execute this sproc without problems:
EXECUTE AS LOGIN='machine est';
EXECUTE UserManagement.AddUser;
(1 row(s) affected)To my astonishment the INSERT statement inside the stored proc does execute - although UserManagement.AddUser and Sales.Users are two different schemas. Why is that, is there a chaining happening? To my understanding SQL Server should test INSERT rights on Sales.Users for the UserManagement schema and deny the INSERT statement because UserManagement isn't allowed to INSERT in the Sales schema.
Any ideas? Help regarding the issue is greatly appreciated.
Best regards,
Alex
How To Change The Schema Owner In Sql Server 2005?
Apr 22, 2008Hi,
While i trying to drop the user,i getting the following error:
(Microsoft SQL Server, Error: 15138)"
Now,i wanna change the owner of that schema,Which is associated with the schema,which i am trying to delete.
How to transfer the owner?
Plz help me.
User/schema Problem In SQL Server 2005!
Oct 13, 2006do 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 RelatedHow Do I Change The Schema Of SQL Server 2005 Database Diagrams
May 23, 2008Hi all. I'm mapping FKs in a DataWarehouse and I'm wondering if there is a way to change the schema of a database diagram from dbo.
Thanks,
Charlie
Protect Data And Schema SQL SERVER 2005 EXPRESS
Aug 17, 2006
We have a commercial VB.NET winforms client/server application that utilizes SQL Server 2005 express edition. The schema and data that the application utilizes is proprietary and could be very damaging if it got into a competitors hands.
Is there any way to protect the data and schema of a sql server 2005 express edition database?
Will this functionality ever be added?
Thanks
Updating Table Schema From Sql-express To Sql-server In VStudio 2005
Jun 29, 2007During web-site development, I am using VStudio 2005 with SQL-Express. I frequently publish changes to my web server that is running SQL- Server 2000 standard edition.
Is there a simple way to replicate changes in database tables design without copying the whole mdf-file and loosing the existing data in the target database?
thanks, Reinhard
SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.
Jan 11, 2006I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedSQL 2005 Schema Issues
Aug 31, 2007I just upgraded from SQL 2000 to 2005 and have been reading up on the new 2005 schemas and how they can be used to simplify assigning permissions, etc. I started experimenting yesterday with a dummy database and ran into a few issues in which I have questions. Note, I am wanting to use Windows authentication as much as possible where end users will be accessing the database via a mapped Windows group. Based on what I've read thus far on the net this presents an issue in that users who access the database via a mapped windows group are not assigned a default schema.
My concern with migrating to using schemas for protecting objects is the potential for breaking existing applications based on the way objects are referenced in code. Most of the existing code simply references the object only ...such as 'mytable' vice 'dbo.mytable'. If I understand SQL 2005 schemas correctly if only the object is referenced in code (i.e. 'mytable') then SQL 2005 looks in the sys schema first then the dbo schema, etc. in attempt to find the object. Basically it defaults to the dbo schema. But issue I forsee is if I used a custom schema ('MySchema') coupled with users accessing the database via a mapped windows group. This is supposedly where a default schema cannot be assigned ...and since db objects won't reside in the 'dbo' schema the SQL won't be able to locate the referenced objects (I.e. 'mytable') and will in turn break applications?
If my assumptions above are correct then it would appear I have limited choices. Use the 'dbo' schema for everything ...or abandon schemas entirely?
[2005 Express]Add SPs To A Schema?
Oct 30, 2006I have SQL Server 2005 Express set up on a PC. I imported a backup of an MSDE database. All of the database objects are there. However, I need to assign permissions to my Stored Procedures. So, using Management Studio Express, I saw absolutely no way of adding a stored procedure to a schema or to a user. So I downloaded SQL Server 2005 Developer from my MSDN account. I installed the client tools to get the full version of Management Studio. Same thing. No way to add permissions to a SP. Some web articles say to right-click the SP and select Properties. However, there is no "Properties" in the right-click menu!!! There is only Rename, Delete, Modify, etc. How can something so easy with an old product be so difficult with a new one? Does anyone know how to accomplish adding permissions to SPs?I can right-click a table or a View and get the popup window with the list view and contents to set permissions to tables and views.
View 7 Replies View RelatedSQL 2005 Schema Best Practices
Jan 3, 2008Hello,
In general, with the introduction of schemas in 2005, is it considered "bad practice" to tell people to create new tables in the "dbo" schema?
Our product documentation contains a "quick start" guide for users who just want to get the product up and runing. We suggest that the customer creates a database for our application. This database is configured with a user that is assigned to the 'db_owner' role (we want to keep things as simple as possible) that we use to connect to the database. This database will only be used by our application. In this situtation is it okay to use the "dbo" schema, or should we consider creating another schmea for all our tables?
Are the any "best practices" for using schemas in SQL 2005?
Thanks,
Brenden.
SQL 2005 Standard Report Schema Changes
Jan 28, 2008i notice there is a nice little report that is maintaining all schema changes, this is becoming rather large.
Is there a way to tidy up the changes on a weekly basic.
Thanks
Export Schema In SqlServer 2005
Sep 1, 2005Hi all,sorry for the rather trivial question but I couldn't figure this out bymyself.How do you export a db schema either from the commandline or from theGUI (if I remember correctly it's called Management Studio in the newversion).Thanks in advance,Lorenzo
View 2 Replies View RelatedGranting Permissions Using SQL 2005 Schema...
Aug 15, 2007All,
I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.
I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].
I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.
Anyone have any ideas?
Thanks,
Justin
Programmatically Remove Schema From SQL 2005 (C#)
Dec 11, 2007
I NEED HELP! Does anyone know how to remove a database schema from SQL Server 2005 using C#? I have a web application that deletes users. In SQL2k, I am able to simply delete the users. In SQL2005, I must delete or alter the schema BEFORE I can delete the users. Of course, the query below works, but how can I get this to work in code for a web application?
USE EAST_USERS
DROP SCHEMA "plijsmith"
Thanks
Schema Issue - MSSQL 2005
Mar 27, 2008Hi,
In one of the application we recently migrated the database from MSSQL
2000 to MSSQL 2005.
We are using the user sa for our database and when we import data from one table to another ,we
are getting the error as "Ambigoius table name Address".
and in another machine we got this error message.
"The source DB Connection have access to multiple schemas. Restrict the
access to required schema for the user."
It is expecting some schema to be refered since all the database have
'sa' as user in common in MSSQL database.
can anyone show how the jdbc url syntax for including the schema in
MSSQL 2005 for the user sa ?
Thanks
The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.
Sep 27, 2007Locally 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!
Needs A SQL 2005 Data And Schema Compare Tool
Mar 12, 2008Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.
A free tool would be best please..
Thanks in advance.
Gaj
SQL 2005 Security - Schema && Username... Very Annoying
Jun 8, 2007So on my local server I have a username CWI. I have my main DB: CW.
CWI is the owner of 5 schemas on CW, and everything works great.
---
I now go and create a new DataBase called CWTest. I want to now add the user CWI to the security section of CWTest (The same way I did it in 2000).
However, now I get the error message:
"The login already has an account under a different user name."
When I created my DataBase, IT had the default user, but now I want to add another user so I can create my schemas.
---
On our live servers, we will have 100-300 Databases all using the same useraccount as the "God Mode" user.
Any advice?
SQL 2005 Schema Problem After Installing SSIS
Feb 8, 2006I restored a backup from SQL 2000 onto a new SQL 2005 server. The
database restored and the matching user who was the owner of that
database in SQL 2000 was created as a user in SQL 2005 as well as being
designated as the "schema" for that database. I was having
trouble logging in with the user that was created after the restore, so
I created a new user and assinged the new user as DBO of the new
database, set up the new users default database and schema, and
everything was working fine, my database driven application worked
perfectly under that user.
Then I installed SSIS, as it was recomended as a solution to another
problem. After installing SSIS I can not run queries without
specifying a schema name. For example this query will not work,
it returns table not found:
SELECT * FROM <table_name>
However if I specify the schema name like this, the results return:
SELECT * FROM <schema>.<table_name>
This screws up my database application as it does not include the schema name in its transactions.
I do not understand why installing SSIS would have changed this.
Transferring Schema From SQL SVR 2005 To Compact Edition?
Mar 4, 2007I'm building my app in the full blown MS SQL 2005 Standard Edition as our new app will have a enterprise version using SQL 2005 and a desktop version using either SQL Compact Edition 2005, VistaDB3, or maybe even (uggh) SQL Express 2005. I would prefer SQL Express 2005 but the install size of my app + DB would kill sales.
Back to my question. I'm building out the schema in the full SQL 2005, how do I take this schema and get the schema applied to a SQL Compact Edition DB?
Does SQL Compact Edition support DDL?
Thanks.
Transferring Objects Form Schema A To Schema B In One Shot....!
May 27, 2008I 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,
Problem With Using Dynamic SQL To CREATE XML SCHEMA COLLECTION In SQL 2005
Apr 8, 2008This works in SQL Server 2005:CREATE XML SCHEMA COLLECTION Version2_1 AS'<xs:schema xmlns="http://www.icpsr.umich.edu/DDI" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:doc="http://www.icpsr.umich.edu/doc" targetNamespace="http://www.icpsr.umich.edu/DDI" elementFormDefault="qualified" attributeFormDefault="unqualified"> <xs:annotation> <xs:documentation> This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification. This schema is intended for use in producing electronic versions of codebooks for quantitative social science data. CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $ </xs:documentation> </xs:annotation></xs:schema>' This version, however, gives errors: DECLARE @sql VARCHAR(max)SET @sql ='CREATE XML SCHEMA COLLECTION Version2_1 AS <xs:schema xmlns="http://www.icpsr.umich.edu/DDI" xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:doc="http://www.icpsr.umich.edu/doc" targetNamespace="http://www.icpsr.umich.edu/DDI"elementFormDefault="qualified" attributeFormDefault="unqualified"><xs:annotation> <xs:documentation> This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification. This schema is intended for use in producing electronic versions of codebooks for quantitative social science data. CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $ </xs:documentation></xs:annotation></xs:schema>'EXEC (@sql)Ultimately, I am trying to create a stored procedure that will take as parameters the name of the new schema and the .xsd file it comes from, so that I can use it in code. I am a novice, so all explanations would be greatly appreciated.Thanks,Ed Graham
View 3 Replies View RelatedSql 2005 Management Studio And Changing Object's Schema
May 4, 2006 Using a query like
"ALTER SCHEMA TestSchema TRANSFER dbo.table1"
I can easily move "Table1", with schema "dbo", into schema TestSchema. But I can't see how to do it using only the GUI tools provided in Management Studio. Any ideas?
TIA,
Barkingdog
Database Schema Compatible With Schema Version '1'
Apr 12, 2008Hello 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 RelatedMoving Data From One DB Schema To Another DB Schema Using SSIS
May 8, 2007Hello,
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.