I have a database, where all tables and stored procedures look like this:
dbo.table
dbo.sp
Is this naming because of the db owner creating the tables and stored procedures?
The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...
How can i solve my problem!
Using the user without db_owner rights brings in my application the error:
The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.
Thanks for help!!!!
Steven****
--------------------------------------
Solution:
I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005!
New to SQL so please be patient. I'd like to give my developers "See All/Do Nothing" rights to my production server. I want them be able to see everyting that I do, as SA, but not be able to change anything. Is this possible?
I'm new in the SQL Server Express Configuring Stuff and I've a problem or more exactly I don't have any idea about configuring an secure and hacking save user for a SQL Express DB.
What server roles/server permissions/endpoint permissions/database permissions are neccessary to have an rw access SQLE user? Any articles available? Any suggestions?
Scenario is a Personal Webpage for my Family & Friends located in my house
- ASP.Net Starter Website: Personal Website
- 2 MDF DB's: ASPNETDB.MDF & PERSONAL.MDF (included with the Starter Website)
- Development on DEV PC; Deploy on SERVER PC
HW: - SERVER PC with Win2k (fully patched) - DEV PC with Win XP Sp2 (fully patched) - LAN 100Mbit w NAT FW - WAN Cable Modem 128/1500
SW: - APACHE 2.0.55 with latest aspnet_mod - ASP.NET 2.0 - SQL Server Express - SQL Manager 2005 Lite - MS Web Developer Express
What I've done so far: - APACHE: configured & working - aspnet_mode: configured & working - SQLExpress: configured & working - SQLBrowser: configured & working - TCP/IP Protocol for SQLE: configured & working - Mixed authentication for SQLE: configured & working - ASPNETDB.MDF: attached, configured & working - PERSONAL.MDF: attached, configured & working - SQL Manager 2005 Lite: configured & working - SQLE users: created one for ASPNETDB and one for PERSONAL (with SQL Manager Lite; Sorry MS!!! your SQL Server Mgmt Studio Express sucks!) - WEBSITE: changed connection string from local to remote
(local doesn't work, because the LocalSystem User on Win2K, which is used by the SQLServer, has no User Profile. Also you cannot change the Service User, so it won't work with the local connection string, but with an attached and remote accessible DB it works just fine) - WEBSITE: runs locally and remotely on DEV PC with remote DB's and custom users.
But I'm concern about the two users I've created, to access the two DB's. I've no idea, what rights/roles aso they need to use the DB for remote (for development) and local (for production) access and to be secure?
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest? Thanks a lot.
I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).
I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.
If you can think of soemthing, please let m eknow.
I have MS SQL Server 2000 DB. I have created a User and created some tables for the same. I created a Role named A and granted Select Permissions for few tables to that roles.
When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.
Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main() DTSDestination("a")=DTSSource("a") DTSDestination("b")=DTSSource("b").Value + 100 Main = DTSTransformStat_OK End Function
or
Function Main() dim x DTSDestination("a")=DTSSource("a") x = DTSSource("b").Value + 100 DTSDestination("b").Value = x Main = DTSTransformStat_OK End Function
Hello friends I need a very ergent help!!!!!!!!!!!!! Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on. but the database is still running and users are accessing it. This SQL database is accessed by an application(it can also create users on the backend). I don't know how it happend ( i cant even get to errorlog). I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend from Test2 Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' )
which returns: m_id 33 34 34 35 35 35 36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count 33 | 1 34 | 2 35 | 3 36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count" FROM taglink WHERE m_id IN ( SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' ) ) GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count 33 | 3 34 | 2 35 | 3 36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!
i try to do a simple insert using SQLDataSource and a Stored Procedure I try all night and always have this error
Object Must Implement IConvertible
I can’t understand where I wrong..
Looking around I understand is maybe a typecasting problema but I not find nothing more understeable.. Could someone tell me why Microsoft release this SQLdatasorce making more difficult a simple insert? If someone could help me to fix this problem please
Here is the code ------------------------------------------------------------------------------------------------------------
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try SqlDataSource1.Insert()
Catch ex As Exception Response.Write(ex.ToString) Finally SqlDataSource1.Dispose()
End Try
End Sub ------------------------------------------------------------------------------------------------------------
i have a table "employee" , i have given grant all rights to a user in sql 6.5 server . still whenever he tries to access the table from VB he gets an error "execute permission denied on table employee, owner dbo" . if i log in the server with this user name and his password i am able to interact with the table . please help.
We've tried attaching the mdf files given to us by some customers using the script they've provided, too.
I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."
What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4)) Insert Into @Stage Select convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai Union all select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0 Union all select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1 SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
First, I'm not sure what format to post the SQL query in, but I'm using it in the Pawn programming language, so it's all on one line. Sorry if it's hard to read :(
Ok, I am supplying you guys with my database that I am having problems with. Basically, I'm telling it to ORDER BY ASC but it's not ordering it correctly. It's almost correct, but not perfect. I'm not sure if SQLite is much different than SQL standard, that's why I differentiate.
Here is the SQLite database: *deleted*
The problem comes in when you execute this query:
SELECT p.alias, min(s.fin_time), s.cps, s.gcs, s.boosts, s.wpns, s.score, s.score_id FROM climb_scores s JOIN climb_players p ON s.user_id = p.user_id JOIN (SELECT user_id, fin_time, score_id FROM climb_scores WHERE map_name="j2s_4floors" AND cps = 0) g ON g.score_id=s.score_id GROUP BY s.user_id ORDER BY s.fin_time ASC LIMIT 20
The end goal is to have fin_time sorted in ascending order, but it's not working right. Can someone clue me in to the problem?
Location Tree View --- .___ Building A . .___ Room 1 . .___ Room 2 . .___ Partition 1 . .___ Building B . .___ Room 1
on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint. the higher of its value means it is the latest placement.
so i had created a sql to pull the data like this:
SELECT tblasset.AssetName, tblassetplacement.OfficerInChargeID, tblassetplacement.Locationid, tbllocation.LocationName FROM tblasset,tblassetplacement, tbllocation, (select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl where tblasset.AssetID = tblassetplacement.AssetID and tblassetplacement.PlacementDate = temptbl.LatestDate and tblassetplacement.AssetID = temptbl.AssetID and tbllocation.LocationID=tblassetplacement.LocationID ;
this will output: --- AssetNameOfficerInChargeIDLocationIDLocationName IPODSusan6Room 1 Sony PS3Fred5Partition 1
i would need to have a column ParentLocationName that directly shows the name of the parent's location as below: --- AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationName IPODSusan6Room 1Building B Sony PS3Fred5Partition 1Building A
is there a way to pull the parent's location name in one sql syntax?
Is there a way to supress output on one column in a SP, using data from the same row?
Like This: SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null? DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning. I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS BEGIN SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility FROM dbo.tblResidents A WHERE DOT IS NULL UNION SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility FROM dbo.tblResidents I END GO
I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.
Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.
Why are all LVFs still active after detach/attach and checkpoint? How can I shrink the LDF file?
Hello,We have an ADO.NET application using .NET version 1.1.4322 SP1. It iscalling stored procedures in a database that it never written to. Theonly thing the stored procedures do is a select statement on a fewtables that are joined. In the last few weeks we have experiencedissues where, two or three of these stored procedures consistentlytimeout. When we recompile the stored procedures, the problem goesaway for a period of time (anywhere between a few hours and a fewdays), then it reoccurs with the same two or three stored procedures.We have tried running the stored procedures with the same parametersagainst the same server using query analyzer with the same user andconnection settings while we are having the timeout issues, and theyperformed normally (in the 5 second range). We have also put thedatabase into "read only" mode, which has improved performanceduring normal times, but the issue continues to occur.Our trace data shows that during the timeouts periods, the storedprocedures perform the same number of reads and writes, and using thesame amount of CPU as during normal times, but the duration increasesfrom 5-6 seconds to 30 seconds (when the server receives the timeoutrequest).We have found no blocking on any of the tables (they are, after all,read only).Finally, we compared execution plans for the stored procedures when wehad timeouts to right after we recompile and alleviate the issue, andthe plans are identical.What could be causing this problem? Does recompiling a storedprocedure affect the Sql Server .Net Data provider?Thanks in advance
Sory for starting a new message, it won't let me reply to the original.Problem has been solved.osql -U sa -s localhostBALTDDoes the trick.Thanks for everyone who had a look at it anyway!Enjoy your weekend!
I have 15 websites running on my server, which also has SQL Express installed. every other day my webpages whch connect by sql connection string, give an error.
Restarting SQL Service does not resolve, however rebooting server, without making any changed sorts the problem. does my sql account need access to the master?
Is this weird or am i missing something really obvious.
