Just a day ago, I received question from blog reader Mike McDonald.
“How can I modify permissions for SA user? I tried to modify dbo users permission but now I am having problems.”
First of all, there may be no relation between dbo user and SA user. They are different and should be left separate.
Modifying the permission of SA user is not possible. However, SA can be disable or enabled using following script. Make sure that you are logged in using windows authentication account.
/* Disable SA Login */
ALTER LOGINÂ [sa]Â DISABLE
GO
/* Enable SA Login */
ALTER LOGINÂ [sa]Â ENABLE
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
How to disable guest user in SQL 2008 ?
Hi,
I had scheduled a job of four steps.
step 1: I try to insert four records to a table.
success: goto next step
failure: go to step 4
Step2: display the inserted records
success: go next step 3
failure: go to step 4
write into the records to txt file
step3:delete all records.
success: quit the job reporting success
failure: goto step4
attach a file to write into the records.
step4: Printing the reason of failure statements
sucess:quit the job reporting failure
failure: quit the job reporting failure
“Print the statement into text field stating the reason for failure in the Txt field.
thanks,
Ravi
Hi,
I’m trying this, I’m logged on to the server using Windows auth as the Domain Admin and when I try enabling SA with
ALTER LOGIN [sa] ENABLE
I get
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.
Any thoughts?
I am getting a permission to registry error installing SQL Server 2008 on a machine that have already Dynamics POS installed.
Does this has any relation to “sa” user?
I am installing as administrator.
Thoughts?
Thank you!
If I disable a login, what will happen to the jobs which are owned by it?
for instance, a backup job we have is owned by sa. If I disable the login, will the job fail to run?
Hi Pinal,
I am consolidating all database users for reporting purpose, and I need to know if database users are disabled or enabled.
Can database users (not logins) can be disabled? Using sysusers or sys.sysusers, you can know if a user hasdbaccess. Can similar information can be found using database_principals?
Any help is appreciated.
Hi,
Does anybody know how to suppress the message like :
“Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”
after executing sp_configure ‘show advanced options’, 1?
I am attempting to disable or lock the SA account in SQL Server 2000. Is there a procedure by which I can do this? I am running mixed authentication. I need to make the SA account unavailable, essentially to render it unusable. We are required to change the password each time a DBA leaves, on 200 intances. Would rather disable the account than continually have to change the password.
Hi there,
I have been assign to display list of users who has DBO access and remove the DBO acces… However i was unable to create require script to complete work. Please advice…
Thank you… Thank you… Thank you :)
hey All ,,, required your help on urjent basis … new bee to sql server .
requirement is – need to create one new user on SQL Server2008R2 that would be having select right for only 2 tables .. what is happening user is having rights for other tables too … that i m not able to restrict. achieved on different version . let me know… what i m missing
Can you please give me some reason why to disable ‘sa’ account?
Special, if you have one sql login sysadmin like ‘sa’ and one sysadmin domain login.
Guys,
My master, temp DB has SA db owner. In case if i disable the SA login what will be the problem i will face it..
Hello, i have a nightly data load job, and it runs under a specific db user. I would like to keep all other users out until the loads are done. Is there a way to have just one user running the loads, and setup a job that runs every 10 minutes or so, to kill all other connections until the loads are done. Currently, i have a job that runs once, and kills all SIDs at midnight, before the loads start. However, there are times that users forget and schedule jobs past that time and it delays my data loads. thanks in advance.
How about simply setting the database in single user mode?
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
— your job —
ALTER DATABASE SET MULTI_USER ;