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
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 (http://blog.SQLAuthority.com)
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 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:
http://msdn.microsoft.com/en-us/library/dd207004.aspx
i can recommend this script:
http://archive.msdn.microsoft.com/addselftosqlsysadmin/Release/ProjectReleases.aspx?ReleaseId=3954
“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?
Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority
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?????????
i have one doubt in providing user permissions, we have a silcan/DBadmin sa group, this group have a 11 members i need to provide the READER permission for one of the member on this group “silcan/DBadmin”, how can i provide the permission ,could you please help me..