Editing SA Permissions MS SQL 2005
Jul 7, 2006Hi guys
Am i able to deny the SA account access to a specific database?
thanks in advance for your help.
TheGing
ADVERTISEMENT
Changed Context To ... Message When Editing Permissions W/ VBA
Oct 25, 2007I have a client using a small SQL Express database with an Access front end.
I've created a form in the front end that allows a supervisor the ability to perform two very basic security operations. 1) Adding a new user, and 2) granting/removing write privileges. The code behind the form uses VBA to build some T-SQL strings and then executes them via an ADO connection.
The strings are as follows:
1) Add new login to server:
"CREATE LOGIN [NETWORKUserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"
2) Add user to database:
"USE MyDatabase CREATE USER [NETWORKUserName]"
3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORKUserName' "
4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORKUserName' "
I have full privileges over the server & database, and everything runs perfectly.
However, when the supervisor - whose login includes security admin privileges - tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the code fails.
If I grant him "db_owner" status, the message does not come up and everything runs perfectly.
I'm not a security guru or anything, but I suspect that it's not the greatest idea to hand out db_owner privileges.
I ran a search, and the best I could come up with were a couple of KB articles:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;197459
http://support.microsoft.com/default.aspx?scid=KB;EN-US;143339
I'm not even sure these articles are talking about what's happening..
Any ideas?
Changed Context To ... Message When Editing Permissions W/ VBA
Feb 15, 2008I have a client using three small SQL Express databases with MS Access front ends. I've created a form in each of the front end apps that allows a supervisor the ability to perform some very basic security operations. The VBA code in the form concatenates some simple TSQL strings and then executes them via an ADO connection. The strings are as follows:
1) Add new login to server:
"CREATE LOGIN [NETWORKUserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"
2) Add user to database:
"USE MyDatabase CREATE USER [NETWORKUserName]"
3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORKUserName' "
4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORKUserName' "
Everything runs perfectly for me. However, when the supervisor tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the VBA code fails.
I've found two workarounds, but they have drawbacks that I'd rather not deal with
1) If I change the default database for his login to "MyDatabase1" it runs fine. But sometimes he needs to be able to perform these same operations in "MyDatabase2" or "MyDatabase3".
2) If I grant him "db_owner" status, it runs fine. However, I'd rather not hand out "db_owner" status if I don't have to
The only other idea I have to to build "EXECUTE AS.." stored procedures for each of these operations and then call those SPROC's from VBA.. Will this help? Any other ideas?
Please Help Me With Editing XML Files In SSIS 2005
Apr 28, 2006I am using a XML file and retrieving data for my SSIS 2005 (Intigration Service) package, where after retrieving the data I need to update my XML file with new data by using script task or XML task
<?xml version="1.0" ?>
<TimeBuilder>
<StartYear>2003</StartYear>
<EndYear>2004</EndYear>
</TimeBuilder>
Sql Server 2005 Questions Regarding Editing Views
Jun 21, 2006Previously in Sql Server 2000, we would be in enterprise manager, you'ddouble click on a view, and a nice little dialog box opened with the t-sqlstatetments, there was also a check sql syntax and apply and cancel buttons.Not exactly query anaylizer, just a quick lightweight dialog box. Is thisfeature still around? Seems like I have to go into the query anaylizer likemode to edit a view now. I am a total newbie to version 2005. Are there anyoptions I can set to make it behave the old way? All feedback isappreciated.TIA,~CK
View 2 Replies View RelatedVb.net 2005, Sql2005 Not Locking The Records While Editing
Dec 18, 2007I have created a database and used visual basic 2005.net2.0 to build the application. I used the data wizard. it created datatables and then I bound it to the contols on the form. The program is published to the server and each instance loads from the server and executes. When it stops, the instance is destructed from the host pc. The pc's are large ram and very small hard drive. These were the specs of the job. now when each pc loads an instance of the program, they can all load and edit the same exact record. I tried to bust it and I got on the same record and changed first name on one and last name on the other. the second one overwrote the first name edit. All of the data handling is done inside of the dataset designer and it does not allow edits. ( actually you can edit, but the next time you save it, or run it, it will rewrite what you added and it will be gone.) is there anything I can add to the form vb to tell it to lock the current record?
View 3 Replies View RelatedHow To Restrict Editing Of SQL Server 2005 Data Via ODBC Link?
Apr 25, 2007I have a sql server 2005 database with Delphi 2006 in the front end and for querrying and reporting we use MS Access 2003 by connecting to this database via ODBC connection. I recently found out that the SQL Server 2005 data connected thus can be edited (updated) from MS Access. I do not want end users to modify/update the SQL Server 2005 data from MS Access while I also want them to have the ability to insert/update/delete rights using the appropriate application interface. For now, I am handling this by creating a user id that is not permitted to update, insert and delete and using the same account in the ODBC. Is there a way in SQL Server 2005 you can control insert/update/delete rights for all users that will be applicable only in the ODBC mode?
Any help will be greatly appreciated.
thulo
Routine Crashing Of Visual Studio 2005 When Running Or Editing SSIS
Jun 7, 2006We have found that it is common for Visual Studio 2005 to crash when editing or running SSIS packages -- from CTP versions through beta versions and including the release version.
Of course we kept hoping that newer releases would become more stable, or at least more robust -- and now I'm hoping there will be a service pack, which might make it more robust?
How To Editing XML File Through Script Tack In SSIS (Integration Service) 2005
May 2, 2006Please help me with editing a XML file through script tack in SSIS (Integration Service) 2005
Thank you
Trying To Debug Sql 2005 In Vs 2005 No Permissions
May 4, 2008I am trying to debug a sp, I did it like a month ago and now i am getting msg:"
T-SQL execution ended without debugging. You may not have sufficient permissions to debug"
The server is a remote server
ANY help PLS!!!
Trying To Debug Sql 2005 In Vs 2005 No Permissions
May 5, 2008I am trying to debug a sp, I did it a month ago and now i am getting msg:"
T-SQL execution ended without debugging. You may not have sufficient permissions to debug"
The server is a remote server
ANY help PLS!!!
The dba says nothing has changed regarding my user log in or domains. I am a sysadmin user (he tells me) , I have looked up info but really dont understand any of the stuff, (i am a super dummy! and just started this job 5mo ago, and learned from reading a book, so i dont have any great understanding about domains or anything. Pls help
How To See User Permissions In 2005...
Nov 2, 2007In SQL/2000 EM I can go to a user Database, expand the Users, double click a user and click Permissions to see everything a user has permisson to in the database. How can I get the same information in Management Studio in SQL/2005? Is there an overview of this process in BOL 2005?
Thanks in advance!!!
DeWayne
SP Permissions Under SQL Server 2005
Feb 22, 2006I am just migrating to SQL Server 2005 and I am having difficulty
figuring out how to do some tasks that were easy under SQL Server 2000.
Specifically, I am not sure about object permissions.
(This is
what I did for SQL Server 2000) For database access by my web
application, I added a SQL login for the IIS_WPG group. I then added a
database user (name ASPNET) associated with this database login and
give it only datareader and datawriter privileges. I would then
'double-click' the user, which would bring up the list of securable
objects. I would then click the 'execute' permission for all of the
user-created stored procedures. [done]
For SQL Server 2005, I am
not quite sure what to do. It seems to me (based on what I see after
importing the tables), that a schema should be used, but I do not see
any explicit permissions in the schema. On the other hand, if I select
each stored procedure and look at its properties, I can see that the
ASPNET user has execute permission.
It seems inconceivable that
the only way to configure permissions is to modify each SP by hand.
This would take more than 10 times as long as the SQL Server 2000
method of assigning permissions.
So, my question is this:
How
can permissions be assigned 'wholesale'? (i.e., some method akin to the
SQL Server 2000 method that does not require setting permissions on
each individual object individually.)
Of course, if you care to suggest a better way to do this, I'd love to hear it!
Thank you.
2005 Reporting Permissions In IIS 6.0
Nov 20, 2007Hi. I created user: "Reporter"
This User is set as my anonomoys login user in IIS 6.0. I gave him permissions on both the database server & the Report server, however I keep getting an error. "The permissions granted to user 'SRVRReporter' are insufficient for performing this operation. (rsAccessDenied).
I inherited the task of syncing up these new reports & I'm trying to follow what the last guy did (not exactly though, that's why I created the new user)
I gave the new user login rights on both servers as well as access to the SQL 2005 database. I have the identity impersonate="true"
Can anyone out there tell me what permission I need to grant to get my reports to run?
Permissions For Table-SQlServer 2005
Feb 7, 2008Hi,
I have a table in my database where I want the Insert/Modify permissions to only administrator or (User X). Remaining users can just read the data. How do I set this in sqlserver 2005 database?
I can right click Table->properties->Permissions and add specific permissions to admin. But how would i deny permisssions to all others? I cannot add each login to deny permissions.
Thanks,
Granting Permissions Using SQL 2005 Schema...
Aug 15, 2007All,
I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.
I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].
I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.
Anyone have any ideas?
Thanks,
Justin
SQL Reporting 2005 Permissions Issue
Jun 13, 2007
Hi,
When i try to acces the rdl file from the aspx page i get the following error
"The permissions granted to user 'MACHINENAMEASPNET' are insufficient for performing this operation. (rsAccessDenied) "
Where do i need to give persmissions for this report to show up correctly?
Any pointers would be appreciated.
Thanks
Rohan Wali
SQL 2005 Express Permissions Problem?
Jul 20, 2006We have build and ASP.Net web application and also running another application that runs as a windows service.
We are having a problem with SQL 2005 express in that :the service and the webpages are able to access the resource individually,but when both are trying to access the resource, that is creating a problem and give us SQL access errors.
The application works perfectly under SQL 2000
Anyone have any suggestions?
Permissions Error Trying To Run SQL 2005 Express
Apr 3, 2007We have a windows forms app (VB.NET) that uses SQL Server 2005 Express in user instance mode. We have installed the app on hundreds of Win2K and WinXP PCs without any problems, but have run into an issue with one customer's site. They are running Win2K SP4 and we have installed the application on a couple of PCs at the customer's site to determine if it is environmental. The error occurs as the program is starting up the user instance of the database:
Error information:
System.InvalidOperationsException: An error occurred creating the form. See Exception.InnerException for details.
The error is: User does not have permission to perform this action, ---> System.Data.SqlClient.SqlException: User does not have permission to perform this action.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.TrhowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbCOnnectionOptions option, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(dbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbCOnnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbCOnnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
This error occurs at the first attempt to write to the database during the program startup. Any ideas?
Shawn
Execute Permissions Aspnet Sql Server 2005
Nov 29, 2006Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error
EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.
as far as im aware ive givne the ASPNET account the correct permissions
EXEC sp_grantlogin [MachineNameASPNET]
EXEC sp_grantdbaccess [MachineNameASPNET], [ASPNET]
** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.
Is there anything else I need to do ?
How Would You Modify These Permissions In SQL 2005 For Minimal Exposure
Jan 20, 2008I have finally gotten a setup to work, but I suspect there are improvements I should make to the permissions. Here's the setup:
I am accessing two databases, (aspnetdb and my own database called custom) on a local SQL 2005 server.
In SQL Server Management Studio (SMS) I created logins at the level of the SQL instance for two users ( "NT AUTHORITYNETWORK SERVICE" and "viewer"). Then I select each user, and set the properties to access the Server Roles section. There I found each was given the public server role. Now here's where I set the permission to sysadmin. This setting allows my application to work, but I'm sure there are less permissive approaches I should take. However, I just can't seem to find a simple and direct explanation of the procedure to set more appropriate permissions. The "NT AUTHORITYNETWORK SERVICE" accesses aspnetdb in order to create users and membership settings. The "viewer" login accesses the database called custom, and it reads and alters this database. The application (which is name viewer) performs these operations using a connection string like this:
Data Source=STORE;Initial Catalog=custom;Persist Security Info=True; User ID=viewer; password=xxxxx;
What is the recommended way to set the minimal permissions for these logins on these databases?
Permissions For Stored Procedures Generated By VS 2005
Apr 25, 2005I have a DataSet (Data Component in Beta 1) and I want to add Fill and
Get methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.
Permissions Not Visible - SQL 2005 Management Studio
Jun 30, 2005I'm trying to apply some security to my database, so i've created a role, added my user to the role and then set the security rights for the role. Everything work great, the security right behave exactly as defined....However, when I go back the the Permissions pages, the values that i've already set don't appear in the lists (the all still work). In Fact they don't show up anywhere either in the permissions for the table / sp concerned or the right for the user or role.Is it just me, or is it a problem with SQL 2005 Management Studio? or can anybody suggest a way of finding the securty rights of any given database object?Microsoft SQL Server 2005 Beta 2Microsoft SQL Server Management Studio 9.00.1116.00RegardsGary T
View 2 Replies View RelatedHow To Script User & Object Permissions In 2005
Feb 8, 2008Using Management Studio how do you script only user and object permissions? I don't want to script the corresponding "Create" statements for each object, only their permissions.
Thanks, Dave
Permissions For Developers Not Working After 2005 Upgrade
Feb 1, 2006Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.
After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?
Setting Permissions In Sql Server 2005 For Asp User Account
May 15, 2006How would Set permissions Sql Server 2005 so that I can access a asp page created in VWD and Sql Express.
On the production server i have just the one instance of Sql
I have the following connection string in the web config file:
<connectionStrings>
<add name="ConnectionString" connectionString="server=serversNameXPRESS;uid=aspuser;pwd=aspuserPassworkd;database=DATABName" />
</connectionStrings>
The account i setup to access the db in Sql 2005 Proper on the Production serve is Represented by the name in the above example as “aspuser�. I created this user in security, logins. And I gave permissions to this on the Db level “create procedure delete, select, update insert.�
I get a error when i run the page in the browser that says “login failed for aspuser.�
I know virtual directory is configured properly. I can run aspx page in the directory with out a db connection, without and error.
Any help would be greatly appreciated.
Minimum Permissions Required By The SQL Server 2005 Upgrade Advisor
Feb 15, 2008Hi all,
What are the minimum permissions required by the SQL Server 2005 Upgrade Advisor (UA)? I could not find it in the documentation.
Obviously being a local Administrators Windows group and a member of sysadmin SQL Server role will do the trick.
But will being a member of only the sysadmin SQL Server role be enough? I know that the UA does want to read the registry.
Running it under just sysadmin generates the following type of errors:
Database Server
PreUpgrade
Requested registry access is not allowed.
WINSOCKPROXY
Database Server
PreUpgrade
Requested registry access is not allowed.
FTUNSIGNEDCOMPONENTS
Database Server
PreUpgrade
Requested registry access is not allowed.
NETPROTOCOL
Database Server
PreUpgrade
Requested registry access is not allowed.
FTMULTIPLEINSTANCES
Database Server
PreUpgrade
Requested registry access is not allowed.
INVALIDNAMEDPIPE
Database Server
PreUpgrade
Requested registry access is not allowed.
FTCOMPONENTREG
Database Server
PreUpgrade
Requested registry access is not allowed.
FTACCTPASS
The issue then is whether these are significant or not. If the UA is only reading the registry to determine if SSAS, DTS, etc is installed then that is not important. But if it is affecting the end result because it cannot read critical information from the registry that is another matter.
TIA
Have Transferred Database && Users From Sql 2000 To 2005 But How Do I Transfer Their Permissions ?
Aug 14, 2007We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
any suggestions would be appreciated
Regards
Chris V
Sql Server 2005 Install On SBS2003 Fails Due To Registry Key Permissions
Mar 10, 2008I am posting this to hopefully help someone else that encounters the same issue in the future...
Server: SBS 2003 Premium, with exchange and with all service packs/patches applied.
Server-name: NEWSERVER
Server migrated from: OLDSERVER
Important notes:
This server was migrated from another SBS 2003 on different HW following the instructions provided by microsoft.
The oldserver had exchange and sql 2005 installed on it.
The new server has Office Accounting 2005 installed, but I don't think that matters...
I am trying to install SQL 2005 from the SBS2003-R2 DVD onto the new server, and get the following error:
Error:
---
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup failed to modify security permissions on registry key SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServerSuperSocketNetLib for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that the registry key exists on the destination drive.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29508&EvtType=sqlca%5csqlsddlca.cpp%40Do_sqlRegSDDL%40ExceptionInSDDL%40x7344
------------------------------
BUTTONS:
&Abort
&Retry
&Ignore
------------------------------
---
I looked in the registry, and the administrators group has full control over this key.
Digging into the SQLSETUP log file, at the end I see:
---
Configuring ACL:
Object: HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServerSuperSocketNetLib
ACL: (A;CI;KR;;;[SQLServer2005SQLBrowserUser$NEWSERVER])(A;CI;KR;;;NS)
Action: 0x100
Failed ACL:
ReplaceSDDLSid is failed at the error code 1332; Converted SDDL: '(A;CI;KR;;;[SQLServer2005SQLBrowserUser$NEWSERVER])(A;CI;KR;;;NS)'
Error Code: 0x80077344 (29508)
Windows Error Text: Source File Name: sqlcasqlsddlca.cpp
Compiler Timestamp: Tue Sep 13 01:08:29 2005
Function Name: ExceptionInSDDL
Source Line Number: 65
---
Looking into AD Users+Computers, there is not a group present for SQLServer2005SQLBrowserUser$NEWSERVER but there is one for SQLServer2005SQLBrowserUser$OLDSERVER.
It appears that the install did not create the new group that was necessary..
Once I duplicated the OLDSERVER group, renaming it to have NEWSERVER, the installation completed without error.
I hope this saves someone else a few hours of pain.
-Jason
www.protected-pc.com
Windows Accounts And User Permissions In MSSQL EXPRESS 2005
May 15, 2008This may be an idiotic question:
I am attempting to use Visual Web Developer Express with a connection to a SQL Express db from a non-admin account on my XP Pro SP2 machine.
I can do everything in the app under an admin login, but can't seem to configure the db to allow the non-admin account access to the db. I've tried tweaking WMI, using Network Service, Local Service, and Local System with NT AUTHORITY, individual logins, and group permissions, but I'm stuck.
Any thoughts?
Permissions In SQL Server 2005 To Allow Users To View The Management Activity Monitor?
Sep 27, 2006For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
Why Does Windows Login Have Full Permissions On Local Installation Of SQL Server 2005?
Jun 22, 2007Hi,
I recently installed an evaluation copy of SQL Server 2005 Enterprise Edition on my local machine and during the installation I used Local System system account for the SQL Server service and set the server to use Mixed Mode authentication.
I am able to connect to this local server Database Engine with my Windows login through SQL Server Management Studio and am able to perform sysadmin tasks. My question is why?
My thinking was that even though my Windows login would provide me a connection to the server, I would still have to manually add this login to the sysadmin server roles but after checking the sysadmin role, my Windows login isn't in there. The Windows login is not found under Security - Logins in SSMS either.
Can someone tell me should details for the login be visible on the server and why it seems to have sysadmin permissions ?
Thanks
Table Permissions Versus View Permissions
Aug 2, 2006Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies View Related