Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

SQL Server 2005 / Securityadmin Role / SSMS

Experiencing a little weirdness with SQL Server Management Studio...

Have created a login, that has fixed server role "securityadmin".
The login can create/drop logins, add/remove them as users in
databases, etc.

The login can change another login's password, using SQL
from a query window (e.g. alter login bob with password='abc')

However, the login *cannot* change another login's password,
using SSMS GUI (e.g. "Security", "Logins", double-click "Bob",
change password, say "OK"). The error text is:

Change password failed for Login 'Bob'. (Microsoft.SqlServer.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or
Cannot alter the login 'Bob', because it does not exist or you do
not have permission.
(Microsoft SQL Server, Error: 15151)

Has anybody else experienced this?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Problem Adding Securityadmin Role To Sa In 2005
Hi Guys,

I'm trying to add the securityadmin server role to the sa user but have been unable to due to an error occuring. The error says "Add member failed for server role 'securityadmin'. Cannot use the special principal 'sa' Microsoft SQL Server error 15405."

I assumed that the sa account would already have the required permission to perform most operations on the server but I have been asked to try assiging this server role to sa by a support guy for our third party product.

Has anyone got any ideas why this error is occuring? I have been looking on loads of forums but cannot find anything relevant to me.

Thanks in advance for any help!

View Replies !   View Related
Sql 2005 Database Role Vs Application Role ?
Ive 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) ?


View Replies !   View Related
SQL Server 2005 Take Long Time In Domain To Load SSMS
I installed sql server 2005 ent in windows advanced server 2000 in WORKGROUP as default installation process and select allservices in local server.At this situation thereis no problem in database to load SQL serverManagment Studio (SSMS) after logon as Administrator. Then Join the server to the Domain. Logon to server as Domain Administratorand when we try to loadSSMS, its take a long time(10 to 15 minitues) to load SSMS and when we try to connect it also take a long time to connect to databaseand successfully connected. Sratnge behaviour is that after logoff and logon as local Administrator to Local Machine(This computer) it works normally and SSMSload fast & normally and conneted to database also normally fast.

Where is the problem?

View Replies !   View Related
Web Application And Role Management In Sql Server 2005
Hi every body

 I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this??

by Thanks




View Replies !   View Related
SQL Server 2005 Application Role's Problem

Hi all.

I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005.

My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables:
1) Table A
2) Table B
3) Table C

The USER login is a restricted user which has only SELECT permission to Table A, B and C.

I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime".

Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A.

I have tried for almost 2 days but am still clueless. Any ideas?

Thank you so much.

View Replies !   View Related
Implementing Membership And Role Management On SQL Server 2005?
How can I implementing Membership and Role Management on SQL Server 2005 (not Express)? Anyone have any documentation? Are there any scripts to run that will basically set up the same Database schema on SQL Server 2005 that Express uses?

View Replies !   View Related
Creating Databases Under MS SQL 2005 Server (SP-2): Dbo User Role
Good Day,

I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server,Error: 15150). I am connected to my database engine through the "sa" account.



View Replies !   View Related
SQL Server 2000 And 2005 - Application Role - Sp_setapprole
Hi All,

Is there any limitation for setting password to an approle (like it should be 8 character long,should not start with numbers) ?

If so, what are those limitations ?

Does the same is applicable for sp_setapprole which uses the same password ?

please confirm, for both SQL Server 2000 and 2005 versions.

thanks in advance,



View Replies !   View Related
How Can Create User In Reporting Server 2005 And Assign To Role
how can create user in reporting server 2005 and assign to role

i convert reporting server to forms authentication

and i want create user from my applection and assign to role

thanks for all

View Replies !   View Related
Uploading SQL Express To SQL Server 2005 - Role's Causes Application Error With SSE Provider
I am very frustrated.  Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: 
The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.
The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard.
Please help!

View Replies !   View Related
Can't Make Database Role A Member Of Another Database Role In 2005.
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 Replies !   View Related
SSMS Fails To Connect To SQL 2005
We are running SQL 2005 Standard on Windows 2000 Server Standard. We are trying to connect via SSMS with the Object Explorer to the server and itis failing with an error message stating:

"An exception occurred while executing a Transact-SQL statement or batch.

Additional Information:
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)

Using profiler, I tracked down that it is a call to xp_instance_regread. This is something that SSMS is issuing - not me.

We've found in the past that rebooting the server fixes the problem. But it keeps cropping up. The only thing running on this server is SQL 2005 - so if something is monkeying with the registry - it's got to be SQL Server itself.

Querying works just fine - we can use the editor to perform queries without any issue - it's just explorer that goes kaboom.

We have SP2a installed on the server and on most (if not all) of the clients.

This is getting frustrating because it's a production server and we're having to reboot this to fix it.

View Replies !   View Related
No Available Databases In A New Query In SSMS 2005
When I create a new query window in SSMS 2005 (SP2) , I do not have any available database in de SQL Editor pane. Though in the Object Explorer window below, I can access all objects of all databases. The only way to query a table is by using the 'Open Table' feature in the Object Explorer window, then right-click on Pane-->SQL .

I also can open a stored procedure, but not modify it as the SQL Editor pane is disabled.

The SQL-Editor pane is available for all the other servertypes (Analysis, Reporting, Integration).

All services all started.

I re-installed completely SQL2005 and SP2 : nothing changed.

What am I missing ???

Versions = Microsoft SQL Server Management Studio9.00.3042.00

SQL Developer Edition 2005 (Service Pack 2)

Windows XP Pro 5.1 (build 2600 Service Pack 2)

Any help is welcome !!!

View Replies !   View Related
Making Inegration Services Visible/available In SSMS 2005
I have installed SQL Server Management Studio 2005 and have beenfinding the equivalent of the Data Transfer Services in SQLServer2000. I think I have worked out that it is in SQL Server IntegrationServices but when I open File-New-Project I do not see the SQL ServerIntegration Services there, though Help assumes that it is availablefor selection when starting a new project..How can I make it visible in the New Project window?I have successfully used the import function so I think this meansthat Inegration Services is there somewhere.I would appreciate some advice,Best wishes, John Morgan

View Replies !   View Related
Report Works In Preview Mode But Not In SSMS 2005

I've created an SSRS report in Visual Studio 2005 which runs fine in the preview. I have a list of server names which when clicked on expands to a list of databases on the chosen server. When I click on a database it then queries a table of information and displays information on that database. The query is something like:

SELECT * FROM MyDB.dbo.DB_info
WHERE ServerName = @ServerName and DBname = @DBname

This works fine in preview mode. When I run it in SQL Management Studio via the Custom Reports menu it only works when the chosen server is the one that contains MyDB. I've run a trace and can see the query being issued when I use preview mode. However, when I run it from SSMS I don't see any query being issued to the database. This seems strange as it does return data in the instance described above and if I put MyDB into single-user mode it then complains that it can't access it so it is obviously trying to communicate with the database but not showing up on the trace. I've selected all events and only put a filter on the database name.

Seeing as all of the information is in the same table it seems odd that they query only works when the server name is the one where the database is held and that the query is not shownup in a trace. I'm running the report under the same credentials too.

I'm completely baffled by this so I'd be very grateful if someone could explain what might be happening here.


View Replies !   View Related
Use SSMS With SQL Server 7.0
Is there any way to use SQL Server Management Studio to manage a 7.0 database? I have a few old databases still in 7.0 and would really like to manage them through SMSS. Am I out of luck?


View Replies !   View Related
Application Role And SQL Express (2005)

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005



View Replies !   View Related
SSMS Linked Server Connection For DB2
I am trying to establish a OLEDB connection for a db2 server from SSMS adn some one tell me the settings. I haver tried different options but failed.
This is my connection string from SSIS
"Data Source=DB2W;User ID=myid;Provider=IBMDADB2.1;Persist Security Info=True;"

I gave
Linked Server: DB2W
Provider: IBM OLEDB provider for DB2
Product name: ???
DataSource: ???
Provider string:???
Location: ???

View Replies !   View Related
Sending SQL 2005 Dbmail When Using Application Role
I am attempting to use dbmail from an application that logs in to my database using an application role.  Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it:  I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.

Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail.  But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission."  When I run my spSendEmail stored procedure from the calling database, I get the same error.

View Replies !   View Related
Sql Server 2008 Express With VWD2008 And SSMS


On a windows 2003 server R2 x64, i had the 3.5, Sql Server 2005 express (instance s423553 on NT SERVICENetwork) and Visual Web Developper 2008.

I installed Sql Server Express 2008 Express (install .SQLexpress with the local account sqlserv10). The first time (with the account NTSERVICENetwork), it failed. I uninstalled it and Sql Server Management Studio 2005. The second time, the install end properly. But, can't find SSMS. On a MsSql2008 microsoft page, i've found a link for SSMS 2005. So i installed it.

But, on this server, Sql Server Management Studio Express 2005 does not work with Sql Server 2008. I tried to use the 2008 Instance with Visual Web Developper 2008 but it didn't work (only compatible with sql server 2000, 2005....).

Could you help me please ?

Thank you.

View Replies !   View Related
How To Drop A User Defined Database Role In 2005?
Using Studio, I created a user defined database role but I can not delete it because

"TITLE: Microsoft SQL Server Management Studio

Drop failed for DatabaseRole 'test1'. (Microsoft.SqlServer.Smo)


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

I am quite annoyed because the "owned schema" is db_owner, whichcan not be unselected. Quite an innovation. How do I drop this relationship?

View Replies !   View Related
Unattended Install Of SSMS/BIDS After SQL Server Express

I'm currently trying to develop an unattended installation of the Client Tools to be distributed to developers workstations.

All is well on 'clean' machines, but the problems arise when an instance of SQL Server Express is already present. The install fails, and thereason isn't clear from the log files.

I know Express installs a couple of the Client Components (like Config Manager), and I believe this is what it blocking me.

Has anyone tried this scenario?

Would I be best to REMOVE the Client_Components first, and then re-install them with SSMS etc.? Or is there a better method?

I have to leave the Express Database instance untouched as I'm not responsible for its installation or maintenance.



View Replies !   View Related
Cannot Connect To Server Through Ssms - Cannot Generate Sspi Context

I have gone through so many kb articles to understanding the problem, but no luck as yet.

Here is the situation:

I have two dev servers : Server A and Server B. Both are set up in an identical fashion. Windows 2003 sp2, Sql Server 2005 sp1 Developer Edition, both are on the same domain, Both have all the three protocols enabled: Shared Memory, Tcpip, and Named Pipes in that order. I can ping both servers, and telnet using the default port. Both have only default instances. Both use the same domain user account for sql service. Both have the error in the log saying that spn could not be registered so authentication may fall back to NTLM.

Here is the issue: When I try to register Server A specifying tcp-ip connection, I am successful and I can see that the authentication is NTLM - is as expected. since the spn registration did not happen and since the sqlservice account is not a domain admin, the authentication used NTLM over tcp-ip. However, I try to register Server B specifying tcpip, I get a cannot generate sspi context error. Why did the authentication not fall back to NTLM as in Server A? From what I read in the KB articles, I asked our network admin to check up the spn, but he assures me that spn were never manually registered.

Can somebody throw more light on this issue?


View Replies !   View Related
SQL RS Upgrade From 2000 To 2005 RSExec Role Not Created In Master Db
My production environment uses a remote catalog configuration. I am following the instructions from SQL Server 2005 Upgrade Technical Resource Guide to do a side by side installation on new servers for Reporting services and the back end database.

After I restore my 2000 ReportServer and REportServertempdb and configure the database connections (see page 222 in the recource guide). An upgrade on this step ALWAYS fails because the role RSExecRole is missing for the master database.

How do I get the upgrade process to work as documented?

Many thanks Ayla

View Replies !   View Related
Server Role
What server role should be assign for individual to create and execute DTS packages.
Thank you,

View Replies !   View Related
Server Role
what views table that I can use to view the server roles on the instance?


View Replies !   View Related
MSSQL 2005 Inserting Data On Tables Created By Regular Users With Ddl_admin Role, Using Access Or Other Front End Apps
Writing to tables created by regular users on MSSQL2005

I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much

View Replies !   View Related
Server Role Permissions
Where can I find the information about dbcc setcpuweight? I checked books online and couldn't find it. I also went and searched Google with the same results. Any help is greatly appreciated.

View Replies !   View Related
Server Role For Read-only

I have a couple enter prise SQL 2000 servers, each might contain around 800 customer databases. each of those 800 databases has its own sql standard login and password and corresponding permission on table select/update/delete and proc exec rights.

I want to create a sever level single login and password, which could read-only those 800 databases all tables only data. Those 800 databases are not always the same, some might be dropped out, new might be added.

If this is impossible, what might be the alternative?

-D :)

View Replies !   View Related
User With Server Role
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied.
how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database.


View Replies !   View Related
Public Server Role
In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.

I would like more information about this role. It seems to be sort of "secret".

When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.

What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.

Thanks for the help.


View Replies !   View Related
All Fixed Server Role Missing ?? Sql Server 2000
How can I create seven fixed server role ?
My database is working but I can not do any admin stuff even if I login as
windows administrator or SA as sql administraor.
When I check sp_helpsrvrole 'sysadmin' it says no such role ( for all seven roles)
Now I do not want to lose my data. How can I solve or create roles ?

Thanks in advance

View Replies !   View Related
Returning Role Memberships FROM Sql Server
A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.

View Replies !   View Related
Checking The User's Server Role
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?

View Replies !   View Related
Backup User - Server Role?
I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.

View Replies !   View Related
Grant Server Role To A User For A Particular Job
Hello All,

Trying to run a sqlserver stored procedure which requires the user to have the 'System Administrators' server role.

Is there anyway to grant access for this user to this server role by using SQL GRANT command or so.


View Replies !   View Related
Sysadmin Fixed Server Role
Hi all,

I grant a NT user(NT Domain Admins and administrators group)as sysadmin fixed server role. I create a procedure tranfer data between SQL servers (on same domain) use that login, then schedule a job to run this proc daily basic.
but I got an error

"SQL Server Scheduled Job 'sql_backup_pinelineToBean1' (0xD8160F52CA85D51194AD00508BA55A29) - Status: Failed - Invoked on: 8/10/01 4:51:57 PM - Message: The job failed. Unable to determine if the owner (CORPORATEXXX) of job sql_backup_pinelineToBean1 has server access (reason: Could not obtain information about Windows NT group/user 'CORPORATEXXX'. [SQLSTATE 42000] (Error 8198)). "

If I change the job's owner to sa, then it works.

Any idea?

Thanks in advance

View Replies !   View Related
Is It Possible To Reconfigure The Sysadmin Server Role?
Hi all,

Is it possible toreconfigure the sysadmin server role?
How can I see it's permissions?

View Replies !   View Related
Application Role, App Role
 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

param As SqlParameter

cookie As Byte()

        Dim cn As New

(cn.State = ConnectionState.Closed) Then


        End If Please help.. 

  Thanking you, Nirmala  

View Replies !   View Related
Can't Read Tables If Not In Sysadmin Server Role

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 problemI'm havingnow 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 ?

View Replies !   View Related
How To Generate All Grants To A User Role? (SQL Server 2000)
Using SQL Server 2000 I need to extract all the grants made to a usercreated role so that I can recreate the role in another version of adatabase and there are more grants than I want to do by hand. Doesanyone have SQL or T-SQL to perform this task that they would bewilling to share?I tried searching the archives but either I missed the post or what Iwant isn't posted.Thank you.Mark D Powell

View Replies !   View Related

Copyright 2005-08, All rights reserved