I Need Some Simple Security (for SQL Server Management Studio) In A Group Of Programmers.
Dec 5, 2007
Hi, I'm working in a web project.
In our lab, all the PCs are installed with SQL Server Management Studio, and the codes of the site are left by the previous batch of programmers.
Here is briefly how our web works, the web will call the sql server providing a username (userA) and password (aaaaaa) to log into the database, then the web can connect to the database to do various functions like select, insert, update and delete.
Currently anyone can go to thier SQL Server Management Studio on thier PC to edit various things like names and columns of tables by logging in as userA.
But, we now only want a small number of users to have the ability to change things in the database. What are some ways we can do that?
Something that I've thought of is that only allow userA to log in and do functions like select, insert, update and delete and do not have the ability to edit things like names and columns of tables. Have a userB with password only known to me that can have full control of the database which I have done.
Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.
I have a question about granting enough rights to allow a non admin user to start and stop a sql server service via the SQL Server Management Studio by right clicking on the server node.
I have changed the acl's of the SQL Server service security and gave the user rights to start and stop the service. This does allow them to connect remotly to the server using computer manager and they successifully start and stop the service. But, in SQL Server Management Studio the option still does not show up, unless he is a admin of the server.
Does anyone know what other security settings I need to address for the start and stop to show up when I right click on the server node?
Originally I started using SQL Server Management Studio on the server directly. When I would right click on a view->edit I would essentially get a Alter Script of the view. Clicking on view-> design would open the query editor.
However I decided to connect to the server from my desktop instead using a local installation of SQL Server Management Studio. Now when I right click on a view I only get the modify option which opens the Query Editor and not the actual code that can be scripted. I know I can just right click->script view as->alter to to get similar results. I just can't seem to find any options to change this functionality. Does anybody know how to change this or is this a limitation of a desktop installaion?
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
Hello, I am facing a very odd behavior with SQL server 2005. I have a database role with specific permissions to execute only some of the stored procedures in the database. I also have a SQL login/user attached to the role.
If I execute one of the stored procedures from the application (web application, with Microsoft enterprise library database block) I am getting a security error stating that I need select permission for the schema. However, If I execute the same sp with the same parameters from SQL management studio, connecting with the same SQL login as in the application I can execute the sp without any security errors. The SQL management studio is in on a different server than the targeted database.
Are there any differences between executing stored procedures from SQL MS and from application when using the same SQL login?
Greetings Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role Is mapped to master reader/writer/public Is mapped to model reader/writer public Is mapped to msdb reader/writer public Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database). When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database. He can access his database alright. My question is, can I hide all the other databases from him, and only display that only database he has access to? I have tried many different ways, I can't seem to be able to do so.
I know from searching this forum that there have been many variations of the issue I'm currently suffering, but I haven't found anything quite like mine or a solution to solve it. The issue I'm running into is that I am unable to log on to Reporting Services using Management Studio or the web interface when Windows Integrated Security is enabled. I have full functionality using basic security, but the risks involved make it impossible to deploy basic authentication out into production. The error I'm receiving in Management Studio is The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient). I recieve the same error when I try the web interface.
I've looked the most recent logfile in WindowsSystem32LogFilesW3SVC1 and these entries match up to the time I attempted my authentication.
Code Snippet 2008-05-12 20:30:42 <Edited: Server IP> GET /reports - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:42 <Edited: Server IP> GET /reports - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 301 0 0 2008-05-12 20:30:42 <Edited: Server IP> GET /reports/ - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:46 <Edited: Server IP> GET /reports/home.aspx - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 302 0 0 2008-05-12 20:30:46 <Edited: Server IP> GET /reports/Pages/Folder.aspx - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:47 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 2 2148074254 2008-05-12 20:30:47 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 1 0 2008-05-12 20:30:47 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 1 2148074248 2008-05-12 20:30:49 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 2 2148074254 2008-05-12 20:30:49 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 1 0 2008-05-12 20:30:49 127.0.0.1 POST /ReportServer/ReportService2005.asmx - 80 - 127.0.0.1 - 401 1 2148074248 2008-05-12 20:30:50 <Edited: Server IP> GET /reports/Pages/Folder.aspx - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 500 0 0 2008-05-12 20:30:50 <Edited: Server IP> GET /Reports/js/ReportingServices.js - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:50 <Edited: Server IP> GET /Reports/js/ReportingServices.js - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 200 0 0 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/styles/ReportingServices.css - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/images/blank.gif - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/images/blank.gif - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 200 0 0 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/images/48error.jpg - 80 - <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/styles/ReportingServices.css - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 200 0 0 2008-05-12 20:30:54 <Edited: Server IP> GET /Reports/images/48error.jpg - 80 DomainUserName <Edited: Server IP> Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 200 0 0 2008-05-12 20:31:07 127.0.0.1 GET /reports/ - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:31:07 127.0.0.1 GET /reports/ - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 1 0 2008-05-12 20:31:13 127.0.0.1 GET /reports/ - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 2 2148074254 2008-05-12 20:31:13 127.0.0.1 GET /reports/ - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727) 401 1 0
I tried to highlight one of each of the HTTP errors I am getting. 401.2 and 401.1 are the ones I kind of expected, but have no idea why I'm getting them. The 500 0 error is a bit troubling, because unless I'm mistaken thats a server side error.
Here's my current setup. As far as I can tell, I've done everything to set up correctly for Windows Integerated security.
My Specs The server machine running all SQL 2005 services. It resides in a domain. All Clients are in the same domain.
Intel Dual Processor 3.20 gig Xenon
Windows Server 2003 w/ SP2
Microsoft Sql Server 2005 w/ SP2 Reporting Services Setup The virtual directories Reports and ReportServer are set to Windows Integrated Security with Anonymous Access disabled. All other checkboxes in Directory Security are unchecked. For permissions in Reports, I have:
Administrators (servernameAdministrators) - Full Control
ASP.NET Machine Account - Modify, Read & Execute, List Folder Contents, Read, Write
Domain Users - Read & Execute, List Folder Contents, Read
SQLServer2005ReportingServices]WebServiceUsers$... - Read & Execute, List Folder Contents, Read
SQLServer2005ReportServerUsers$... - Read & Execute, List Folder Contents, Read
SYSTEM - Full Control
Users (CompNameUsers) - Read & Execute, List Folder Contents, Read In ReportServer I have the same permissions except Authenticated Users is absent.
At one point, I even added the account Everybody and gave it full control, and I still recieved a HTTP 401 error. Any help at what I might be missing would be a godsend.
Hi all, I want to do SQLCLR by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express and I have read the following articles and books: (i) Connecting to SQL Express User Instances in Management Studio in http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-insta... (ii) Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition in http://www.microsoft.com/technet/sql/2005/mgsqlexpwssmse.mspx (iii) Chapter 16 - Going Beyand Transact-SQL: Using the SQL Common Language Rutime (SQLCLR) in Microsoft SQL Server 2005 Express Edition for Dummies (iv) Chapter 21 - Working with the Common Language Runtime in Microsft SQL Server 2005 Programming for Dummies (v) Chapter 4 - Introduction to Common Language Runtime (CLR) Integration in Programming SQL Server 2005 by Bill Hamilton. I want to create an SQLCLR project "HelloWorld" by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express. But I am still not sure how to get it started, because I do not understand the following things: (1) Pipe Name for a User Instance, (2) Enabling (or Disabling) the CLR by using Transact-SQL, (3) Creating a Transact-SQL script, (4) Creating an Assembly, (5) Creating a backup device, etc. I need to see some simple examples of SQLCLR by Connecting to SQL Express User Instances in Management Studio via VB 2005 Express. Please help and tell me where in the websites I can find them.
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
I am new to visual studio and I am still not sure of all its components and features.
I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio?
I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional?
I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool.
How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase.
Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology.
My goodness, it seems I still have so much to learn.
How do i get the database that i am using in visual studio into my SQL server management studio? i need to create some scripts to create stored procedures on a live server.
I have SQL server 2005 developer edition installed on my machine. I was expecting something like Enterprise manager to manage databases but nothing is installed by default. Ive found SQL server management studio express and installed it, but Ive also seen references to SQL Server Management Studio. Are there any differences between the two , or are they the same and are just described differently ?? Im a bit confused, Microsoft should make this clear.
Hey everyone, Would someone please tell me why the new SQL Server Managment Studio comes with hardly not frills? What happened to the SQL Enterprise Manager in SQL Server 2000? It had DTS, SQL Agent Job and step viewing, Cool Wizards.. All that great stuff. but with this new Sql Server Management Studio with 2005 all I can do is pretty much look at the data and write queries and create and modify tables. Am I missing something? I did download an external tool which was called an import/export wizard but it sucks compaired to the DTS in SQL 2000. I did notice when I open my Sql Server Management Studio "Express" that it is the Express version... Is there a difference? Thanks for any help.
i have SQL Server 2005 dev edition and i wanted to use SQL Server Management Studio so is it installed or i need to install the SQL Server Management Studio express edition ??? thx
I have installed SQL Server Management Studio (for SQL2005) to a pc. When the user boots the pc, the following error is received:
fmaagent.exe - Common Language Runtime Debugging Services Application has generated an exception that could not be handled. Process id =0x328 (808), Thread id=0x32c (812).
Click Ok to terminate the application. Click CANCEL to debug the application.
After clicking on OK, the pc continues on its merry way and boots up successfully.
Does anyone know what would cause this error? I have checked the Microsoft site but did not find this error listed among the documents.
Good Afternoon! I installed SQL Server Express 2005 but the GUI is not visible. Someone can tell me how to install it or where I can find the executable.
David writes "I am kind of new to SQL Server Management Studio. How do I export tables and stored procedures from a database on my PC to a database by the same name located on a remote server."
Can I set a passport protection to a database (once I am connected to the SQL Server on my own machine) from not opening/browsing/stop even read access?
(I want something like log in authentication before I open/browse/double click on any database to expand basically)
I installed SQL 2005 in a few server but none of them have SQL Server Management Studio. Where to find out them? I checked c:...inn, there is now VSShell folder.
I have the "Ready to Launch" package, which includes Vidual Studio 2005 Standard Edition, SQL Server 2005 Standard Edition and something else called BizTalk Server 2006.
Anyway, Visual Studio is spread across two CD's and I have installed that. I now want to use the SQL Server Management Studio to administer a database I have running on a website. Anyway, SQL Server is on a DVD and when I put it into the drive it comes up with the autorun screen. I noticed that in the top left corner of this screen it says "SQL Server 2005 (Disc 1 of 2). However, there is no second disc, so I'm wondering if this is a problem or just a typo left over from the CD version.
There is two options in the menu under Install, "Server Components, tools, Books Online, and samples" and "Run the SQL Native Client Installation Wizard." I have tried both of these options and I still don't have the SQL Server Management Studio. In fact if I select the first option, when I get half way through the wizard, it asks what components I want installed. I cannot see any reference to the SQL Server Management Studio in this list, so I just select everything to be sure.
Once it has finsihed installing, all it does is create a Start Menu folder called "Microsoft SQL Server 2005" and another folder inside that called "Configuration Tools." This folder just contains a few things like configuration manager and surface area configuration, etc...
So where do I find this SQL Server Management Studio? Have I done something wrong or am I missing something (like a second disc)? I'm installing on to Windows XP, would that cause the problem? Do I need to be installing on to some sort of server OS (which I don't have BTW as I'm just a regular user looking for a way to administer my MS SQL database and I heard this tool was the way to go)?
I have just completely installed MS SQL Server 2005 Express Edition in MS Server 2003. After installation, I only have the Configuration Manager, Error and Usage Reporting and Surface Area Configuration, listed under Configuration Details.
SQL Server Management Studio is not accessible anywhere. I also do not have the folder C:Program FilesMicrosoft SQL Server90ToolsBinnVSShell in which the SQL Server Management Studio is installed. What must I do to get this component installed?
In this article http://msdn2.microsoft.com/en-us/library/ms143219.aspx, it is also written in step 8 that there should be a "Components to Install" page. I have done the installations and uninstallations a few times, but I still do not have "Components to Install" page. In fact, after step 7, I am brought to the "Features Selection" page.
When attempting to start the SQL Server 2005 Management Studio from the Start Menu on my Windows 2003 SP1 server, I quickly get the error, "The Application failed to initialize properly (0x80000003). click on OK to erminate the application. I am accessing the server from my Windows XP Workstion using Timbuktu Pro Remote control software. I have no problem attaching to the SQL Server database on this server when connecting to it from my Workstation install of Management Studio. The SQL Server Business Intelligence Studio launches fine in the Timbuktu session as does SQL Server Configuration Manager. The Configuration manager shows all services (for both my Default and Named Instance) in the Running state.
In 2005 Query Analyzer no longer exists and the query is integrated with management studio. I have some users that want to query the database but I don' t want these users to have the ability to use anything else in the Management Studio - restore databases, detach, etc. Anyone have any ideas how this might be done? Thanks.
I have the Express version but now need the full tool set. I've been looking for the last half hour on Microsoft.com and elsewhere, and can find absolutely anything I could want to know about the program except how to purchase it. I feel kind of stupid, but I have to ask: where the hell do you buy this thing?? :-)
I've install SQL Server 2005 Enterprise Evaluation Edition but the Management Studio was not installed. Is this normal? If yes how can I get the Management Studio
I just had to do a total restore on one of my machines that has a copy of SQL Server 2005. Luckily, this is not the production server. But when I reinstalled SQL Server, I now do not have the option to get into SQL Server Management Studio. Why would this be?
Everything seemed to install okay - including Reporting Services.
