I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:
All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID. ( I can€™t do it because I got €ś The €śDeafult_Schema clause cannot be used with a windows group€?)
Same as above but this time they only need select permission on tables nothing else.
I've not touched SQL server programming since 1999. I have very littlememory of it and need some clarifications on some basic questions thatI could even use a book for. Until I get myself a good book, someoneplease help me with the answers:1) What are SQL functions and how are they different from storedprocedures? Do both of the programming objects not achieve the samething? What was the need of having one in addition to the other?2) How do we use an "if construct"/if clause within a SQL statement?Can we use conditional checking with the if construct within a storedprocedure? Can you please post a trivial example of a stored procedurewith an if clause?3) Stored procedures can have input parameters as well as outputparameters. Can they also have in/out parameters that are like "byreference" parameters? What's the syntax on Microsoft's T-SQL version?4) How does one check the return value of a stored procedure?Thanks for helping out.
Hi All,I am used to other SQL engines, and have a few basic questions--1)If I wanted to conditionally drop a table, does SQL Server have away to natively do this? Many SQL implementations will allowsomething like:CREATE OR REPLACE tablename ASSELECTx,y,zFROM sourcetable;Does SQL Server have something like this? This syntax, both the"create table as select" syntax and the "create or replace" syntaxseem to cause problems.2) Some of our existing queries have a keyword, "GO" where I wouldotherwise expect a semi-colon. Is there a functional differencebetween the two? I seem to be able to replace the "GO" keywords withsemi-colons without any changes in how the script behaves, but Ithought I would check and see if anyone has advice about thedifferences here.Thanks in advance,Ben
Im studying sql server clustering at the moment and have come across many points which confuses me. Can any SQL Clustering Guru clarify some of them for me or point me in the right direction. thanks.
1) Single Instance clustering now replaces Active/Passive clustering? If so, doesn't this mean the you cant run an 'Active/Active Cluster' by installing 2 instances of sql server on each node?
2) Multiple Instance clustering now replaces Active/Active clustering?
3) You can only have 1 instance of SQLSERVER on each node in a single instance cluster (2 nodes). However u have more than 1 sqlserver in a multiple instance cluster?
And last but not least, do u know where i can get the latest resources on sql server clustering? apart from microsoft.com. thanks.
I have two questions concerning formatting output in SQL.
I have a processer speed column that I would like to make usable. A sample entry is 2793, which I would like to be returned as '2.8 GHz'. I am using the following successfully, but when I try to append the ' GHz' it gives me a conversion error (I have tried putting both a cast and convert around the round statement). ROUND(CONVERT(DECIMAL,PCCPUSpeed_IN) / 1000,1) + ' GHz'
Secondly, I have a RAM colum that stores values such as 534753280. I would like the results to fallin into 64 Mb buckets. So values between 512000 & 532480 should return '512 Mb'. I realize I can do this using a CASE statement, but is there a better way?
I've done some basic database stuff with Access and ASP, but I have a client who has told me that he is in over his head and wants me to try getting his "mini CMS" site to work. He told me that he set everything up in Dreamweaver using ASP and "MSSQL"
I have never used what he calls: "MSSQL" before. Do I need software to create an "MSSQL" database? or is it all coded?
Also, one other question. The whole purpose of this site is to have Admin users log in to a page where they can edit the content of a page and upload it. If there is a large block of text, does it all reside in a field in the database, or would it be a link to a text file? kind of like linking to an image since images don't actually reside in the database.
I have created two tables and inserted the data using insert statements. The two tables that I have created are Automobile and Dealer_Info and have a 1:M relation from Dealer_Info > Automobile.
I am trying to list the Vehicle_Model and the number of times they are repeated, but when I run the code (below) the Count is inserted into an arbitrarily made up attribute (No column name). When that happens I cannot sort in DESC order.
I am using SQL(2000)
Select Vehicle_Model, Count(Vehicle_Model) from Automobile Group by (Vehicle_Model) Order by count(*) ;
Results:
Vehicle_Model (No column name) Escort 1 F150 4 Explorer 6 Taurus 12
I have been working with SSIS and find myself stopped on what seem like very basic tasks.
Am I right in thinking that, if you want to import a flat file into a SQL datatable, in most PC based systems, that you have to manually cast EVERY SINGLE COLUMN to unicode string, if you don't want to get the 'cannot convert between unicode and non-unicode string data types'? I mean, this is surely a VERY COMMON task, taking a CR/LF delimited ASCII file and dumping it into a database tables? What if you have 50 fields to import?
Am I the only one who finds it very difficult to cast a string from a textfile to a date format? At this point, I always just dump the date to a 8 character string and save it that way, and use a SQL view to cast it to a date. At least SQL Server can do 'basic date conversions'. Is there any way to freeze execution and find out what data was provided to a derived column, if you're trying to do some substring operations and hodge together a date? I couldn't find any. I usually put a extra column in a datatable and plunk some data there to find out 'what it looked like', how else can you debug an SSIS program?
Is there a planned service pack or update to SSIS that will make this more usable? Does Microsoft ever intend to implement an UPSERT type module? Or a File Move operation that handles directories that fill up with log files, each of which have to be added to a datatable and then moved to an archive directory (surely, another very common task - I figured out how to accomplish this but it is far from intuitive, the way it currently works). Sorry for grumbling, but honestly, I find turnaround time to develop a very simple import program is RIDICULOUS. It's quicker to write a COBOL program to issue a bunch of formatted SQL UPDATE statements than using this application.
I'm new to using SQL server and have a few setup questions. I'm setting up a database for the company I work for. Should I setup a different database for each division in our company (e.g. accounting, project management, estimating, etc.), or should I use just one database? It seems like it would be easier to use one database so I only have to use one connection, but is this ideal? I want all the info from the different divisions to be used together, but at this point I'm confused as to how to connect different databases together, if I were to go this way.
Hello all, this is my first post, I have a couple of basic questions regarding the use of SSIS. I have used SSIS for a couple of years for on off table loads but never implemented production jobs with it so please bear with these questions.
1. When making scripts that connect to one or more databases, is there a way to store the userid and password somewhere besides embedded within the package, such as a web.config file, this would make routine password changes much more manageble.
I guess it was only one question but any help would be greatly appreciated.
Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...
Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions... ___________________________________________________________
The database engine uses the Service Master Key to encrypt... - Linked server passwords (example?) - Connection Strings (example?) - Account credentials (example?) - All database master keys
You should back up the Service Master Key and store it offsite. (Even if I never plan to use encryption?)
ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key. (Is it building a completely different key, or regenerating the original?) (Why would I want to regenerate it?) (Does this void all database master keys encrypted by the original Service Master Key?) (If so, do I regenerate all database master keys voided by the regeneration?)
A database master key is optional. (So if I have Server A with encrypted database A, that I want to restore to Server B, I need the following items to perform restore? - a backup of database A - the Service Master Key of A at time of backup - associated password of Service Master Key at time of backup)
We have a small Ethernet LAN running SBS 2003 R2. I have installed SQL Server 2005 that came with that release on the server. I did not specify a named instance.
1) What is the name of the default instance?
2) When I go to a client computer I cannot see the SQL server instance.
For example on my PC I have VS2005. If I go to Server Explorer, all I see is the local copy of SQL Server 2005 Express I have installed on my PC. I see nothing for the server. Does the server need configuring within SQL Server to allow this. Do I need to change permisions on the folders where the SQL Server 2005 files are stored.
In short, what basic steps do I need to follow to get acces to SQL Server on my server from a client.
3) If I want to create a new named instance, do I have to do Run the whole setup again? There appears to be no managemnt tool for instances of SQL server.
I really confused , I wanna get an rowid on sql 2000 table so I have created a sproc and it's syntax is OK How can I check it on sql query analyzer? this sql server 2000 Also How can I use that in select statement?
thanks..
here is my select statement which I have to use sproc inside select custid,ordernum,sku,amount, dbo.get_rownums (custid,ordernum,sku ) ??? from tp_cod cod
here is my sproc: CREATE PROCEDURE [dbo].[get_rownums] @custid as varchar(10),@ordernum as varchar(5),@sku as varchar(10) , @i as int output AS BEGIN DECLARE @SkuID as varchar(10) --DECLARE @i as int DECLARE got_sku CURSOR FOR Select sku from tp_cod where custid=@custid and ordernum=@ordernum set nocount on set @i=0 OPEN got_sku FETCH NEXT FROM got_sku INTO @SkuID WHILE @@FETCH_STATUS = 0 BEGIN Set @i =@i + 1 if @SkuID=@sku begin return @i end else begin FETCH NEXT FROM got_sku INTO @SkuID end END CLOSE got_sku DEALLOCATE got_sku END GO
Greetings! I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code. txOTHrs.Attributes["onBlur"] = "return calculateTotal(this)"; The onblur event handler is coded as follows function calculateTotal(tb) { var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt; document.getElementById("txTotal").value = total; } Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?
My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
Any help anyone can provide is greatly appreciated!!
I'm new to SQL 7.0 and have a basic security question. I want to set up security so that different roles can see different portions of the same tables. In other words, Department A see's its projects from table 1 and Department B users see only their projects from table 1. The results are then linked to the client front end, so that the basic form they input/read off will give them the results from table 1 with only their projects. Is there a simple way to do this with a stored procedure or view, so that the result set can be linked to the form and only differs depending on the role? Thanks for the help.
I'm trying to learn the "correct" way to use security with SQL Server 2005 and .NET 2.0. Let me explain what I've always done in terms of security and why.
Historically, I've created a Users table and a UserPermissions table in my database. The Users table has the username and password, and the UserPermissions table relates to the Users table and has a list of all (application-defined) permissions granted to the user. For example, say the application has features A, B, and C. The administrator might grant features A and C to user John Doe.
The reason I put this table into my database is two-fold. First, it allows me to easily setup a list of features that the application can secure. Second, it allows the application to log into SQL Server using a constant username a password. The application always gets access to the database, but then if the user didn't provide a good username and password, the application logs back out of the database and quits.
The downside to this is that SQL Server doesn't know who's accessing it. So if I wanted to have a trigger that gets the current user, all users appear to be the same person. The upside is that the administrator doesn't need to go into SQL Server and set up new users, and the users aren't aware of the actual username and password used to access the database, so they can't get to it directly.
I'd really like to learn to set up security the right way. What I want is:
A) Each user to be uniquely identified within SQL Server - i.e., each one logs in with his own user id B) The application has complete control over the database (I want the application to be able to read all tables, for example) C) The user doesn't have direct access to the database. I don't want the user to be able to load SQL Server Management Studio on his computer and go directly into the database D) There is some way to grant application-specific features to each user
I would think this has to be a common scenario. Can anyone offer some good tips on how to accomplish what I'm trying to do? Thanks in advance...
I'm having 2 Dummy Users. The one i created i direct login on the server and access to 2 databases. The second one i've placed in a AD users group. That group has a login and is also mapped to the 2 databases.
Sql server is running with on a AD-users, not localsystem.
Why can DummyOne work fine, and DummyTwo (the grouped one) can login but access none of the databases.
I'm designing a C# winforms app that will be used by 500 customers/companies. The customers will use the app to edit data for their company on the remote SQL Server. What's the best way to manage security in terms of logins/passwords? Should each user have a different login? (probably not) Or should the application have it's own login/password that is encrypted and the user never knows what the login/password is?
Also, is it necessary to use Web Services for this application? Any other thoughts/comments are very welcome.
Ok, I know it's a really basic question but I'm hoping someone will humor me. Not being a database expert I am wondering what the basic security differences are between a SQL server db and an Access db. What makes SQL a more secure choice for confidential information over Access.
I know from a useability SQL is the better choice being a multiuser access platform but I am not familar with security mechanisms employed.
The apps that connect to my SQL DB's (for internet use) use SQL authentication (the app login so to speak). Anyways, a Java developer showed me that he built into his app a way to retrieve that app login and password from SQL Server. Obviously I wasn't very happy about this. So my questions:
1; Does anyone know if the same thing can be done using .NET code? 2; I've heard of a method using .Net Web Services (WS) for using WINNT authentication even for internet apps. It would be that the app calls a WS, that WS the passes in WIINT authentication to the DB and all is good. Has anyone else out there used this type of security? Is there a link you can provide? 3; If number 2 is implemented, does it then eliminate the possibility of apps being able to retieve the sensitive information?
I want to create an MS Access front-end for my SQL database. I want to create user accounts with passwords for each user. Should I change SQL Server to "MIXED authentication mode" (SQL Server authentication and Windows authentication) ?
******************************************** *** Thank you very much in advanced, security gurus *** ********************************************
I have created logins and user accounts in SQL Server. How come, when I open SQL Server, it did not prompt for passwords ? How can I make it to do so ?
******************************************** *** Thank you very much in advance, security gurus *** ********************************************
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
However i am still not 100% clear, dialog security with certificates when there are hundreds of remote services seems a bit complicated.
I have a few more questions.
1. you said "When sending the first message (any direction), we look up the 'best' certificate for each user"
My inititor sevice is owned by dbo, i have created certificates only for named users not dbo, how would this work?
2. I am going to try settin gup this again, but at the central service i will create a seprate user for each remote service, authorize the remote certificate for this user and use this user in the remote service binding for that remote service, is this correct or am i still doing somehting wrong?
3. How does all of this relate to the endpoint security, i ahve the same scenario, 1 pricipal id with all remote certificates authorized for it for the endpoint? I have no idea how i would have to setup different user for each remote servcie as far as the endpoint goes as there is no remote service binding to dictate which user to use ?
I will keep try and experimenting maybe i will get it right sooner or later.
Still working on custom security Since I can't get a name of file (path) from within CheckAccess method it becomes somewhat useless. Is there a way to maybe overload GetPermissions or Policies methods? Or maybe CreateReport method so that I can include some custom code there? Is there an example of something like that?
I wish the catalog tree was transparent to Authentication extension, I don't see a point in acl for a custom extension, all I want is names and I can build on top of that. Something along the lines -
Code Snippet
public bool CheckAccess(...file...) { string[] permissions ; permissions = server.GetPermissions(file); ... Maybe I am missing something simple and I can tie everything to a security descriptor but I don't see how I can if there is no information such as name, date, modified by name and so on. All we get is principal name which is not very useful since I don't use built-in security names.
I am an Oracle DBA who inherited SQL Server administration. I have been to some 2005 training and I've been supporting several DB's for a while now but I still have some nagging security questions and would appreciate some help.
1) I needed to grant execute on a specific procedure but when I drilled down, I found that it already had execute in the EFFECTIVE PERMISSIONS. I would like to know how to tell where it got this permission from. I did some digging and found that execute appears to have been granted to the schema itself. I didn't know you could do that. Would this result in the effective permission that I observed?
2) I am trying to audit the permissions on existing principles. In Management Studio I drilled down and found permissions under Security and under Server Properties. There are also more permissions under Database Properties and Security and still more assigned at the specific object level. Where can I go or what can I query to see ALL the permissions a principle has been granted across the entire server?
3) If I grant a principle CONTROL to a schema does that also automatically confer DDL rights to said schema or would additional privs be required to perform DDL?
does anybody know why MS doesnt consider Basic Authentication a viable solution for Inter/Xtra net access to RS? I'm re reading the documentation at http://msdn2.microsoft.com/en-us/library/bb283249.aspx but so far am not getting what the Security Extension options accomplish that can't be accomplished with much less effort using some form of Basic Authentication.
I posted this question before, and got a response about Windows Update. However, I work in a larger corp. and need to know if there are any other answers to this? We use a patch client that rolls out patches and service packs after we have tested them in our environment. What can we do to stay as up to date as possible for security patches?
How are security patches for SQL Server Express made available (e.g., as separate distributions or bundled into other Microsoft patch distribution mechanisms)? Are there specific procedures that I need to put in place to ensure that it gets patched on end user machines?
Greetings. I have developed our own custom security extension, implemented forms based authentication, and can authenticate from report manager, report server and sql studio. So far so good.
However, when it comes to Authorization, i'm unclear in a few areas and would appreciate if someone could help me out with the following questions. It should be noted that in the code I have granted an administrator user full access to all operations and permissions, and then tested against both an administrator user and a normal user.
"Returns the set of permissions granted a specific user for an item in the report server database."
Inparticular, the secDesc parameter is supposed to contain the security descriptor associated with the item.
However, with our extension this parameter is always null, even if I have already granted access for a user, which is confirmed through logging in CreateSecurityDescriptor.
Through the report manager or sql studio I can see that the permissions have been created, so I can't understand why I never see them in the GetPermissions method? This then (seems to) flow through to the various CheckAccess methods, where the users are authenticated, but are not authorized to perform any operations. i.e. in report manager a user has no folders or reports available.
Is RS authorization designed around the concept that the details will always be stored in it's own database?
Ideally, we'd like to have the various roles, users and function authorizations defined in our own security framework. This is working great for the authentication aspect of the extension, but unless there is a mechanism which exposes the details of the particular authorization process (e.g. the name of the folder being viewed or report being run), then I can't see a way we can implement it. Unless i'm missing something fundamental of course!
Using Report Builder as the Administrator user (or any other user), I can see no data models available, even though I have created them via Report Manager, and I get the following exception trying to open up the list of reports:
System.Web.Services.Protocols.SoapException: The permissions granted to user '' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction.Execute() at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
I have implemented a report server proxy (inherited from ReportService2005) as per the example, to pass through the authorization cookie. Any clues as to what could be wrong?
Finally, I suspect part of my problem may be in assignment of users to System Roles ("System Administrator" and "System User"), I'm not sure if these are meant only for Windows Authentication? I can see no way of assigning these roles to any of my users using Forms Authentication.
