The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.
SQL SERVER – DISABLE and ENABLE user SA
SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission
The question I had received is following: “How to add any user to systemadmin role?“
This is very simple process.
Method 1: T-SQL
USE [master] GO CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin' GO
Method 2: SQL Server Management Studio
Reference : Pinal Dave (https://blog.sqlauthority.com)
37 Comments. Leave new
installed SQL Server 2005 Express Edion. But in VIEW options, i can not find SOLUTION EXPLORER and TOOLBOX.
What kind of problem is that ??? Help me to fix it pls, it will be apreciate so much.
Thanks
Method 1 is not working in SQL Server 2005
and getting the below error….
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘LOGIN’.
Msg 15002, Level 16, State 1, Procedure sp_addsrvrolemember, Line 17
The procedure ‘sp_addsrvrolemember’ cannot be executed within a transaction.
Hi Pinal,
I have a situation where someone asked me to check data in a table by name “Material” over phone. But when I checked for the table in the database, it was not there. I called back that guy to confirm the tablename and he said “I am sorry. The tablename was Materials”. He added the letter “s” to the table name.
The point I am trying to make here is…..at the time of writing a query in particular database, would it be a good idea to have some kind of color coding which tells me if the table exists at all while typing the query itself. Otherwise, I am wasting my time typing the whole query and receiving an error message……..I hope you got my point…….
Hi Srinivas,
Please compile the below procedure in master datbase.
CREATE procedure dbo.sp_f
@name varchar(40),
@type varchar(2)
as
begin
if @type in (‘U’,’V’,’S’)
begin
select ‘select * from ‘+name from sysobjects
where type=@type
and name like ‘%’+@name+’%’
order by name
end
if @type in (‘P’,’TR’)
begin
select ‘sp_helptext ‘+name from sysobjects
where type=@type
and name like ‘%’+@name+’%’
order by name
end
end
Then you can call exec sp_f ‘mate’,’u’
if will list down all tables that contains the key word mate. Hope this solves your problem.
Hi Pinal,
If we want to add a Domain User as a Sysadmin on SQL.
Do we need to create Login in SQL and then add it as a Sysadmin?
I tried to add domain user directly as a sysadmin and it also created as Login(user) in SQL.
Command i used:
sqlcmd -S MachineName\SQLEXPRESS -q “EXEC master..sp_addsrvrolemember @loginame = N’Domain\User’, @rolename = N’sysadmin'”
Aniket
Dave
Let me know if there is a way to do this in sql server 2005
I have a DB server in which i will be adding/dropping users very frequently as per the requirement. Now i would like to implemet this from a stored proc in which it has to do the follwing.
1. List users in a DB
2. Add user to the DB with one of the Db role
3. Remove any users if required
4. Run a script to lock few tables in the db for that particular user.
I am not sure if this is possible from a store proc but would really like to do so, I understand it involves has admin previlages but i thougt of creating a sql user with admin access to run this proc.
Great, I thought I was at a loss and had to re install and reconfigure. I love my tech community!
hi pinal,
it’s very useful to me
hi pinal, now i am learning sql, please give me some tricks to understand and write queries…
First Learn SQL. If you have any doubts ask here
Hi,
Thank you. It has helped me a lot for granting rights to the user. It is simpler to understand and do it.
Thanks a lot
Manaa
hi, can the database be open for users, means can any user add data. for example, currently running on access 2007, any employee can add there jobcard. If I migrate to sql2008, is this possible. pls reply. thanks. [email removed]
Hi Dave, I have followed your posts etc and used your wonderful knowledge over the years (won’t say how many). I have been away from DBA for awhile. I’m working in SQL 2008 first time and can not get a sysadmin setup on the sql instance.
When sql was installed a sysadmin was setup. I can log into the server with this admin account and then connect to SQL. I then added myself (windows acct – domain admin) to sql as sysadmin. Then I tried logging into the server as myself and connecting to sql as myself. No go…. I get the following error:
A network-related or instance-specifi error occured…. you know it. Since I can connect this way with the Domain account that was setup as sysadmin at install I know this works and remote connections are setup etc.
There are 2 instances and I added myself to both, what am I missing? Is there an AD component? Do I need to restart sql services after adding users (not in my experience) do I have to log off and on?
Thanks
this is what microsoft supplies about this problem:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017
i can recommend this script:
“The role does not exist in the current database” what is this error while adding server rol to database???
hi every body
i want create user in sql server 2005 to only access a one table
how i can do it
Hello, I have a server on a machine which has had it’s name changed (has moved from being a local only machine to one connected to a domain with a new name. The database is not letting any account access to it. I am trying to create those permissions. The only user with sysadmin rights on the server is sa. The password for this has gone missing.
I have followed your instructions above for creating a new sysadmin, using sqlcmd run as administrator on the machine. I get the following error at the create login line:
msg 15247, Level 16, State 1, Server MYSERVERNEWNAME, Line 1
User does not have permission to perform this action.
Is there anything i can do to either create a new sysadmin, or sort out the issue with the name change?
i have installed sql server 2008 but i am not able to create a database in it
its throwing an error like permission denied to create a database. so can any one help me out in solving this
I have to do Same thing from Command Prompt
can a user be w/ sysadmin role but deny user from truncate or delete?
can you please help me to create new user with Mixed Authentication?
can any one tell me what type of sql code used by tester. since i am new in testing to what i need to practice for?????????