Associate User's New Objects With Data File
Apr 20, 2007
I'm currently running SQL Server 2005, and have been exploring the feasibility of grouping users to use a default file group and data file. This would be to group a set of users to have their new tables reside in a specific data file. I only see mechanisms for moving an already existing objects into a file group or to set the entire databases default file group--not a user level association or dynamic assignment of the default for that user, script, etc.
It's not looking good.
I'm looking for confimration that it's not possible or leads on doing so.
Thanx in advance
View 1 Replies
ADVERTISEMENT
Nov 22, 2004
Hi all, I've had this issue in the past but dealt with it in a way that I don't think is correct:
Let's say I have a database on Server1. On Server1 I create a login and a user on the database. Now I have User1 with a login authorization on Server1 as well as a user within my database. If I detach that database now, and re-attach it on a different server, User1 exists. Of course, on the new Server2 User1 does not have authorization to login. I've deleted the existing user in the database and then re-created a login on the server, cascading through with the GUI setup to re-create the user within the database, too. Of course this gets ugly if the user has objects within the database. If I create a server login with the same name as the database user the login is not associated with the user too, correct?
I'd like to know the way that I can create a server authorization tied to the existing database user without dropping that database user, preferably within Enterprise Manager.
Thanks all,
MC
View 1 Replies
View Related
Aug 21, 2006
i begin for Data mining ( analysis manager - sql server 2000)
i create some mining model with Microsoft_decision_ trees or Clustering. it's oK
but now i want to create a model to know "If customer 1 has product A in their basket, what products should I recommend ".
i read and see that "Microsoft analysis manager sp1" have support association rules.
i install pack 4. but i don't see anything else . how i create a model with association rules.
View 3 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Aug 27, 2004
Is there anyway, that I can generate a list of all the objects that a given user has access to (including type of access whether select or update etc), by running a SQL command? One of our databases has nearly 40,000 tables (no kidding!) I can always find this out by manually looking into the roles etc on the enterprise manager, but I need a way to find out using a T-SQL query..Thanks for any help!
View 2 Replies
View Related
Oct 17, 2005
I remembered there is an option I can use to display just user definedobjects in SQL Server Enterprise Manager, but I can not find it anymore?Would you like to tell me? I really appreciate it.Laurence*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jan 3, 2007
Good afternoon,
I have a little trouble with sql server 2005 express database:
customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.
I need delete all user objects from this DB, it must be as new created one.
Is any query whitch can do this?
PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.
Thank's for reply.
View 3 Replies
View Related
Aug 12, 1998
If I want to easily and quickly grant all permissions to a group of objects
for one user (or group), how can I do this with the tools provided?
In 6.5, I could right-click the user or group and click the "Grant All" button.
In 7.0 this is either missing or has been moved.
Thanks!
Jim
View 1 Replies
View Related
Jan 29, 2002
Usually all the user datatypes in our databases have the owner dbo.
One has a few that are owned by a user with dbo rights. I am trying to change them to dbo owner.
Sp_changeobjectowner gives 'object does not exist'
Any ideas
Jim
View 1 Replies
View Related
Oct 7, 2015
I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...
View 4 Replies
View Related
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
Mar 24, 2008
hi all
i am using sysobjects for taking the list of user objects.
by using userid, status column combinations, able to take out the user tables/triggers/views
but for stored procedures, it doesn't work.
is there any better way to list the user objects.
thanks in advance.
sam alex
View 3 Replies
View Related
Mar 15, 2004
We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:
Error: the file PRADATA4 cannot be removed because it is
not empty.
The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)
2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO
Can anyone help?
Thanks
View 8 Replies
View Related
Mar 17, 2000
Does anyone know the logic that Enterprise Manager/SQL Server uses to distinguish system objects from user objects. I've looked through all the sysobjects in the database and there doesn't seem to be any unique logic for seperating all objects. User tables are easily distinguished from system tables (U/S) but thats not the case with stored procedures and views. I'm looking for suggestions...
Thanks,
Adam
View 1 Replies
View Related
Jul 20, 2005
How would I, using a sql script, copy permissions assigned to a useror a role in one or more databases to another user or a role in theirrespective databases?Help appreciated
View 2 Replies
View Related
Dec 4, 2007
Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc:
INSERT INTO [dbo].[ProductionRequirementDetail]
([ProductionRequirementHeaderID], [SKU], [Quantity])
SELECT @ProductionRequirementHeaderID,
[SKU],
[LAMPS]
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL
LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU])
WHERE [SKU] IS NOT NULL
AND [CODE] IS NOT NULL
AND [LAMPS] IS NOT NULL
AND [LAMPS] > 0
AND [LampTypeID] = @LampTypeID
I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.
View 8 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
May 8, 2008
Hi. We're trying to setup a new case management system and I'm having a problem trying to store some of the information. I think I know how to go about it; however, I'm not sure if it's right.
We have two tables: Cases and Clients. The cases table stores relevant case information like case number, case type, etc. The Clients table stores information like name, address, phone number, email, etc.
What is the best way to associate these two tables together? I'm assuming I need a third table. Right? Thanks!
View 7 Replies
View Related
Dec 28, 2006
Hi all.Running SQL2K SP4 on W2K3 Standard, SP4.I have just refreshed a database on one server with a backup fromanother. The database had existed previously on the target server, andI am just refreshing its contents. I used the following approach1) From the target server, create a SQL script with users and roles2) From the source server, back up the db3) Transfer the file4) Restore the db, checking the location of the data and log files toensure correctness5) Remove orphaned users6) Run in the users/roles script generated in step 17) Run in a canned script containing object-level grants.I've done this dozens of times in other databases, but something alittle unusual has occurred here:My dbo user is orphaned. Normally it's associated with the 'sa' login,but at this point there is no associated user.I can see why this has happened - on the source db, the dbo user hasbeen associated with a non-default login.And, many of the objects in the db are owned by dbo.The only viable option I see is to change the ownership of theseobjects, drop dbo, re-add it with the association to sa, and reassertthe object-level grants.But what I would prefer is some way to change the association of thedbo user, to associate it with 'sa' without the need to drop andrecreate.Can anyone suggest an alternative strategy to associate a login and auser after-the-fact?Thanks much for all input!BD
View 4 Replies
View Related
Nov 6, 2006
I'm making a copy of some tanles between 2 servers.
Server 1 requires a sql login
Server 2 is using Windows Auth.
I have a user on server 1 named "odbc" able to log in.
however my copy task fails, when I drill the error, it's lists the first user in server 1 alphabetically as the failed login???? but in my dts I am specifying the "odbc" user and password.
I think I have a permissions problem on server 1. So my Question, what minimum permissions does user "odbc" need to copy a table?
On server 1 I can copy from northwind to server 2 just fine..but any other db on server 1 causes the weird failure with the wrong username.
Any Ideas? I am not a DBA obviously :)
Thanks,
Carl
View 1 Replies
View Related
Jun 22, 2007
Hi!
There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):
Code Snippet
CREATE VIEW [dbo].[ViewREPL_PublishedObjects]
AS
SELECT TOP 100 PERCENT
CASE [xtype]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE NULL END AS [Object Type],
[name] AS [Object Name],
CASE WHEN [replinfo] = 0
THEN 0 ELSE 1
END AS [Replicated]
FROM [sysobjects]
WHERE
[xtype] in ('U', 'V')
AND [status] > 0
ORDER BY
(CASE [xtype]
WHEN 'U' THEN 1
WHEN 'V' THEN 2
ELSE 10
END),
[name]
Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].
So, I need a view with same functionality, that will work under SQL-2005 and 2008.
Please, help!
View 2 Replies
View Related
Mar 23, 2007
(Spam Removed)
View 1 Replies
View Related
Aug 16, 2007
Hi there,
I'm wondering if there is a way to associate a job or task of some kind (i.e. SQL Agent job or SSIS package) with the generation of a report?
I have a need to provide an on-demand report that somehow initiates the updating of its data source prior to generation. In other words, I'd like the report to do *something* that can start an external process (external to the report) and wait for that process to complete before proceeding with the report generation.
As you can probably tell, updating the data source is expensive. It is normally scheduled along with a scheduled report, but I'd like the report to be runnable on demand when that is (rarely) the need, and I'd also like to guarantee the data source is updated when the scheduled report runs, which I can only do if I can somhow create a dependency between the updating process and the report generation.
Any ideas?
(The only way I can see to execute code synchronously from the report is to build a custom data processing extension/provider, which is fairly absurd.)
Thanks
Sam
View 2 Replies
View Related
Dec 14, 2006
This problem is a bit weird but I'm just wondering if anybody else experienced this.
I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.
Just wondering if anybody else experienced this problem and of course if there's a way to solve it.
Thanks.
View 2 Replies
View Related
Aug 26, 2006
Hello, im brand new to these forums, scrolled through a bit of the forum(mainly trying to figure out how to go about what im about to ask) and it seems like a good forum...anyways onto my question.
i am in need of ideas/know-how on how to structure a database like such:
its an online motorcycle accessories website, and this is the information i need to store in the db(basically these will be my tables in the db)
bike manufacturer
bike name
products
now my main question is how would i structure those tables so i can get this effect.
each product in the product table needs to have a bike associated to it, 99% of the time it will be more than one bike associated to it, so how would i go about doing that?
originally i had set it up with the bikes name being different columns in the products table with a bit type set to 1 if that product was available for that bike and 0 if it wasnt available. however as you are probably already thinking that isnt the best way to do that.
so what is the best way?
any info at all would be greatly appreciated.
thanks in advance
View 2 Replies
View Related
Aug 21, 2007
Currently a 2005 login has been granted sysadmin because I can not figure out how to grant the same login the SqlAgentUserRole. I thought I would use SSMS but there must be a trick. The role only exists in the msdb database, which the login has not been granted explicit access to. And the login does not appear in the list of Role Members or its sub windows. This should be real easy and intuitive . . .
Thanks!
Michael
View 3 Replies
View Related
Aug 22, 2007
What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database??
I would like a separate file for each object (table, view, or stored procedure).
Could someone point me in the right direction??
Thanks,
Paul
View 1 Replies
View Related
Nov 29, 2007
We are in the process of trying to automate our production releases (what a concept ;-)
The database is SQL server 2005
All objects are being stored in VSS
Using Nant and Cruise Control for the actual migrations.
I have two directories - Create (for a brandnew database) and Change (db object changes)
In my 'Change' script, I do the following -
1 - Take backup of database
2 - Migrate objects from 'change' directory to production
3 - Script out all objects of database and save in the 'Create' directory
For the #3, I was hoping I could create an SSIS package that would
script out all database objects and save them on the VSS server.
I'm new to SSIS and want to verify it's something that can be done before I start down that path.
If anyone has any examples or references, it would be much appreciated.
Thanks
View 4 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
Jul 23, 2005
I've a problem.In my MSSql db I've some tables named username.mytable and only onestore procedure named dbo.sp;CREATE TABLE [pippo].[mytable] ([year] [int] NOT NULL ,[month] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [pluto].[mytable] ([year] [int] NOT NULL ,[month] [int] NOT NULL) ON [PRIMARY]GOCREATE PROCEDURE [dbo].[sp]ASselect * from mytableGObut when I try to execute dbo.sp (from pippo or pluto connection) I'vethis error (users pippo and pluto are owner):Server: Msg 208, Level 16, State 1, Procedure sp, Line 4Invalid object name 'mytable'.How can I access to pippo's (or pluto's) data from dbo.sp?thanks!!
View 2 Replies
View Related
Mar 20, 2007
I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.
Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.
If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.
any ideas.
View 5 Replies
View Related
May 22, 2000
What is the best approach when transferring all database objects & data from a SQL Server 6.5 database to SQL Server 7.0 database running on different servers.
Thanks in adavance for your tips
Cheers,
Phil
View 1 Replies
View Related
Apr 3, 2007
We have decided to use business objects in our new application, which seems to be working well, since they can be used as a binding source for grids, etc.
I am trying to evaluate whether SSIS is a practical solution for our ETL requirements. The problem is, I can't find any examples or references, or even the slightest hint that anyone is using them with business objects. Any attempts to search yield a ton of results which are based on a commercial product called "Business Objects" rather than the design pattern.
It is currently a requirement of our development team that all data access must be done via business objects, rather than communicating directly with the database.
Can anyone provide some more information (besides just suggesting I write a custom connection manager)? Is there anyone who has actually made SSIS work with business objects?
Thanks, Richard
View 9 Replies
View Related
