System Administrator Privilege
Jan 25, 2007In SQL 2000, how do I grant system administrator privilege to "sa" or a Windows User?
View 3 RepliesIn SQL 2000, how do I grant system administrator privilege to "sa" or a Windows User?
View 3 RepliesHi all
I deleted the sa account
how do I add it with all the correct permissions (plus include it in all the tables )
TIA
Guy
Hello all,
does someone know where to find information regarding what is accesible to a BUILTINAdministrator which is not accesible to a System Administrator?
Somewhere I have read that a System Administrator can not see the "All users' folders (i.e. the collection of "My folder"s for all users of the Report Server) but I have also experienced a behaviour which has surprised me: as a System Administrator with all permissions I cann't even see folders that a BUILTINAdministrator can.
Does it mean that a System Administrator can not really manage all the resources of a Report Server?
Any ideas will be welcomed. Many thanks.
Hi guys,
I am new to this forum and new to SQL.
I have a stored procedure which sets the value of a variable (@owner_logon_name, type sysname) to sa. It then calls sp_add_job in the msdb database, which in turn calls numerous other stored procedures. The ASP page I post down to the server is coming back with an internal server error and the log file shows the following message:
"Only a system administrator can reassign ownership of a job."
Even though the login name was set to sa, it seems to be using another role.
The code I have in my sp is:
exec @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @JobName,
@owner_login_name = sa,
@description etc, etc
Can anyone think why it would do this? An explanation of how sp_add_job works would also be much apprciated.
We recently downloaded and installed SQL Express as it was a required program for our new database management software. When trying to install my database management software I was asked to Login to the SQL server. I entered the SQLEXPRESS server, however, I do not have the SA password. When SQL Express installed it did not ask for a password only for a user name and company. I am running Windows Small Business Server with the Administrator and no password. We did not assign a password to the Administrator account yet.
I have tried to leave the password blank using the login SA and I get error message 28000 Login failed for user. The user is not associated with a trusted SQL Server Connection.
Is there a default password for SA? Or in the alternative, how do I create a user or associate an exising Windows user with a trusted SQL server connection?
I posted this topic and got not response, but I'm pretty desperate so I'll try one more time.
I'm a programmer at a small company but for the past year I've also been relagated to SysAdmin (fun, fun, fun). We less than 25 users (usually no more than 10-15 at one time). We run NT server on a P133, 64MB RAM, 3.5MB & 2.5MB SCUSI hard drives. We run SQL Server on dual P133's, 98MB RAM, 4MB & 2MB SCUSI hard drives. Our systems are pretty junky, they were bought at Microcenter a couple of years ago and some other machines we had bought about the same time have begun dying left and right. Our Server is very slow and we're afraid it's going to poop out on us soon.
I've been put in charge of taking care of this. "Get us a new server, and don't spend too much money." Being a programmer, not and administrator, I have a few questions:
1. Can I buy 1 good server to replace these two? That would mean running our NT File Server and our SQL Server (on NT of course) on the same box. Is that bad or can I do that? The only thing the NT Server is used for is file sharing (mostly images). No applications, no exchange (although maybe in the future), no internet. 2. If yes, should I go with a really fast desktop with a few ATA/66 drives, a smoking chip, tons of memory. Or, should I get a true server with SCUSI drives, ECC memory, RAID capable and all that. Essentially is it better to get a smoking desktop or a lower end/entry level server?
3. I don't quite understand RAID, do I need it? If so, what is it? We need to backup or data somehow.
4. Anything else you think I'll need? Or, could some list some minimum specs?
5. How much should this cost me?
Our main concern is the SQL Server running fast enough. We have a custom Accounting/Order/Inventory system (Access97-front/SQL-Back end ). The database is about 200MB and growing and the largest table has about 100,000 records and we anticipate about 20,000 records a year in that table. We also store tons of images (art company) on our file server and we would like to link our SQL inventory records to those image files.
I'm really kind of in over my head with the administration but the pressure is on. They want me to do this instead of outsourcing and they want to keep in the <=$3500 range (Server & Backup System). Any help or advice of any kind would be greatly appreciated.
Thanks.
I am using windows 8 and I have only one user in laptop. While installing SQL 2014, I am getting an error (Missing System Administrator). Under SQL Server Agent and SQL Server Database I have select the system user and gave the password but still I am getting the error.
View 8 Replies View RelatedI have inherited a sql server and no one knows the SA password. I cannot login with windows authentication even if the account is administrator of the machine.
View 4 Replies View RelatedHi,
Can any of you tell me how to create a login in SQL Server 6.5 with System Administrator privileges, like we can do with SQL 7 or SQL 2000 ? I don't want to use the sa login.
Thanks
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.
My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.
Our database structure is a trade secret and we cant reveal the structure to the client.
please answer this question by email to me at farhandotcom@gmail.com
Thanks & Regards
Farhan
We are trying to set up SQLAgent Proxy account. If the SQLServer service id is a domain admin, do we still have to add it to the local administrators group?
Jeff
Please help! I am new in SQL Server 2000 Administration. My manager had ask me to manage a SQL Server, but Windows Adminstrator refuse to give me Local Admin rights. I only have sa account to login to databases. I don't know any excuses or reasons to give to Windows Adminstrator so that he can give me the local admin rights. If any one have this answer, please help. Thank you.
-JC
it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?regards,
View 5 Replies View RelatedUsing SQL 6.5 SP4.
TIA.
Zak
hi, can anyone help me out with this report.I need to produce a report for the database level user permission.like for a database: DB_A, I want to output as such:[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] .... otherpublic G Dguest D D D D Ddbo G G G G Guser_A G D D D GG stands for Grant and D stands for Deny.I need not only to look at direct grant/deny but also whether that useris a member of fixed database role and fixed server role. like dbo willhave G on all.I am trying to look into sysprotects table and looking at lines with id= 0 but I just can't get to know what different action and protecttypenumbers means!Many thanks
View 1 Replies View RelatedHi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
I am recieving error 4606 Granted or revoked privilege SELECT is not compatiable with object.
Canot read the next data row for the dataset BEEP.
However when I use the same userid/pwsd in Crystal to access the same stored proceedure I have no problems accessing the data.
We have already tried re-granting the permissions to the userid to no avail.
Are there any other suggestions?
Phread
I'm using our development ID to set up peer-to-peer replication. I am following the steps outlined in the BOL using SQL Studio 2005. When I created the initial publication, I read the section on permissions and assigned the development login ID db_owner. Indeed, to eliminate any possible issues (I thought), I gave it db_datareader, db_datawriter, and all the db admin rights and made very sure the db_denydatareader and db_denydatawriter roles were not checked. I did this through the Logins section of the Security folder and applied the same rights to both the distribution and the publication database. Nevertheless, when I run the LogReader agent job, it fails with the message for step 2 that:
Executed as user: ourdomsqldev. A required privilege is not held by the client. The step failed.
The dev ID essentially has sa rights to the server and all the databases. It is also a domain authenticated ID. Any ideas what's causing this problem? I really need to get the replication going reliably before the weekend is out.
Thanks.
Randy
An user member of users,what's the sufficient privileges?
View 2 Replies View RelatedHi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon
I keep getting this error message on the subscription execution, even though the account is a domain administrator. Both publisher and subscriber are on the same domain. Can someone tell me what is the problem?
Message
Replication-Replication Distribution Subsystem: agent [Agent Name] failed. Executed as user: [Domain]administrator. A required privilege is not held by the client. The step failed.
Regards,
I am getting this error message "Replication-agentclassname: agent F1TESTSQLSERVER20-ELC-ELC_Pub-F1TESTSQLExpress-39 failed. Executed as user: . A required privilege is not held by the client. The step failed." All the users replication agents have failed and I am looking on how to resolve this serious error. Where do I start? I am using merge replication.
Thanks!
SAM
Hi. Thru a sproc, I drop & re-create some temp tables.When I call that sproc from the client, though, I cannot drop thetables.I need to allow the user, say "Alex", to drop/create tables (actually,that would be DDL). Which role should "Alex" assume ? How do I do that?I run the following sproc named, say, "CREATE_TABLE" (SNIP):__________________________________________________ __________________Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'Exec (@StrSQL)Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] ([GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...........[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]'Exec (@StrSQL)Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+ @TableName + '] TO [Alex]'Exec (@StrSQL)__________________________________________________ __________________As you can see, it is dynamic, because I need to repeat it for many@TableName values - that means, further more, that I will be executingthis in the context of the current user, Alex, and therefore I have togive Alex rights to both executing the sproc and to the tables referredto by the sproc, as specified by @TableName.I created a _TEST sproc which contains only the following:_______________________________________________CREATE PROCEDURE _TEST ASDROP TABLE [dbo].[SomeTable]RETURN_______________________________________________When I execute it from the client, thru ADODB, on user Alex, I get"User does not have permission to execute this operation on tableSomeTable"The table has been created thru "CREATE_TABLE", abovePlease help, I have to finish this tomorrow, and I'm under tons ofpressure.Thanks a lot,Alex.
View 1 Replies View RelatedI have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies View RelatedWhat I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy account
job failed and give above error.
Server is cluster and taking data from desktop.
server is in one domain and desktop in another domain.
I've read all the posts... set the security policy (for the service account running SQL Server) to lock pages in memory... ran gpupdate... still same error... HELP !!!!
View 1 Replies View RelatedI have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies View RelatedI am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
Hi Everyone,
I am new to sql server. I would like to now how to add somebody as an administrator so that he/she can connect to my instances. what is the easiest way to do it>? thanks....
I'm using Mix-Moded Authentication. Can I remove the Builtin/Administrator loggin ID without cause any harm to my current Login ID user(Windows NT user)? Are will removing Builtin/Administrator what harm will this cause to my server?
View 1 Replies View Related