Does anyone have a SP which will output all users and their permissions (dbo), (read), (write) for each database for SQL 2k? I'm trying to find a way to simply automate this so I can output the data on demand for auditors. I'm currently checking each users permissions on each database through security/logins :(
View 3 RepliesHi,
I have a problem with sp execution.:
objects of [dbo]
Tables of [nuran]
Grants of [nuran]
[dbo].tabloA
[nuran].tmptabloA
Select,insert, update on [dbo].tabloA
[dbo].tmptabloA
Deny for [dbo].tmptabloA
[dbo].sp_yordam
Grant for executing [dbo].sp_yordam
(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS
declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'
SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)
END
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
Thanks a lot
Nuran
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I have a VS2005 aspx page using the ReportViewer to view a SSRS 2005 Report, all running locally on a Vista Business laptop.
I have an error because the stored procedure that it executes does not have the rights to Drop or Create Tables.
I can Preview it fine in VS2005 and I can go to ReportManager and run it OK.
This all worked fine on my XP laptop, so I am guessing that it is the Account used by IIS(7) to get to the ReportServer.
Does anyone have any clues on this?
Does it use NT AUTHORITYIUSR or NT AUTHORITYNETWORK SERVICE, etc.
Thanks in advance
GC
Here's the message and the Tables do exist
An error has occurred during report processing.
Query execution failed for data set 'BranchHeadCountExistStartFinishTotalDS'.
Cannot drop the table 'ExistingStaff', because it does not exist or you do not have permission. Cannot drop the table 'Starters', because it does not exist or you do not have permission. Cannot drop the table 'Finishers', because it does not exist or you do not have permission. Cannot drop the table 'TotalFinish', because it does not exist or you do not have permission. Cannot drop the table 'StartFinish', because it does not exist or you do not have permission. The specified schema name "dbo" either does not exist or you do not have permission to use it.
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
I need to write a script to restore login rights after a nightly database restore. Any suggestions?
View 2 Replies View RelatedHi,
Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword")
Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn)
Line 15: objConn.Open()
Line 16: objRdr = objCmd.ExecuteReader()
Line 17: While objRdr.Read()
Source File: C:finalyearproject2sample.aspx Line: 15
Please Help!! Im a beginner to this, so if anyone knows the answer, take baby steps when explaining. Thanks
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
View 9 Replies View RelatedI am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
View 5 Replies View RelatedWhen creating a frontend with a database behind that, how should i get the login rights for user working?
Can i create a user with a password in the database and assign it a role or how should i get that done?
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
View 1 Replies View RelatedHi when i try and open a database in sql server management studio i get the error "Cannot open user default database. Login failed. Login failed for user 'DOMAINUser'. (.Net SqlClient Data Provider)", what can i do to rectify this, i have googled around and still havent found no answers.
View 4 Replies View RelatedHi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies View RelatedHey Microsoft: this affects development that will eventually sell a lot of seats on SharePoint Server 2007. If you guys and gals can't straighten out this mess, you can't expect custom solutions with custom web parts. So pay attention for your own good. Yes, SPS 2007 is what this is all about. I've created simpe web parts and deployed them, same VS 2005, but I need local development and debugging for more complex stuff. I'm really disappointed that you have no solution for this problem.
I've seen other posts on this, but the result seems to be the same: if you fix it, it's by accident, not by design.
1. I created a blank ASP.NET web site
2. I added a web part manager
3. I added a web part zone
I get the error described above. I have no idea why SQL Server settings are so screwed up.
I've tried all the stuff in the other posts - security settings, command line installation, etc. None of those worked.
The default template(s) don't copy anything into App_Data, nor create an empty folder. I don't know why that is. I have other web sites working just fine, but they use remote SQL databases, or local ones I created, and didn't need profile management (no forms authentication, no web parts, no web zones).
I've also started out with a local database, using SQL Authentication, later moving it to a remote server, and never had any problems. The SQL Server developer edition and Express seem to work just fine. I can also debug stored procs when the .mdf is in App_Data. So the problem isn't one of connectivity.
What would cause the default screw-up? This would seem to point from all directions to a problem with Visual Studio (2005)? The installation is defalt - perhaps some extras included (copy local), but nothing unusual. AJAX for ASP.net is installed, but not used in the above "default web site" trial. Likewise Syncfusion's tools, which work fine on other developer workstations at this same location, all of which are from the same image (purchased at the same time & imaged). That web parts work on other boxes, running all the same management tools (anti-virus, remote sys admin/sms etc.) makes none of those suspect.
I think Microsoft needs to have a clean problem solution and publish it. SP1 certainly doesn't fix it. What's worse is the stupid, lame error message. "I can't open the default database". Well, what did you try? What do you think the default is? What settings are you using? Why is there no debugging information other than the usual worthless trace information showing it's all deep within the ProviderBase and SqlClient namespace code? How is that supposed to be helpful?
If anyone has anything solid, not guesses, I'd like to know.
Hi All,
I have a user that should only have the rights to view the jobs and database properties within Enterprise Manager. I am not sure how to do that. Can you please help? Thanks.
Hi,
I am using SSRS 2005.
Created several reports on the server where SSRS is installed.
In addition I managed to develop a few more reports on my work station and then deployed the reports to the server.
From my local machine I can brose to http://servername/reports and view/run the reports.
Now I would like to find out if/how others can view some of the reports. How/where do I set rights...?
Thanks
I'm running into an issue with a user with restricted rights being able to access a local SDF file. The user has Modify right to the folder (and the file), but cannot access the SDF if it was created by someone else.
The only two workarounds I've found are: 1) If the I delete the SDF and then the restricted user creates the SDF they can then access it. 2) If I grant Full Control to the folder then the restricted user can access the database.
Is this by design? Are there programatic changes that can be made to enable access?
All users who login to the box (Windows XP) need to be able to access the same SDF. The SDF is stored on the local machine.
Thanks,rlw...
In my last post I was having doubt in Registration form and obviously I'm having doubt now in Login form. I can write stored procedure if Login Control is used, but I've created Login form manually and dunno how to do, but I've tried some. Can any one help me? Login.aspx "LoginUserNameLabel" runat="server" Font-Bold="True" Style="z-index: 106; left: 625px;
position: absolute; top: 179px" Text="Username:" Font-Size="Small">
"LoginPasswordLabel" runat="server" Font-Bold="True" Style="z-index: 107; left: 625px;
position: absolute; top: 225px" Text="Password:" Font-Size="Small">
"LoginUserNameTextBox" runat="server" Style="z-index: 108; left: 696px; position: absolute;
top: 175px" BorderStyle="Solid">
"LoginPasswordTextBox" runat="server" TextMode="password" Style="z-index: 109; left: 695px; position: absolute;
top: 222px" BorderStyle="Solid">
"LoginRememberMeCheckBox" runat="server" Style="z-index: 110; left: 624px; position: absolute;
top: 276px" Text="Remember me next time." Font-Size="Small" />
"LoginSubmitButton" runat="server" CausesValidation="true" Style="z-index: 111; left: 786px; position: absolute;
top: 288px" Text="Login" Width="65px" BackColor="Transparent" BorderColor="Black" BorderStyle="Solid" ForeColor="Black" OnAuthenticate="Login_Authenticate" />
Login.aspx.cs: protected void LoginSubmitButton_Click(object sender, EventArgs e)
{
bool authenticated;
string uname, pwd;
uname = LoginUserNameTextBox.ToString() ;
pwd = LoginPasswordTextBox.ToString();
authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());
//e.authenticated;
if (authenticated == true)
{
Response.Redirect("Safety.aspx");
}
}
private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox, string LoginPasswordTextBox)
{
bool boolvalue = false;
SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("usp_ulogin",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
conn.Open();
dr = cmd.ExecuteNonQuery();
while(dr.Read())
{
if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString()));
{
boolvalue=true;
}
dr.Close();
return boolvalue;
conn.Close();
}
}
} Stored Procedure: select * from mytablecreate procedure usp_ulogin@uname varchar(30),@pwd varchar(20)asselect uname,pwd from mytablewhereuname=@uname andpwd=@pwd Is there any thing that I should correct in my code or my code is itself wrong. And can any one explain me what is happening in the code, because I'm little week in c# ;-)
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies View RelatedHi,
I want to give 'Execute' permission for all the sp to a particular user.
Any straight way of doing this then to go to each sp and give him permission for every sp.
TIA.
Hi All,
How can I give the user view rights only?
I need some advice with a msSQL 2005 databaseI'm creating a administration program in vb.net based on a new msSql db. This programme is involved with customeradmin, facturation, products, sales,...by example: Some employees don't has anything to do with product, so they don't need the rights to delete, create or edit it.The question is how can i resolve this problem, because i don't find any good solution. The rights are for every employee different, and can be changed by a admin panel. The admin can give a employee specific rights for every part of the programmeso how can we give a user certain rights when he is logging in into the program.thanks, BoardD :S ;)
View 1 Replies View RelatedDoes anyone know if it possible to make a specific table invisible to auser when they are in Enterprise Manager?
View 1 Replies View RelatedIn the login properties on the Database Access tab, a user's name saysdbo instead of his name. I do not want him to have dbo permissions. Icannot drop the db_owner permissions that he has on the databasebecause I get the error "Error 15405: Cannot use the reserved user orrole name 'dbo'."How do I remove these permissions from this user?
View 3 Replies View RelatedHi, SQL experts.
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?
Thanks for any help, ideas and suggestions.
Alex
Hi iam working with a login form.
When the member completes the registration the details like username,pwd and role i.e Admin or User will be stored in the database,later when the member login again it must check with the username and pwd and must get the role as output and must open the particular pages.I have two different home pages for Admin and User so if the member is admin by role then only admin home page must be opened and if he is user then directly user home page must be opened,
please help me with the stored procedure.
the procedure which i had written is:
Alter procedure LoginCheck(
@Username nvarchar(50), @Password nvarchar(50),
@Role nvarchar(MAX) output
)
AS
BEGIN
SET NOCOUNT ON ; IF EXISTS(SELECT Login_Role FROM RegistrationDetails WHERE Login_Uname = @Username and Login_Pwd=@Password)
BEGIN SELECT @Role = Login_Role FROM RegistrationDetails WHERE Login_Uname = @Username and Login_Pwd=@Password
END
SET NOCOUNT OFF
END
But this will just get the role as output but later how to get the related webforms depending on the role.
please help me with this..
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword]
(
@loginid nvarchar(180),
@oldpassword nvarchar(40),
@newpassword nvarchar(40)
) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @newpassword
WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END
IF @@ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END
COMMIT TRANSACTION
END
************
This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
How can we allow users to access a MS SQL Server OLAP cube but restrict the level of data that he might see?
I mean, if there is a cube with data for three different countries. How can one user be restricted to see data pertaining to
only one of the countries and not the other two countries?
Hi All,
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
TIA,
Shamil
What rights do users require to be able to use SQL Server Express?? I receive errors when the user doesn't have full admin rights. Our users cannot have admin rights all the time on their PCs. Errors below:
"Create failed for Database 'TestDatabase'. (microsoft.SqlServer.Express.Smo) Additional information: An exception occurred while executing a Tranact-SQL statement or batch. (Microsoft.SqlServer.Exress.ConnectionInfor) Create Database permission denied in database 'master'. (Microsoft SQL Server, Error: 262)"
Hi,
How to ennumerate the Rights of an User for all the tables [Select/Insert/Update/Delete] in a database or how to ennumerate/list all the Table rights for a particular user in a database? By User, i mean the Login names [like bill, sam, sa] and not dbowner, public, etc. thanx in advance.