Hi All,
Is it possible to give a user a sysadmin role and then deny some of the privileges?
I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.
Hi
I'm new to SQL Server. I have created a databased named Sample and
I hae created the user with login named "Sman".
SMan owns some tables and sp's. I'm able to access the tables and SP's when I was logged in as Sman in Query analyser. I have given a Sysadmin privilege to Sman then I'm not able to access the tables and sp's when i try to login with Sman.
ie, Select * From tabl1 is not working But
Select * From Sman.tabl1 is working. I dont know Why is it so?
Can any one help me!
Thanks in Advance
Hi all,
Our SQL2000 server now allows all member of the Windows 2000 local administrators group log in with SysAdmin role.
I only want couple of people with sysadmin role. What should I do
to prevent that. I was told once that I should delete the
BUILTINAdministrator ID and manually add each window login ID
to SQL server. Am I on the right track?
Thank you for your help.
We set up a SQL Server 2005 box. When we set it up we did it with windows authentication, so the sa account is disabled. After everything was set up we were going to do some locking down. We added a domain account and gave it sysadmin rights. Next we went in to the sysadmin role and deleted the builtin/administrators group. This deleted all users except for the sa account. The sa account is disabled and we don't have a password for it. Not sure what to do next. Is there anyway to salvage this without have to completely reinstall? I can't enable sa because it says I don't have the rights, I can't add anyone to the sysadmin role because I don't have the rights. Why did it delete my domain account in the first place?
View 1 Replies View RelatedAlright, this should be a simple question, but I don't know the answer.
I created a group in our Active Directory, and added myself and another member of my team to the group. I then went into SQL 2005 management studio and added the group as a new login, and gave the group the sysadmin role.
The idea is for us to be able to connect with Windows Security to do administrative tasks on the server versus logging in with SA.
The problem, is that we cannot connect. It does not allow us to login. Do I have to do mappings for each database too?
Hi all,
Is it possible to reconfigure the sysadmin server role?
How can I see it's permissions?
Thanks,
Assaf
Listed below are some basic info about my application.
.net 2.0 web application
SQL 2000 database
Single sign-on. Windows Authentication with impersonate set to true in web.config:
<authentication mode="Windows"/>
<identity impersonate="true"/>
Connection String in web.config: <add name="conn" connectionString="Data Source=MyRemoteSeverName;Initial Catalog=MyDBName;Integrated Security=SSPI; " providerName="System.Data.SqlClient;"/>
The problem I'm having now is that any users that are not in the sysadmin server role can't read any table. Say if I have 2 users:
Domain1User1 ( in db_datareader and db_datawriter Database Role, no sever role assigned)
Domain1User2 ( in db_datareader and db_datawriter Database Role, sysadmin Server Role)
Here is what's expected to happen if everything goes well.
A list of customers ( first name, last name, age, etc) in a gridview should show up after login.
After both users logged in, Domain1User1 received an exception message of "Object reference not set to an instance of an object".
Domain1User2, however, was able to see the list.
I checked SQL Server Logs, and found 2 items:
Login succeeded for user 'Domain1User1' ... Connection TRUSTED
Login succeeded for user 'Domain1User2' ... Connection TRUSTED.
Based on the log file, it appears that both users had good connections to the database. Then why is it that User1, which is not in sysadmin Server Role, was not allowed to make a query?
Now if I assign sysadmin Server Role to Domain1User1 as well, the User1 will be able to see the list without seeing the exception.
Can someone shed some light for me please ?
Hi there. I work in a support department and on great occasion (such as this morning), I am RASd in to a client and try running a SQL trace, only to receive an error when setting it up, 'In order to run a trace against SQL Server you have to be amember of sysadmin fixed server role.'
Today, I even called their DBA and asked him if he could set our userid up with the proper permissions to all us to run traces (I'm debugging a RTE). He stated that he was unfamiliar with the error and didn't know where to assign us to resolve this problem.
Does anyone know how to resolve this?
Many thx.
My company has a Windows 2008 R2 server which is running SQL Server v11.0.5058. Â This server was previously running SQL Server 2008 and was recently upgraded. Â Since the upgrade I have noticed that when I connect to this server using SSMS and Windows authentication it seems as though I have a limited user context as I cannot see SQL Agent in the server tree at all and underneath the server security > logins folder I can only see the sa and SQL Server Windows service accounts (there are many more).
If I connect to the server using SSMS and the sa credential then I can see everything I expect to be able to see as a sysadmin.
I tried connecting as sa, then deleting my Windows AD account from the security > logins folder and reading my Windows AD account with the sysadmin role however this yielded the same result, when I connect using Windows authentication I still appear to be in a limited user context.
We have several other SQL 2008 / 2012 servers within our organization and all of them appear to be working fine / none of them exhibit this problem.
Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below
by the way I am a member of the sysadmin group
thanks in advance
km
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
Edition: SQL Server 2005 Standard
I am trying to take a snapshot of a database for use in a publication. The account under which the snapshot agent is running is set to have the db_owner role for the database and have write access to the snapshot share.
I can not get the snapshot to run unless the account under which the snapshot agent is running is granted the sysadmin fixed server role. Because of the security implications of this, I don't want to grant these permissions.
As far as I am concerned, the minimum requirements for the snapshot account have been met and I have tried every other alternate that I can think of. I've checked MSDN and the newsgroups but I still have not solved the problem.
The error that I get when I run the snapshot.exe from the command line is:
The remote server "TURING" does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
This error message has now inexplicably changed to:
You do not have sufficient permissions to run the command...
Any ideas? Thanks.
When I create logins for SQL Server 2012, something strange happens. When I assign the sysadmin role to a login, the login loses the access to the network drives (for example when creating or attaching databases). The only possible place is the c:drive of the computer that is running SQL Server.
View 11 Replies View Related
Question to those who may have had this same error- it seems that I am not able to delete some of the reports that I have created. This just started happening recently and according to our system admin nothing has changed as far as permissions are concernced. We installed SP2 the other day and I was wondering if this could have anything to do with the error message below
by the way I am a member of the sysadmin group
thanks in advance
km
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
View 5 Replies View RelatedIve been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies View Related Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
SQL Server 2000 SP5a on Windows 2000 SP4Friday morning we discovered that we no longer have sysadminprivileges. We were able to query the syslogins table. In the outputwe can clearly see that our accounts do have the sysadmin privileges,since there is a 1 in that column. But yet we do not have sysadminprivs!?!?!?!??!?!? Puzzling.We are not able to get into the SA account, since no one knows thepassword. But we are in BUILTIN/Administrators, and we have many SQLServer authenticated accounts with sysadmin privs. But yet none ofthem seem to have the privilege.Saturday I was able to restart the instance (actually, several timesnow), but that does not seem to resolve the problem. I have alsorebooted the server, which does not solve the problem.The next option would be to restore Master from a few days ago, butsince I have no privileges I cant even do that!!!Help? Ideas?
View 1 Replies View Relatedis there a difference in the previleges of 'sa' login and other loginwith 'sysadmin' role (and 'db_owner' for all databases) ?can they do the exact same things ?
View 1 Replies View RelatedHi
How do i set my domain administrator account as a sysadmin account for SQL? I have an error when installing SCCM but it just because my domain administrator account (which I use to install the SCCM) does not have sysadmin SQL Server role permissions on the SQL Server instance targeted for site database installation.
thank you.
enz
We€™re running mixed mode authentication on our SQL Servers. To make the server €œsafer€? builtinadmininstrators no longer have sysadmin role on the sql server. If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it? How could we reset the password or create a new sa account with a new password? This situation has not occurred, but I€™m worried about how to recover from it should it occur. This question relates to SQL 2000 and SQL 2005.
David Zokaites,
DBA & Software Engineer
Is is possible to hide "salary" or other sensitive data from a person who is a Sys-Admin. My belief is that there is no way. Please correct me.....
Assumptions: SQL Service account has Local Admin privelege.
Sysadmin can do anything on local machine, including run scripts adding themselves to any default/instance of SQL on the machine.
Please direct me to any other source of information for this topic.
Thanks for your input
I need to have a NON-sysAdmin, NON-Owner to be able to run specific jobs.
I'm ok on the non-sysadmin part, but how can I allow someone to run a job she does not own?
Thanks
Michael
Is it possible to show the user name (such as 'phuser') who is a member of the sysadmin group (NOT my idea!) I notice if you go to current connections is SQL EM the name shows, but if I login as that user if I try, user, user_name, etc inside of QA it shows DBO
View 4 Replies View RelatedHello everybody .
I have a group - SQL support.I want to give them rights to run any job from EM but I don't want them be a part of sa group
What rights should I give them ?
All existing jobs owned by members of sysadmin group.
Thank you
I have a login,
Default instance: SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER
on my server.
Looks like this is a sql service account. But my sql service is not running under this role.
Can I delete this login from sql?
------------------------
I think, therefore I am - Rene Descartes
Hi,
I need to give a sysadmin role to a user and I need to prevent that user to access some tables in specific databases...
thanks in advance...
I have a SQL2005 in a cluster environment, for some reason the only way that user accounts can login to either the database or SSMS is to grant them the SysAdmin role. This access is a little to high for my liking and am wondering if anyone else has come across this before.
Thank you
Hi guys,When I am trying to connect with SQL server 2005 as SQL server authentication mode in my won machine as a sysadmin user then a error is ochered."Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)". Please provide me a solution.
View 2 Replies View RelatedHow to determine to a login is a member of SERVER ROLE or DB ROLE?
I want to query all users are member of SYSADMIN, how to do?
Is there a way to deny Security Permissions to a login that has sysadmin? Unfortunately I have to leave the user as sysadmin. I trying denying alter any login and control server but that didn't work.
View 3 Replies View Relatedwhat is the command to grant sysadmin to the user?
thanks
Hello,I would like to know is it possible to disable drop database for saor sysadmin. If saor sysadmin needs to drop the database , he/she mayhave to change status in one of the system tables (sysdatabases ?) andthen only database can be dropped . This is to avoid dropping thedatabase by mistake by sa.In books online under drop databaseSystem databases (msdb, master, model, tempdb) cannot be droppedI would like to know how this is implemented for system databases ?ThanksM A Srinivas
View 3 Replies View Related