Rights Assigned To WindowsPower Users In SQL Express.
Nov 9, 2005
Hi,
In SQL Server Express Edition, what are the rights that are assigned to a Normal Windows User and PowerUser by default ?
When I install SQL Express on a clean machine and login as Power User I can add/edit/delete data but when I login as Normal Windows User I can see the data but not change it.
Please help me in this regard.
When I restore DB from testing to production, we want to remove extra access rights granted to public group. Is there a simple way to query to find out for which objects (table, view, sp, fn) that public group were granted select, delete, update insert, or execute rights?
My objective is to write a sp to remove all user assigned rights to public group (role), but not to deny any rights. How to do it?
Is there a way to find a list of Windows User accounts that are directly or indirectly (through Windows Group membership) assigned to a database role?
I could put work in to CLR programming or using a Linked Server to Active Directory, but if there is a sys.* view available that can provide me this information directly it would be much easier.
I'll be looking into this further myself anyway and posting an answer if I can find one, but if anyone has suggestions... well, thanks!
I've restored the dev db from the prod backup which overwrote the users and their rights in dev db. Is there any way that I can find out what those rights were? I have the list of users in dev db but not their rights . Thanks.
I have a problem that im using Active Directory in Win2k Server, when domain users logon to workstations they can't open sql server databases, SQL server is local installed on Workstation and operating system is XP.
When i give administrator rights to users the sql server works fine.
Tell me is there is a way to use sql server without giving to the user administrative rights?
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)"
I'm a beginner in SQL server admin and have just set up SQL Server 2005 in a Vista environment. So far so good! What I've got a problem with now is to get rights set up properly in the MS SQL Server Management Studio Express sw. If I try to add another database or add a new login and such, the results is all the time insufficent priviledges.
CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262) for instance.
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'm a newbie to Reporting Services so please forgive my ignorance...
I've recently installed SQL Server Express 2005 with Reporting Services on my Vista Business Notebook (with no network connections) and have installed VS C# Express as well as the Business Intelligence Studio to create reports.
My C# application can connect to SQL Server fine, however, the reports I created with Business Studio refuses to deploy to the server due to the following reason:
"The permissions granted to user 'localhostJenny' are insufficient for performing this operation".
I find this odd as I've been able to deploy reports on my other Windows XP machine (with SQL Server Express) no probs at all. Additionally, I cannot see the 'Site settings' link within Report Manager (Web) that I'd normally get with an admin account (i.e. on my Win. XP machine). Various sources indicate that my account has insufficient access rights to view this.
Having browsed through seemingly endless blogs and forums, I suspect that my local account has not been configured to access the Report Manager web client to publish, manage and administer the site.
I have tried the surface area tool for SQL Server, but I suspect that this is relevant only to SQL Server DB access and management. Adding an administrator here does nothing.
The question is, how can I grant access to myself (local account) to allow me to publish reports via business studio, and at the same time - administer Report Manager Site Settings - on my vista notebook??
I would be extremely grateful for any help on this matter - I've tried ceaseless reinstallations, but to no avail!
Are there any limit of simultaneous users and/or connections when running SQL Express? The free license of SQL Express, does that include use by any number of users/devices?
we're planning to use sql express as our database server in our office. the database will consist of accounting, purchasing, marketing, sales, inventory, production and hrd tables. 80-100 users will use the system simultaneously in adding, editing, deleting, reports. i need some advice on the following: 1. maximum users that can log on to the database simultaneously? 2. maximum size of the database?
I have now succefully setup SQL Server Express on an Admin PC in my office and can see it and connect to it easily from my PC using my NT logon.
My application is complete and runs a treat for me connecting to the server from my PC.
I have now deployed the application using ClickOnce and it has all gone smoothly, except for other NT users gaining access to the database?
Within MSMSE I have clicked on Security-Logins and can see my NT login details are present and as me I can administrate the server/db.
I have added a few new logins and even tried to match them to my settings but from their computers the application fails to logon to the server/database.
Within the ODBC setup the server is visible for them but they cannot connect?
If I logon to their computer it works for me?
This section is new to me so I am struggling to get it to work, can anyone throw me a bone please?
Hi, i just need some help?? just some info?is it TRUE that SQL Server 2005 Express Edition (free) is capable ONLY of SINGLE user?i mean if i have a data driven page, if someone is using or editing the data in my page, does no one can access my data base simultaneously??i hope you could enlighten my mind, thanks
I am sure that I am missing something which is why I am in need of the communities assistance...
I have installed SQL Server 2005 Express Edition. I can log in with the Windows Authenication and the SQL Server Authentication using the 'sa' account. This is all good...
What I want to be able to do is add 1 to 3 more users so they can gain access to the database I have created to run queries and add data. This is the part I am confused about. How do I get additional users added??
I have developed a Windows App in VS.NET 2005 which connects to a SQL 2005 Express database.
Now I want to install the app and database on the network and I am getting an error "File 'file_name' is on a network device not supported for database files"
I am moving some code that was created in visual studio 2002 into Visual Web Developer 2005 express edition and am getting a warning on some of my code before compilation. The warning is "Variable lSqlTransaction is used before it has been assigned a value. A null reference exception could result at runtime". I experienced no problems in vs2002. The offending line is the rollback command within the exception. If I move it out of the exception it no longer flags the warning, if I move it after the throw statement it now longer flags the warning. Any help appreciated. Heres the code Public Shared Sub ExecuteNonQuery(ByVal lSQLCommand As SqlCommand)
Dim lSQLConnection As New SqlConnection(SQLConnString(enmSQLDB.enmSQLDB_TPSDB)) Dim lSQLTransaction As SqlTransaction
Try '----------------------------------------------------- 'Try and open the database '----------------------------------------------------- lSQLConnection.Open()
'----------------------------------------------------- 'Create a transaction '----------------------------------------------------- lSQLTransaction = lSQLConnection.BeginTransaction
'----------------------------------------------------- 'Assign the connection and transaction to our object '----------------------------------------------------- lSQLCommand.Connection = lSQLConnection lSQLCommand.Transaction = lSQLTransaction
'----------------------------------------------------- 'Execute the stored proc '----------------------------------------------------- lSQLCommand.ExecuteNonQuery()
'----------------------------------------------------- 'Use the transaction to commit the changes '----------------------------------------------------- lSQLTransaction.Commit()
Catch ex As Exception '----------------------------------------------------- 'If any errors have been thrown then roll back without 'commiting.... '----------------------------------------------------- lSQLTransaction.Rollback()
Throw New Exception("An error has occured whilst trying to update the database. " & _ "Your changes have not been saved.", ex)
Finally If lSQLConnection.State = ConnectionState.Open Then lSQLConnection.Close() End If End Try
I'm tryin to copy information from a fact table from one server to another server but when try using a DTS package to do this, an error pops up that says "the Filegroup <filegroup_name> has no files assigned to it."
We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.
We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.
The code used to grab the identity and time of the last update is shown below.
LastEditedDate = GETUTCDATE(),
LastEditedBy = SUSER_SNAME()
What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.
For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?
I have a problem with datatype mismatches. In my Data Flow I am reading a Flat File that includes a DateOfBirth which SSIS correctly assigns a datatype of [DT_DATE]. I need to check to see if the record has previously been submitted, so I do a Lookup that uses a SQL statement that combines fields (LastName, Zip & BirthDate) from three different tables. My problem is that SSIS assigns a datatype of [DT_DBTIMESTAMP] to BirthDate, which I can't use because of the mismatch. I have tried every form of re-formating the field in SQL that I can think of, but it still gets assigned that DT. And I'm unaware of a way of changing the DT once SSIS assigns it in the Lookup task.
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
Hello, Is there a way to assign multiple variables to one select statement as in the following example? DECLARE @FirstName VARCHAR(100) DECLARE @MiddleName VARCHAR(100) DECLARE @LastName VARCHAR(100) @FirstName, @MiddleName, @LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'
I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.
I have a report where certain columns have values that get repeated, but the client has a rigid requirement for not wanting these columns as groups in the reports (other programs and exports doing specific tasks with those values). In these reports, the "hide duplicates" value is checked, so as to give an aesthetic sense of grouping.
The data for the report, in raw form is:
Value 1 A
Value 1 B
Value 1 C
Value 2 D
Value 2 E
Value 2 F
Value 3 G
Value 3 Hetc...
In its aesthetic form, it is:
Value 1 A
B
C
Value 2 D
E
F
Value 3 G
H
My question is, how can I use RunningValue() to give me the following row numbers based upon those repeated values acting as grouping, since I'm not using formal grouping in the report design?
This seems like a simple question but I still haven't been able to get an answer from Microsoft Canada. We're looking at buying 75 device cals to connect to a single instance of SQL Server Standard Edition. Our question is on how those licenses are assigned and/or activated.
Do we need to manually do something for each station or is it an automated process done the first time a station connects to the server? We also wat to understand what happens when a workstation dies and is replaced by another one. Do you have to do something on the server to transfer the license to the new station ?
In other words, when using device cals (and/or user cals), do you have to do any type of license management or is it all transparent ?
At our site, we page DBAs whenever an alert with a severity of 20 or greater is raised. This never was an issue until SQL Server 2005. Now every SSPI error is paged since it is a severity 20. I don't believe the errors are severe enough to qualify an assignment of 20. Anybody else have any thoughts on this?
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
I restored a sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio. $Gorlaz(Restricted User)
Can anyone help me with this? The objective is to allow an application user (with db_datareader & db_datawriter database roles assigned) to be able to create tables in the assigned schema (dbo) via a new role.
-- Create User
use master
go
create login DBA with password='xx', CHECK_EXPIRATION=ON, CHECK_POLICY=ON
use AdventureWorks
go
create user dba from login DBA alter user dba with DEFAULT_SCHEMA=dbo
go
-- Create Role
use AdventureWorks
go
create role sp_ddl_role AUTHORIZATION dbo
grant CREATE TABLE to sp_ddl_role
-- assign user to Role
use AdventureWorks
go
exec sp_addrolemember sp_ddl_role, dba
go
-- Create Table statement then run with following error Error:
Msg 2760, Level 16, State 1, Line 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
Remedy: Grant ALTER on schema::dbo to sp_ddl_role
My problem is that I want to restrict user permissions via this role to just the CREATE TABLE and granting ALTER on a SCHEMA will open up a whole lot more permissions from a security standpoint.
Question: How do I restirct this role to just the CREATE TABLE within a SCHEMA?
In SQL2000, when the Guest account was assigned into a role, such as db_datareader, then querying across databases worked just fine.
Specifically: I have a Report Writer application that connects to the SQL Server with a login (ReportRunner) that actually has very limited permissions on a database. The connection is then set (sp_setapprole) to use an Application role (App_RR) that has the necessary permissions. The report-writer app calls a Stored Procedure that gathers data from several other databases (on the same SQL instance). In SQL 2000, accessing these other databases was done through Guest - we assigned Guest to the db_datareader role. All worked fine.
We've just upgraded to SQL2005: reports started failing. It seems that although guest is assigned to the db_datareader role, the permissions for Guest don't allow selecting from tables via the db_datareader role: we've had to GRANT SELECT TO Guest specifically on the tables necessary for the report.
Is anyone aware of a design change withing SQL Server such that the Guest principal's roles are disregarded when assessing permission? Is there a new and better way to structure the permissions?
We have been storing packages in the File System folder. We had noticed that there seemed to be times when re-importing an existing package did not seem to update it properly. We tried deleting the existing package first rather than overwriting it, but to no avail.
Today we noticed that there were two DTS90Packages folders, one on the C drive and one on the D drive. The dates on the files in those folders showed that sometimes the import put the file on one drive, and other times on the other drive.
The MsDtsSrvr.ini.xml file shows this: "<StorePath>..Packages</StorePath> ".
We intend to stored the packages in the msdb database instead in order to work around this problem.
in visual studio web developer express edition 2008 i added a new item this was a sql server database which i called test2. I added a table called comments, and the fields name, email, comments, IPAddress, and Date and in my comments.aspx page i added a name textbox, a texbox for email, a textbox for comments and the submit button i double clicked on the submit button and added the following code Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim mydS As New SqlDataSource mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString1").ToString mydS.InsertCommandType = SqlDataSourceCommandType.Text mydS.InsertCommand = "INSERT INTO comments(Name,Email,Comments,IPAddress,Date) VALUES (@Name,@Email,@Comments,@IPAddress,@Date)" mydS.InsertParameters.Add("Name", TextBox1.Text) mydS.InsertParameters.Add("Email", TextBox2.Text) mydS.InsertParameters.Add("Comments", TextBox3.Text) mydS.InsertParameters.Add("Date", Date.Now) mydS.InsertParameters.Add("IPAddress", Request.Params("REMOTE_ADDR")) Dim rowsaffected As Int16 = 0 Try rowsaffected = mydS.Insert Catch ex As Exception Server.Transfer("errors.aspx") Finally mydS = Nothing End Try If rowsaffected <> 1 Then Server.Transfer("errors.aspx") Else Server.Transfer("confirm.aspx") End If End Sub Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged End Sub but when i run it it gives me a problem with this line mydS.ConnectionString = ConfigurationManager.ConnectionStrings("testConnectionString2).ToString and says something abt unidentified object and I want to know how do i set the connection string or alternatively where?????? and my table does not update with the corresponding values i enter on the webpage. i also changed the authentication mode to forms instead of windows as i was advised but i'm still having a problem